Excel Problems

Excel Problems for chapter 3: Building on Margin and Short Selling

A. Buying on Margin - Click here to download the spreadsheet (39.0K)

The accompanying spreadsheet can be used to measure the return on investment for buying stocks on margin. The model is set up to allow the holding period to vary in months. The model also calculates the price at which you would get a margin call for a specified maintenance margin.

The initial equity investment, initial stock price, the estimated ending stock price, cash dividends paid on the stock during the holding period, the initial margin percentage, the maintenance margin percentage, the rate on the margin loan and the holding period in months are entered. The spreadsheet calculates the number of shares controlled, the holding period return and the price at which you would get a margin call for a specified maintenance margin. The amount of equity that you will invest along with the margin percentage determines the number of shares that you will purchase on margin. The model is initially set with the maximum allowable margin of 50%.

Return on investment is calculated with consideration of capital gain on loss, dividends received and margin interest paid for the holding period. The return on investment is presented in Cell B22. The return on stock trade that would be made without borrowing is presented in Cell B29.

The data table function calculates return on investment for a range of ending stock prices that range from $20 to $80 in increments of $5. The returns are displayed in Cells E4 through E17. The comparable returns on an all equity investment in stock are displayed in Cells H4 through H17. The impact that margin trading has on volatility in returns is shown by comparing these ranges.

Questions
You have $20,000 to invest in the shares of Iomega Incorporated. The stock is currently selling at a price of $40 per share. You estimate that the stock will be selling at a price of $60 in one year. Since Iomega is a growth stock, no cash dividends are expected over the next year. The rate on margin loans is currently 7%.

1. What would be the expected return on the investment assuming that you used the maximum allowable margin of 50%?
2. At what price would you get a margin call assuming the maintenance margin was 30%?
3. Construct two data tables that compare the return on investment for a margin trade and a trade with no margin for ending stock prices that range from $10 to $70 in increments of $5.
4. What would be the expected return on investment if you were to use an initial margin of 80% rather than the maximum allowable margin of 50%?
5. How far could the stock price fall with an initial margin of 80% assuming the maintenance margin remains at 30%?
6. Construct two data tables that compare the return on investment for the margin trade and a trade with no margin for ending stock prices that range from $10 to $70 in increments of $5. Compare the results to the ranges for question 3.

B. Short Selling - Click here to download the spreadsheet (27.0K)

The accompanying spreadsheet is set up to measure the return on investment from a short sale. The spreadsheet is based on the text example in Section 3.5. The spreadsheet also calculates the price at which additional margin would be required.

The initial equity investment, initial stock price, estimated ending stock price, estimated cash dividends during the holding period, the initial and maintenance margin are inputs in the appropriate cells. The number of shares shorted is calculated from the original investment and the initial margin percentage that is specified in Cells B5 and B10. The return on the short sale is presented in Cell B17. The net income includes the capital gain or loss minus any dividends paid while the stock is shorted. Cell B21 calculates the equity as a percentage of the value of the stock shorted and Cell B23 solves for the price at which additional margin will be required.

The data table function is used to calculate the return on investment for a range of ending stock prices. As is shown in Cells E7 through E23, the range of return ranges from negative 140 percent to a positive 180 percent. The maximum return in percentage would be a positive 200 percent that would occur when the stock price reached zero.

Questions
You have $30,000 to commit to the short sale. You observe that shares of Omicron Technology are selling at a price to $150 per share. Your research indicates that the shares are overvalued and should be selling for a price to closer to $85 per share. Assuming that the maintenance margin is 25% and that the initial margin is 50%, use the spreadsheet as template to answer the following questions.

1. What would be the expected return on investment if you were correct and the shares were to sell for an ending price of $85 per share?
2. What would be the margin based on ending price if your prediction was correct?
3. How far could the price of Omicron rise before you would get a call for additional margin?
4. Analyze return on investment for the range of possible ending stock prices from a high of $170 per share to a low of $70 per share in increments of $10. Use the data table function to created the range of return on investment.