Question

4. Bond Valuation

Given the purchase prices, coupons and maturities of four bonds, calculate the yields to maturity to you, the investor. Assume a $1,000 par value. Bonds A, B, and C are semi-annual. Bond D is a zero but calculate its yield with a semi-annual equivalency. Provide your answers to 4 significant digits (example: 6.1234%)

Bond A Price 984.00, annual coupon 3%, maturing in 2 years

Bond B Price 799.00, annual coupon 6%, maturing in 5 years

Bond C Price 767.00, annual coupon 5%, maturing in 10 years

Bond D Price 566.34, maturing in 8 years

AutoSum - v A A Times New Rov 12 ab Wrap Text General L Fill - Paste Conditional Format as Cell Insert Delete Format Sort & Find & = = = EE E Merge & Center $ - % , • Clear Formatting- Table- Styles - Filter- Select - Clipboard Alignment Font Number Styles Cells Editing fe G16 A D F Н I K 1 A B ΥTM 3 Start 4 0.5 1.0 6. 1.5 2.0 2.5 3.0 10 3.5 11 4.0 12 4.5 13 5.0 14 5.5 15 6.0 16 6.5 17 7.0 18 7.5 19 8.0 20 8.5 21 9.0 22 9.5 23 10.0 24 25 Prob. 1 Prob. 2 Prob. 3 Prob. 4 Prob. 5 133% 12:56 O Type here to search 01/14/2020

Answer 1

Please refer to below spreadsheet for calculation and answer. Cell reference also provided.

A B C D E F G A $1,000.00 $984.00 C $1,000.00 $767.00 Face value Current Price Annual coupon rate Coupon payment (semi-annual) Maturity in years B $1,000.00 $799.00 6% $30.00 5 D $1,000.00 $566.34 0% $0.00 8 3% 5% $15.00 2 $25.00 10 1.92% -$984.00 $15.00 $15.00 $15.00 $1,015.00 $15. 13 14 15 YTM (semi-annual) Start 0.50 1.00 1.50 2.00 2.50 3.00 3.50 4.00 4.50 5.00 5.50 6.00 6.50 7.00 7.50 8.00 8.50 9.00 9.50 10.00 B 5.69% -$799.00 $30.00 $30.00 $30.00 $30.00 $30.00 $30.00 $30.00 $30.00 $30.00 $1,030.00 4.25% -$767.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $25.00 $1,025.00 3.62% -$566.34 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $1,000.00 32 33

Cell reference -

IDE А Face value 1000 Current Price 984 Annual coupon rate 0.03 Coupon payment (semi-annual) =C5*C3/2 Maturity in years B 1000 799 0.06 =D5*D3/2 1000 566.34 1000 767 0.05 =E5*63/2 =F5*F3/2 10 8 YTM (semi-annual) Start 0.5 D =IRR(C11:C15) =IRR(D11:D21) =IRR(E11:E31) =IRR(F11:F27) =-C4 ]=-D4 |=-E4 =-F4 =$C$6 =$D$6 =$E$6 =$F$6 =$C$6 =$D$6 =$E$6 =$F$6 =$C$6 =$D$6 =$E$6 =$F$6 =$C$6+C3 =$D$6 =$E$6 =$F$6 =$D$6 =$E$6 =$F$6 =$D$6 =$E$6 =$F$6 =$D$6 =$E$6 =$F$6 =$D$6 =SE$6 =$F$6 =$D$6 =$E$6 =$F$6 =$D$6+D3 =$E$6 =$F$6 =$E$6 =$F$6 =$F$6 =$E$6 =$F$6 =$E$6 =$F$6 =$E$6 =$F$6 =$E$6 =$F$6+F3 =SE$6 =$E$6 =$E$6 =$E$6+E3 ESE$6 9 9.5

Please note: YTM calculated in above sheet is semi-annual YTM and to get Nominal annual YTM multiply semi-annual YTM with 2.

Hope this will help, please do comment if you need any further explanation. Your feedback would be highly appreciated.