Excel Activity: Bond Valuation

Clifford Clark 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 9% annual coupon, matures in 12 years, and has a $1,000 face value.

Bond B has an 11% annual coupon, matures in 12 years, and has a $1,000 face value.

Bond C has a 10% annual coupon, matures in 12 years, and has a $1,000 face value.

Each bond has a yield to maturity of 10%.

The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round intermediate calculations. Use a minus sign to enter negative values, if any. If an answer is zero, enter "0".

Download spreadsheet Bond Valuation-55eb2b.xlsx

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 premiuma discountpar

1. because its coupon rate isthe going interest rate.

Bond B is selling at

a premiuma discountpar

1. because its coupon rate isthe going interest rate.

Bond C is selling at

a premiuma discountpar

1. because its coupon rate isthe going interest rate.

Calculate the price of each of the three bonds. Round your answers to the nearest cent.

Price (Bond A): $ fill in the blank 8

Price (Bond B): $ fill in the blank 9

Price (Bond C): $ fill in the blank 10

Calculate the current yield for each of the three bonds. (Hint: The expected current yield is calculated as the annual interest divided by the price of the bond.) Round your answers to two decimal places.

Current yield (Bond A): fill in the blank 11 %

Current yield (Bond B): fill in the blank 12 %

Current yield (Bond C): fill in the blank 13 %

If the yield to maturity for each bond remains at 10%, what will be the price of each bond 1 year from now? Round your answers to the nearest cent.

Price (Bond A): $ fill in the blank 14

Price (Bond B): $ fill in the blank 15

Price (Bond C): $ fill in the blank 16

What is the expected capital gains yield for each bond? What is the expected total return for each bond? Round your answers to two decimal places.

Bond ABond BBond CExpected capital gains yieldfill in the blank 17 %fill in the blank 18 %fill in the blank 19 %Expected total returnfill in the blank 20 %fill in the blank 21 %fill in the blank 22 %

Mr. Clark is considering another bond, Bond D. It has a 7% semiannual coupon and a $1,000 face value (i.e., it pays a $35 coupon every 6 months). Bond D is scheduled to mature in 7 years and has a price of $1,120. It is also callable in 5 years at a call price of $1,050.

What is the bond's nominal yield to maturity? Round your answer to two decimal places.

fill in the blank 23 %

What is the bond's nominal yield to call? Round your answer to two decimal places.

fill in the blank 24 %

If Mr. Clark were to purchase this bond, would he be more likely to receive the yield to maturity or yield to call? Explain your answer.

Because the YTM is

less thangreater thanequal to

1. the YTC, Mr. Clarkexpect the bond to be called. Consequently, he would earn.

Explain briefly the difference between price risk and reinvestment risk.

This risk of a decline in bond values due to an increase in interest rates is called

price riskreinvestment risk

1. . The risk of an income decline due to a drop in interest rates is called.

Which of the following bonds has the most price risk? Which has the most reinvestment risk?

• A 1-year bond with a 10% annual coupon
• A 5-year bond with a 10% annual coupon
• A 5-year bond with a zero coupon
• A 10-year bond with a 10% annual coupon
• A 10-year bond with a zero coupon

Calculate the price of each bond (A, B, and C) at the end of each year until maturity, assuming interest rates remain constant. Round your answers to the nearest cent.

Years Remaining Until MaturityBond ABond BBond C12 $ fill in the blank 32$ fill in the blank 33$ fill in the blank 3411 $ fill in the blank 35$ fill in the blank 36$ fill in the blank 3710 $ fill in the blank 38$ fill in the blank 39$ fill in the blank 409 $ fill in the blank 41$ fill in the blank 42$ fill in the blank 438 $ fill in the blank 44$ fill in the blank 45$ fill in the blank 467 $ fill in the blank 47$ fill in the blank 48$ fill in the blank 496 $ fill in the blank 50$ fill in the blank 51$ fill in the blank 525 $ fill in the blank 53$ fill in the blank 54$ fill in the blank 554 $ fill in the blank 56$ fill in the blank 57$ fill in the blank 583 $ fill in the blank 59$ fill in the blank 60$ fill in the blank 612 $ fill in the blank 62$ fill in the blank 63$ fill in the blank 641 $ fill in the blank 65$ fill in the blank 66$ fill in the blank 670 $ fill in the blank 68$ fill in the blank 69$ fill in the blank 70

