Accounting Week 3 Critical thinking Excel

Please complete the spreadsheet below using formulas in Excel.

Module 3 Bond and Stock Valuation
Fill in the yellow shaded cells below.
Greg Greely is a recent retiree who is interested in investing some of his savings in corporate bonds. His
financial planner has suggested the following bonds:
ŸBond A has a 7% annual coupon, matures in 12 years, and has a $1,000 face value.
ŸBond B has a 9% annual coupon, matures in 12 years, and has a $1,000 face value.
ŸBond C has an 11% annual coupon, matures in 12 years, and has a $1,000 face value.
Each bond has a yield to maturity of 9%.
1. Before calculating the prices of the bonds, indicate whether each bond is trading at a premium, at a discount,
or at par.
Bond A is selling at a discount because its coupon rate (7%) is less than the going interest rate (YTM = 9%).
Bond B is selling at par because its coupon rate (9%) is equal to the going interest rate (YTM = 9%).
Bond C is selling at a premium because its coupon rate (11%) is greater than the going interest rate (YTM = 9%).
Work parts b through e with a spreadsheet. You can also work these parts with a calculator to check your
spreadsheet answers if you aren’t confident of your spreadsheet solution. You must then go on to work
part g with the spreadsheet.
2. Calculate the price of each of the three bonds by filling in the yellow shaded cells below.
Basic Input Data
Bond A
Bond B
Bond C
Years to maturity
Periods per year
Periods to maturity
Coupon rate
Par value
Periodic payment
Yield to maturity
VB0 =
$0.00
$0.00
$0.00
3. Mr. Greely is considering another bond, Bond D. It has an 8% semiannual coupon and a $1,000 face value
(i.e., it pays a $40 coupon every 6 months). Bond D is scheduled to mature in 9 years and has a price of $1,150.
It is also callable in 5 years at a call price of $1,040.
Basic Input Data
Years to maturity
Periods per year
Periods to maturity
Coupon rate
Par value
Bond D
Periodic payment
Current price
Call price
Years until callable
Periods until callable
YTM =
#NUM!
YTC =
#NUM!
4. If Mr. Greely were to purchase this bond, would he be more likely to receive the yield to maturity or yield to call?
Explain your answer below.
STOCK VALUATION
Rocky Mountain Corporation (RMC) has been growing at a rate of 20% per year in recent years. This
same growth rate is expected to last for another 2 years, then to decline to g n = 6%.
If D0 = $1.60 and rs = 10%, what is TTC’s stock worth today? What are its expected dividend
and capital gains yields at this time, that is, during Year 1?
1. Find the price today.
D0
rs
gs
gn
Year
Dividend
0
$0.0000
Short-run g; for Years 1-2 only.
Long-run g; for Year 3 and all following years.
0%
0%
1
2
3
$0.0000
$0.0000
$0.0000
PV of dividends
$0.0000
#DIV/0!
= Horizon value = P2 =
0.0%
$0.0000 = P0
2. Find the expected dividend yield.
Recall that the expected dividend yield is equal to the next expected annual dividend divided by the price at
the beginning of the period.
Dividend yield =
Dividend yield =
D1
Dividend yield =
#DIV/0!
/
/
P0
3. Find the expected capital gains yield.
The capital gains yield can be calculated by simply subtracting the dividend yield from the expected
total return.
Cap. gain yield =
Cap. gain yield =
Expected total return
Cap. gain yield =
0.00%
−
−
Dividend yield
r yield to call?
= rs – gn