FIN 4019 Rasmussen College Bond Pricing Valuation Model Sheet

For this assignment, answer the following questions in one spreadsheet (do both parts in one Excel file). You will create your own Excel file from scratch, but you are welcome to use the sample spreadsheet in this module as the starting point.

Requirements for All Parts

1. Choose any methodology you prefer.
2. Label question numbers.
3. Keep the inputs, calculations, and outputs in separate sections.
4. Do not hard-code numbers into formulas cells (i.e., do not paste the value of an output cell into a function argument of another input cell).
5. For 2a and 5b, write an explanation for each answer. Enter your answers in a text box.

Part 1: Bond Valuation

1. An annual bond has a face value of $1,000 and makes an annual coupon payment of $12. The discount rate per year is 4.37%, and the years to maturity is 8. What is the price of this bond?
2. Suppose a bond has a face value of $1,000, an annual coupon rate of 5.0%, a yield to maturity of 9.0% (quoted as an APR), makes 2 coupon payments per year, and 4 years to maturity. What is the price of this bond?
   1. When the bond price above the face value, it’s trading at a premium, or above par. If the price is below the face value, it’s trading at a discount, or below par. If your calculated price is above par, why would you still consider purchasing the bond? If your price is below par, why would you hesitate to buy this one? Explain.
3. Given the four variables below, find the face value of the bond.
   

Part 2: Equity Valuation

1. ABC pays a dividend of $0.56 per share in the coming year. The stock trades at $45.50 per share at the end of the year. The required rate of return is 6.80%. What dividend yield and capital gain rate can you expect?
2. Suppose a stock pays a dividend per share of $43.37. You project the future dividend growth rates over the next 5 years to be:
   

   Year	0	1	2	3	4	5
   Dividend Growth Rate	–	21.0%	18.0%	15.0%	13.5%	11.5%

   After 5 years, the rate will be a constant 11%. The rate of equity return is 13.4%.

   1. What is the stock’s value?
   2. Similar to bond valuation, stocks can be traded at a discount (undervalued) or a premium (overvalued). What factors could cause the stock to be overvalued? Undervalued? Explain.