Create a graph showing the time path of each bond's value. Choose the correct graph.

The correct graph is

graph Agraph Bgraph Cgraph D

1. .

A. B. C. D.

What is the expected current yield for each bond in each year? Round your answers to two decimal places.

Years Remaining Until MaturityBond ABond BBond C12 fill in the blank 72 %fill in the blank 73 %fill in the blank 74 %11 fill in the blank 75 %fill in the blank 76 %fill in the blank 77 %10 fill in the blank 78 %fill in the blank 79 %fill in the blank 80 %9 fill in the blank 81 %fill in the blank 82 %fill in the blank 83 %8 fill in the blank 84 %fill in the blank 85 %fill in the blank 86 %7 fill in the blank 87 %fill in the blank 88 %fill in the blank 89 %6 fill in the blank 90 %fill in the blank 91 %fill in the blank 92 %5 fill in the blank 93 %fill in the blank 94 %fill in the blank 95 %4 fill in the blank 96 %fill in the blank 97 %fill in the blank 98 %3 fill in the blank 99 %fill in the blank 100 %fill in the blank 101 %2 fill in the blank 102 %fill in the blank 103 %fill in the blank 104 %1 fill in the blank 105 %fill in the blank 106 %fill in the blank 107 %

What is the expected capital gains yield for each bond in each year? Round your answers to two decimal places.

Years Remaining Until MaturityBond ABond BBond C12 fill in the blank 108 %fill in the blank 109 %fill in the blank 110 %11 fill in the blank 111 %fill in the blank 112 %fill in the blank 113 %10 fill in the blank 114 %fill in the blank 115 %fill in the blank 116 %9 fill in the blank 117 %fill in the blank 118 %fill in the blank 119 %8 fill in the blank 120 %fill in the blank 121 %fill in the blank 122 %7 fill in the blank 123 %fill in the blank 124 %fill in the blank 125 %6 fill in the blank 126 %fill in the blank 127 %fill in the blank 128 %5 fill in the blank 129 %fill in the blank 130 %fill in the blank 131 %4 fill in the blank 132 %fill in the blank 133 %fill in the blank 134 %3 fill in the blank 135 %fill in the blank 136 %fill in the blank 137 %2 fill in the blank 138 %fill in the blank 139 %fill in the blank 140 %1 fill in the blank 141 %fill in the blank 142 %fill in the blank 143 %

What is the total return for each bond in each year? Round your answers to two decimal places.

Years Remaining Until MaturityBond ABond BBond C12 fill in the blank 144 %fill in the blank 145 %fill in the blank 146 %11 fill in the blank 147 %fill in the blank 148 %fill in the blank 149 %10 fill in the blank 150 %fill in the blank 151 %fill in the blank 152 %9 fill in the blank 153 %fill in the blank 154 %fill in the blank 155 %8 fill in the blank 156 %fill in the blank 157 %fill in the blank 158 %7 fill in the blank 159 %fill in the blank 160 %fill in the blank 161 %6 fill in the blank 162 %fill in the blank 163 %fill in the blank 164 %5 fill in the blank 165 %fill in the blank 166 %fill in the blank 167 %4 fill in the blank 168 %fill in the blank 169 %fill in the blank 170 %3 fill in the blank 171 %fill in the blank 172 %fill in the blank 173 %2 fill in the blank 174 %fill in the blank 175 %fill in the blank 176 %1 fill in the blank 177 %fill in the blank 178 %fill in the blank 179 %

price riskreinvestment risk

YTCYTM

shouldshould not

less thangreater thanequal to

less thangreater thanequal to

less thangreater thanequal to