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
 
2. 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
 
3. 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%
 
4. 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
 
5. 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
 
6. 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%
 
7. 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
 
8. 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
 
9. 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%]
 
2. 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%]
 
3. 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]
 
 
4. 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]
State of Economy
Probability
Stock A Return
Stock B Return
Recession
15%
2.0%
-30.0%
Normal
55%
10.0%
18.0%
Boom
30%
15.0%
31.0%
 
5. Using the information above what is the expected return and standard deviation for a portfolio that is equally weighted (i.e. equal parts A