Unit 3 IP Assignment: Deliverable Length 700 words in a Word file. Submit an Excel Spreadsheet showing calculations (submit separately). Fill out the 4 Tables completely.
1.Bond Table 1: 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” (you may want to use bond screener at the http://finance.yahoo.com/). All of these bonds will have these values (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 indicate whether the bond is currently trading at a discount, premium, or par.
2. Analysis of Bond Table 1:
Organize your work under 3 headings:
•Explain the relationship observed between ratings and yield to maturity.
•Explain why the coupon rate and the yield to maturity determine why the bonds would trade at a discount, premium, or par.
•Based on the material you learn in this Phase, what would you expect to happen to the yield to maturity and market value of the bonds if the time to maturity was increased or decreased by 5 years?
Select a stock that has at least a 5-year history of paying dividends and 2 of its closest competitors.
1. Stock Table 1: You will calculate the required rate of return for each of the 3 stocks. You will need to determine the risk-free rate (see Note below). You will need the market return that was calculated in Phase 2, and the beta that you find on the Web site.
2. Stock Table 2: You will determine whether the model suggests your stocks are over-priced or underpriced. You will need 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. Compare your results with the current value of each stock to determine whether the model suggests that they are over-priced or underpriced.
3. Stock Table 3: You will determine whether the model suggests your stocks are over-priced or underpriced again in a different model. You will need to find the price to earnings ratio (P/E) and the average expected earnings per share. (This should be available on the Web, though P/E can be calculated.) Compare your results with the current value of each stock to determine whether or not the model suggests that the stocks are over-priced or underpriced.
4. Analysis of Stock Tables:
Organize your work under 5 headings:
•Explain the relationship observed between the required rate of return, growth rate and the dividend paid, and the estimated value of the stock using the Gordon Model.
•Explain the value and weaknesses of the Gordon model.
•Explain the how the price-to-earnings model is used to estimate the value of the stocks.
•Explain which of the 2 models seemed to be the most accurate in estimating the value of the stocks.
•Based on the material that you learn in this Phase, what would you expect to happen to the value of the stock if the growth rate, dividends, required rate of return, or the estimated earnings per share were to increase or decrease
You can find information about the top 500 stocks at:
NOTE: You are not asked to find stocks that have a risk free rate of return. You are only asked to list the risk free rate of return, that is the 5 year treasury rate. It is not necessary to actually calculate it. What you have to calculate is the REQUIRED RATE OF RETURN.
The risk free rate will be the same for all stocks and the 5-year Return on the S&P 500 will be the same for all stocks. The beta will be different (you should look this up for the stocks you selected) and with this information you can use the CAPM to calculate the Required rate of return.