Corporate Finance Excel spread sheet problem sets

Prepare a spreadsheet that shows your calculations for the following problems. 

Part 1:  Stock Valuation Problems.

1. What is the price of a stock today if the most recent dividend was $2.00 and the dividend is expected to grow by 4% per year.   Assume that the required rate of return on the stock is 13.5%.  $21.89

1. What is the price of a stock today if the dividend to be paid a year from now is $3.00 and the dividend is expected to decline by 10% per year indefinitely.  Assume that the discount rate is 8.9% for the stock. $15.87

1. A preferred stock pays a dividend of $2.50 per year.  It is currently priced at $19.55.  What is the rate of return for this preferred stock?  12.53%

1. A stock just paid a dividend of $1.00.  It is expected to grow by 20% for 3 years, and then grow at a sustainable rate of 5% per year thereafter.  If the discount rate is 10%, determine what the price of the stock should be today.  $30.84

1. Amon Inc just paid a dividend of $1.95 per share on its stock. The dividends are expected to grow at a constant rate of 4.1% per year, indefinitely.  If investors expect a 10.2% return on this stock . . .  

What is the current price?  33.28  

What will the price be in 3 years? 37.54 

What will the price be in 15 years? 60.80

1. Raffalovich, Inc. is expected to maintain a constant 4.9% growth rate on its dividends forever. If the company has a dividend yield of 5.7%, what is the required rate of return on its stock? 10.6%

1. Bui Corp pays a constant $12 dividend on its stock. The company will maintain this dividend for the next 9 years and then will cease paying dividends forever.  If the required rate of return on this stock is 10%, what is the current price?  69.11

1. Metallica Bearings, Inc is a young start-up company. No dividends will be paid on the stock over the next 9 years, because the firm needs to use its earnings to feed growth.  The company is then expected to pay a $15 dividend (10 years from today), which will grow by 5% per year thereafter.  If the required rate of return for the stock is 14%, what is the current price?  51.25

1. Apocalyptica Corp is expected to pay the following dividends over the next 4 years: $3, $10, $15, $3.08.  Afterwards, the company is promising to maintain a constant 5% growth rate in dividends.  If the required rate of return for the stock is 11%, what is the current price?  59.32

Part 2:  Risk Problems.

1. What are the portfolio weights for a portfolio consisting of three stocks: Stock A – 140 shares priced at $45 per share; Stock B – 210 shares priced at $38 per share; Stock C – 80 shares priced at $66 per share. [32.2%, 40.8%, 27.0%]

1. What is the portfolio expected return on the above portfolio if the expected return on Stocks A, B and C are 13%, 9% and 22% respectively? [13.80%]

1. You have $10,000 to invest in a portfolio and you are considering two stocks (X, Y). You want to create a portfolio with an expected return of 12.4%.  How much will you invest in each of the stocks if the expected return on X is 14% and the expected return on Y is 11%? [46.67% in X; 53.33% in Y]

1. Calculate the expected returns and the standard deviations for Stocks A and B based on the following information: [10.3%, 4.12% for A; 14.7%, 19.63% for B]

1. Using the information above what is the expected return and standard deviation for a portfolio that is equally weighted (i.e. equal parts A & B)? [12.5%, 11.82%]

1. What is the portfolio beta based on the information below: (1.231)

1. Using the information above, what is the expected return on the portfolio if the risk free rate is 3.5% and the expected return on the market is 11.2%?  [12.979%]