Question

Following is information about bonds that are identical except for their terms to maturity: Price 92.401 Yield 6.40% 6.00 Bond ABC FGH QRP VWX MNO JKL Coupon 4.600% 8.490 3.500 ? 6.140 7.208 Maturity 12/31/24 12/31/25 12/31/26 12/31/27 12/31/28 12/31/29 86.450 97.429 5.40 5.44 ? 115.000 Assume that today is December 31, 2019. Each bond pays interest semiannually, on June 30 and December 31 every year. Based on the given information, complete the computations and answer the questions that follow. 1. Using spreadsheet functions, compute the values that are missing in the table above (i.e. fill in the appropriate values where there are question marks).

Answer 1

The table is reproduced below, filling up the missing values:

AB E 1 Face Value (assumed) 2 Frequencvy of coupon 3 No. of times interest a year I $100 Semiannual 2 5 Bond б АВС 7 FGH 8 QRP 9 Vwx 10 MNO 11 JKL Coupon Rate Maturity Price Yield 4.600% 12/31/2024 92.401 6.40% 8.490% 12/31/2025 112.393 6.00% 3.500% 12/31/2026 86.450 5.89% 5.000% 12/31/2027 97.429 5.40% 6.140% 12/31/2028 104.930 5.44% 7.208% 12/31/2029 115.000 5.26% No. of int Coupn amount payments due (PMT) (Nper) $2.30 10 $4.25 $1.75 $2.50 $3.07 $3.60 12

Appropriate values are ascertained as follows:

Price of FGH using PV Function of Excel as follows:

D7 A B 1 Face Value (assumed) 2 Frequencvy of coupon 3 No. of times interest a year fix =PV(E7/2,67,F7,01)*-1 cc E $100 Semiannual 5 Bond 6 ABC 7 FGH 8 QRP 9 Vwx 10 MNO 11 JKL Coupon Rate Maturity Price Yield 4.600% 12/31/2024 92.401 6.40% 8.490% 12/31/2025 112.393 6.00% 3.500% 12/31/2026 86.450 5.89% 5.000% 12/31/2027 97.429 5.40% 6.140% 12/31/2028 104.930 5.44% 7.208% 12/31/2029 115.000 5.26% No. of int Coupn amount payments due (PMT) (Nper) $2.30 $4.25 $1.75 $2.50 $3.07 $3.60 12

Yield of QRP using RATE function of Excel as follows: (Multiplied by 2 since it is semiannual)

E8 А в 1 Face Value (assumed) 2 Frequencry of coupon 3 No. of times interest a year fac =RATE(G8,F8,08*-1,01)*2 D Ε $100 Semiannual 4 5 Bond бABC 7 FGH 8 QRP 9 Vwx 10 MNO 11 JKL Coupon Rate Maturity Price Yield 4.600% 12/31/2024 92.401 6.40% 8.490% 12/31/2025 112.393 6.00% 3.500% 12/31/2026 86.450 5.89% 5.000% 12/31/2027 97.429 5.40% 6.140% 12/31/2028 104.930 5.44% 7.208% 12/31/2029 115.000 5.26% No. of int Coupn amount payments due (PMT) (Nper) $2.30 $4.25 $1.75 $2.50 $3.07 $3.60 12 12

For Coupon rate of VWX, Coupon amount was first ascertained usinf the PMT function of Excel as follows:

Font Allahment C Clipboard F9 f A B 1 Face Value (assumed) 2 Frequencvy of coupon 3 No. of times interest a year =PMT(E9/2,69,09*-1,D1) D $100 Semiannual 5 Bond 6 АВС 7 FGH 8 QRP 9 VWX 10 MNO 11 JKL 12 Coupon Rate Maturity Price Yield 4.600% 12/31/2024 92.401 6.40% 8.490% 12/31/2025 112.393 6.00% 3.500% 12/31/2026 86.450 5.89% 5.000% 12/31/2027 97.429 6.140% 12/31/2028 104.930 5.44% 7.208% 12/31/2029 115.000 5.26% No. of int Coupn amount payments due (PMT) (Nper) $2.30 $4.25 $1.75 $2.50 $3.07 $3.60 5.40%

Coupon rate was then arrived at using the formula (PMT/Face Value)*No. of times a year =(2.50/100)*2=5%

Price of MNO is found using PV function as follows:

f C 010 - A B 1 Face Value (assumed) 2 Frequencvy of coupon 3 No. of times interest a year 4 =PV(E10/2,610,F10,01)*-1 Ε $100 Semiannual 2 No. of int Coupn amount payments due (PMT) (Nper) $2.30 10 $4.25 5 Bond 6 ABC 7 FGH 8 QRP 9 VWX 10 MNO 11 JKL 12 Coupon Rate Maturity Price Yield 4.600% 12/31/2024 92.401 6.40% 8.490% 12/31/2025 112.393 6.00% 3.500% 12/31/2026 86.450 5.89% 5.000% 12/31/2027 97.429 5.40% 6.140% 12/31/2028 104.930! 5.44% 7.208% 12/31/2029 115.000 $1.75 $2.50 $3.07 $3.60

Yield of JKL using RATE function (Multiplied by 2, as it is semi annual)

E11 A B 1 Face Value (assumed) 2 Frequencvy of coupon 3 No. of times interest a year foc =RATE(G11,F11,D11*-1,01)*2 co D Ε $100 Semiannual 5 Bond 6 АВС 7 FGH 8 QRP 9 Vwx 10 MNO Coupon Rate Maturity Price Yield 4.600% 12/31/2024 92.401 6.40% 8.490% 12/31/2025 112.393 6.00% 3.500% 12/31/2026 86.450 5.89% 5.000% 12/31/2027 97.429 5.40% 6.140% 12/31/2028 104.930 5.44% 7.208% 12/31/2029 115.000 5.26% No. of int Coupn amount payments due (PMT) (Nper) $2.30 $4.25 $1.75 $2.50 $3.07 $3.60 11 JKL 12