Chapter 3 Excel Applications

Chapter 3 Excel Applications - Chapter 3 Excel Applications...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
Chapter 3 Excel Applications Spreadsheet for Buying on Margin Download the Long Margin spreadsheet 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 Omega Incorporated. The stock is currently selling at a price of $80 per share. You estimate that the stock will be selling at a price of $110 in one year. Since Omega 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 $20 to $140 in increments of $10. 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%.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
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 $20 to $140 in increments of $10. Compare the results to the ranges for question 3
Background image of page 2
Chapter 3 Excel Applications Spreadsheet for Short Selling
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 10/28/2009 for the course MBA MBA608 taught by Professor Martin during the Spring '09 term at Beirut Arab University.

Page1 / 17

Chapter 3 Excel Applications - Chapter 3 Excel Applications...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online