Task: Portfolio Valuation:
You have inherited a portfolio of stocks and bonds from your favorite uncle. However, a codicil in the will requires you to be able to price the portfolio under different economic conditions before you can take possession of it. Come to think of it, this guy was never your favorite uncle. You can prove your ability to price the portfolio by developing a spreadsheet that will price the stocks and bonds based on the risk-free rate, the market risk premium and the yield spread (for bonds).
Stock A is forecasted to pay a dividend of $10 per share at the end of this year, a dividend that will grow by 5% per year, paying dividends until the end of year 20 for a total of 20 payments. Then, the company will dissolve into a worthless zombie corporation controlled by the U.S. government and a labor union with ties to organized crime. Stock A has a beta of 1.5. There are 100 shares of this stock in the portfolio.
Stock B is forecasted to pay a steady dividend of $5 per share starting at the end of this year and continuing for 15 years for a total of 15 dividend payments. Then, the company will dissolve into a worthless zombie corporation controlled by the Canadian government and a militant splinter group of the Elks Lodge. Stock B has a beta of 0.5. There are 200 shares of this stock in the portfolio.
Bond A is a semi-annual coupon bond with a coupon rate of 6% and a par (face) value of $1000. The yield required on this bond can be obtained by adding the risk-free rate to the yield spread. The bond has 22 years (44 coupons) left to maturity. There are 10 of these bonds in the portfolio.
Create a linked spreadsheet that prices each of these instruments. The price calculations for each of the assets should reference a single set of cells that contain the risk-free rate, the market risk premium and the yield spread. In this way, you can enter different values for each variable and determine their impact on the price of the portfolio. Recall that the value of any security is the present value of its future cash flows discounted at the rate appropriate to its risk.
Once you have the spreadsheet set up, use it to price the portfolio under the base scenario of 2% risk-free rate, 10% market premium and 5% yield spread.
Question 1: What is the portfolio value under the base scenario above (expressed in dollars and cents)?
Question 2: Assume the risk-free rate rises to 4%. What impact does this have on portfolio value (amount and direction) versus the base scenario? Which asset prices are affected by this change versus the base scenario?
Question 3: Re-setting the risk-free rate to 2%, assume the market risk premium rises to 12%. What impact does this have on portfolio value (amount and direction) versus the base scenario? Which asset prices are affected by this change versus the base scenario?
Question 4: Re-setting the market risk premium to 10%, assume the yield spread rises to 7%. What impact does this have on portfolio value (amount and direction) versus the base scenario? Which asset prices are affected by this change versus the base scenario?