Question

Suppose you are holding a portfolio of bonds that consists of the following four bonds. Portfolio Weight (%) 30 A B. Bond A $1,000 twenty-year 15% coupon bond with the interest rate of 12% A $1,000 eight-year discount bond with the interest rate of 7% $1,000 ten-year 12% coupon bond with the interest rate of 9% A $1,000 five-year 4% coupon bond with the interest rate of 5% C A D.

Answer 1

Hi

Please refer to the attached

A 1 2 Face Value 3 Period 4 Coupon 5 Interest B A 1000 20 15% 12% C B 1000 8 0% 7% D C 1000 10 12% 9% E D 1000 5 4% 5% F Weights 30% 30% 15% 35% 0.00 0.00 0.00 0.00 0.00 0.00 622.75 38.10 36.28 34.55 32.91 814.87 110.09 101.00 92.66 85.01 77.99 71.55 65.64 60.22 55.25 473.10 133.93 119.58 106.77 95.33 85.11 75.99 67.85 60.58 54.09 48.30 43.12 38.50 34.38 30.69 27.40 24.47 21.85 19.51 17.42 119.22 1,224.08 8.12 20 27 Price 28 Duration 29 622.75 7.00 1,192.53 6.64 956.71 4.62

With formulae-

A D F 1 А D 1000 1000 1000 10 0.12 0.09 Weights 0.3 0.3 0.15 0.35 0.04 0.05 0.07 82 9 =(C$2*C$4)/(1+$5)^$A7 = (C$2*C$4)/(1+C$5) $A8 =(C$2*C$4)/(1+C$5) $A9 = (C$2*C$4)/(1+C$5) A$A10 =(C$2*C$4)/(1+C$5) $A11 = (C$2*C$4)/(1+C$5) $A12 =((C$2*C$4)+C2)/(1+C$5) $A13 = (E$2*E$4)/(1+E$5)^$A7 =(E$2*E$4)/(1+E$5)^$A8 =(E$2*E$4)/(1+E$5)^$A9 =(E$2*E$4)/(1+E$5) $A10 =((E$2*E$4)+2)/(1+E$5)^$A11 2 Face Value 1000 3 Period 20 4 Coupon 0.15 5 Interest 0.12 6 7 1 =(B$2*B$4)/(1+B$5)^$A7 =($B$2*$B$4)/(1+$B$5) $A8 =($B$2*$B$4)/(1+$B$5) $A9 =($B$2*$B$4)/(1+$B$5) $A10 =($B$2*$B$4)/(1+$B$5) $A11 126 =($B$2*$B$4)/(1+$B$5) $A12 13 7 =($B$2*$B$4)/(1+$B$5)^$A13 14 8 =($B$2*$B$4)/(1+$B$5) $A14 15 9 =($B$2*$B$4)/(1+$B$5)^$A15 16 10 =($B$2*$B$4)/(1+$B$5) $A16 17 11 =($B$2*$B$4)/(1+$B$5)^$A17 =($B$2*$B$4)/(1+$B$5) $A18 =($B$2*$B$4)/(1+$B$5) $A19 =($B$2*$B$4)/(1+$B$5) $A20 =($B$2*$B$4)/(1+$B$5)$A21 =($B$2*$B$4)/(1+$B$5) $A22 =($B$2*$B$4)/(1+$B$5)^$A23 =($B$2*$B$4)/(1+$B$5) $A24 25 19 =($B$2*$B$4)/(1+$B$5)^$A25 26 20 = ($B$2"$B$4)+B2)/(1+$B$5) $A26 27 Price =SUM(B7:326) 28 Duration -SUMPRODUCT($A$7:$A$26,87:826)/B27 =(D$2*D$4)/(1+D$5) $A7 =(D$2*D$4)/(1+D$5) $A8 =(D$2*D$4)/(1+D$5)^$A9 = (D$2*D$4)/(1+D$5) $A10 =(D$2*D$4)/(1+D$5)^$A11 =(D$2*D$4)/(1+D$5)^$A12 =(D$2*D$4)/(1+D$5)^$A13 =(D$2*D$4)/(1+D$5) $A14 =(D$2*D$4)/(1+D$5)^$A15 =((D$2*D$4)+D2)/(1+D$5) $A16 =SUM(C7:C26) =SUM(D7:D26) =SUMPRODUCT($A$7:$A$13,07:C13)/C27 =SUMPRODUCT($A$7:$A$16,07:016)/D27 =SUM(E7:E26) =SUMPRODUCT($A$7:$A$11,E7:E11)/E27