You have decided to open a margin account with your broker and to secure a margin loan. The initial margin requirement is 70%, and the maintenance margin is 30%. You have been following the price movements of a stock over the past ycar and believe that it is currently undervalued and that the price will rise in the near future. You have decided to purchase three round lots (i.e., 300 shares) of the stock at its current price of $25 per share. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. To Do Create a spreadsheet to model and analyze the following market transactions. a. Calculate the value of your investment in the stock if you did not make usc of margin trading. In other words, how much must you invest if you fund your purchase b. Calculate the debit balance and the cash equity in the investment at the time of opening a margin account, adhering to the initial margin requirement. e. If you use margin and the price of the stock rises by $15 to S40/share, calculate the capital gain earned and the return on investor's equity. d. What is the current margin percentage based on part c? e. If you use margin and the price of the stock falls by $15 to $10/share, calculate the capital loss and the respective return on investor's equity. f. What is the new margin percentage based on part e? What is the implication for the investor? Solution a. Calculate the value of your investment in the stock if you did not make use of margin trading. In other words, how much must you invest if you fund your purchase Price per share Number of shares purchased S25 300 Value of investment Less: Borrowed money Equity in investment b. Calculate the debit balance and the cash equity in the investment at the time of opening a margin account, adhering to the initial margin requirement. Initial margin Less: Borrowed money Equity in investment 70% e. If you use margin and the price of the stock rises by $15 to $40/share, calculate the capital gain earned and the return on investor's equity. Price per share S40 Value of stock Capital gain Return on equity (without margin) Return on equity (with margin) d. What is the current margin percentage based on part c? Current margin percentage e. If you use margin and the price of the stock falls by $15 to $10/share, calculate the capital loss and the respective return on investor's equity. Price per share $10 Value of stock Capital loss Return on equity (without margin) Return on equity (with margin) f. What is the new margin percentage based on part e? New margin percentage What is the implication for the investor? If the price declines to $10, the investor will need to deposit additional equity to raise the margin to the level of Requirements 1 In cell D20, by using cell references, caleulate the value of investment if the price per share is $25. 2 In cell D22, by using cell references, calculate the equity in investment if the margin trading is not used. Points 1 1 3 In cell D27, by using cell references, calculate the debit balance adhering to the initial margin requirement. 4 In cell D28, by using cell references, calculate the equity in investment if the margin trading is used. 5 In cell D33, by using cell references, caleulate the value of the stock if the price rises to $40 per share. 6 In cell D34, by using cell references, caleulate the capital gain earned if the price rises to $40 per share. 7 In cell D35, by using cell references, caleulate the return on investor's equity without margin requirement if the price per share rises to 1 1 1 1 1 $40. 8 In cell D36, by using cell references, caleulate the return on investor's equity with margin requirement if the price per share rises to $40. 1 9 In cell D40, by using cell references, caleulate the current margin percentage if the price per share rises to $40. 10 In cell D45, by using cell references, calculate the value of the stock if the price falls to $10 per share. 11 In cell D46, by using cell references, ealeulate the capital loss if the price falls to $10 per share. 12 In cell D47, by using cell references, caleulate the return on investor's equity without margin requirement if the price per share falls to 1 1 1 1 S10. 13 In cell D48, by using cell references, calculate the return on investor's equity with margin requirement if the price per share falls to $10. 1 14 In cell D52, by using cell references, calculate the new margin percentage if the price per share falls to $10. 15 In cells 156 and K56, determine how the price decline affects the investor. 1 Solution a. Calculate the value of your investment in the stock if you did not make use of margin trading. In other words, how much must you invest if you fund your purchase Price per share Number of shares purchased $25 300 Value of investment $7,500 Less: Borrowed money Equity in investment S7,500 b. Calculate the debit balance and the cash equity in the investment at the time of opening a margin account, adhering to the initial margin requirement. Initial margin Less: Borrowed money Equity in investment 70% S2,250 $5,250 c. If you use margin and the price of the stock rises by $15 to $40/share, calculate the capital gain earned and the return on investor's equity. Price per share $40 Value of stock S1,200 $4,500 86% 86% Capital gain Return on equity (without margin) Return on equity (with margin) d. What is the current margin percentage based on part c? Current margin percentage 81% e. If you use margin and the price of the stock falls by $15 to S10/share, calculate the capital loss and the respective return on investor's equity. Price per share $10 Value of stock $3,000 $4,500 Capital loss Return on equity (without margin) -86% Return on equity (with margin) -86% f. What is the new margin percentage based on part e? New margin percentage 25% What is the implication for the investor? nvestor needs to immerse more capital and meet the maintenance margin requirement because the margin percentage in the current scenario has gone below the maintenance margin percentage (25% and 30%) If the price declines to $10, the investor will need to deposit additional equity to raise the margin to the level of