| Phase 3_IP Template |
| | To fill out the first table, you will need to select 3 bonds with maturities between 10 and 20 years with bond ratings of "A to AAA," "B to BBB" and "C to CC" using a credible website like FINRA.org; Bond Screener. All of these bonds will have maturity or future values of $1,000. You will need to use a coupon rate of the bond times the face value to calculate the annual coupon payment. You should subtract the maturity date from the current year to determine the time to maturity. The Web site should provide you with the yield to maturity and the current quote for the bond. (Be sure to multiply the bond quote by 10 to get the current market value.) You will then need to determne whether the bond is currently trading at a discount, premium, or par. |
| | Bond | Company | Face Value (FV) | Coupon Rate | Annual Payment (PMT) | Time-to Maturity (NPER) | Yield-to-Maturity (RATE) | Market Value (Quote) | Discount, Premium, Par |
| | 'A'-Rated | | $ 1,000 |
| | 'B'-Rated | | $ 1,000 |
| | 'C'-Rated | | $ 1,000 |
| | Now, we will need to calculate the Required Rate of Return of 3 stocks using the CAPM formula. Using a Website like Yahoo/Finance you should be able to find the Beta for each stock you have selected. You can use the 5 year Treasury Bond as a proxy for the 5 year risk-free rate. You can use the 5 year return on the S&P 500 that you calculated in the Phase 2_IP. CAPM = the Risk-Free Rate + [Beta * (5 Year Return on the S&P 500 - the Risk-Free Rate)]. |
| | | | Company | 5-year Risk-Free Rate of Return | Beta (β) (Given) | 5 Year Return on S&P 500 | Required Return on Investment |
| | To complete the next table, you will need visit a website like Yahoo/Finance and locate the most recent dividends paid over the past year for each stock, expected growth rate for the stocks, and the required rate of return you calculated in the previous table. Now you will need to compare your results with the current value of each stock and determine whether the model suggests that they are over- or underpriced. |
| | | Company | Current Dividend | Projected Growth Rate (next year) | Required Rate of Return (CAPM) | Estimated Stock Price (Gordon Model) | Current Stock Price | Over/Under Priced |
| | In the final table, you will be using the price to earnings ratio (P/E), which can also be found on a website like Yahoo/Finance, along with the average expected earnings per share. Again, you will need to compare your results with the current value of each stock to determine whether or not the model suggests that the stocks are over- or underpriced. |
| | | Company | Estimated Earning (next year) | P/E Ratio | Estimated Stock Price (P/E) | Current Stock Price | Over/Under Priced |
djkyce
phase_3_ip_template_finc_400.xlsx
