Question description


document of 700–1,000 words with attached Excel Spreadsheet showing

After engaging in a dialogue with your colleagues on valuation,
you will now be given an opportunity to apply principles that were presented in
this phase. Using a Web site that provides current stock and bond pricing and
yield information, complete and analyze the tables illustrated below. Your
mentor suggests using a Web site similar to this one.
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 Web site linked above). 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.



Face Value (FV)

Coupon Rate

Annual Payment (PMT)

Time-to Maturity (NPER)

Yield-to-Maturity (RATE)

Market Value (Quote)

Discount, Premium, Par







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? In this step, you have been asked to visit a credible Web site
that provides detailed information on publicly traded stocks and select 1 that
has at least a 5-year history of paying dividends and 2 of its closest
competitors. To fill up the first table, you will need to gather information
needed to calculate the required rate of return for each of the 3 stocks. You
will need the risk-free rate that you used in Phase 3, the market return is
calculated in Phase 1, and the beta that you should be able to find on the Web

5-year Risk-Free Rate of

Beta (β)

5-Year Return on Top 500

Required Rate of Return

To complete the next table, 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.
You will also need to compare your results with the current value of each stock
and determine whether the model suggests that they are over- or underpriced.


Current Dividend

Projected Growth Rate
(next year)

Required Rate of Return

Estimated Stock Price
(Gordon Model)

Current Stock Price

Over/Under Priced

In the third table, you will be using the price to earnings ratio
(P/E) along with the average expected earnings per share provided by the Web
site. You will also 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.


Estimated Earning
(next year)

P/E Ratio

Estimated Stock Price

Current Stock Price

Over/Under Priced

After completing the 3 tables, explain your findings and why your
calculations coincide with the principles related to bonds that were presented
in the Phase. Be sure to address the following:
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? Be sure to explain each case separately. Note: You can find information about the top 500 stocks at this Web site.ReferencesS&P
500 index chart. (2014). Retrieved from the Yahoo! Finance Web site:^gspc;range=1y;compare=;indicator=volume;charttype=area;crosshair=on;ohlcvalues=0;logscale=off;source=;Yahoo!
Finance. (n.d.). Retrieved from sure to document your paper with in-text
citations, credible sources, and a list of (3) references minimum used in proper APA


·  Apply the time value of
money in making financial decisions.
·  Determine the
relationship between risk and return by calculating stock and portfolio
·  Apply valuation formulas
to assess the value of stocks and bonds.
·  Use effective
communication, team and problem-solving skills to collaborate on a project.

"Are you looking for this answer? We can Help click Order Now"