Question

15 Calculate the Fair Value of the following bonds based on their Required Rate of Return. Use a nested IF statement in your Should Sell For columns, i.e. =IF(logical testl, value if truel, IF(logical test2, value if true2, value if both false)). 16 17 Index Par Value Years to Maturity 18 Fal valu 14 19 20 $ $ 14 10 21 22. 23 24 25 26 27 12 28 29 13 15 30 Bond 1 Bond 2 Bond 3 Bond 4 Bond 5 Bond 6 Bond 7 Bond 8 Bond 9 Bond 10 Bond 11 Bond 12 Bond 13 Bond 14 Bond 15 Bond 16 Bond 17 Bond 18 Bond 19 Bond 20 Bond 21 Bond 22 Bond 23 Bond 24 Bond 25 Coupon R ) 10.65% 5.80% 13.86% 8.37% 9.44% 8.17% 5.45% 7.81% 9.09% 6.25% 5.17% 5.47% 10.14% 6.39% 5.18% 11.01% 7.60% 10.40% 7.60% 9.06% 7.96% 6.81% 7.19% 6.35% 9.77% 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 1,000.00 31 32 Required Rate of Return Semi-annue 8.00% Yes 7.01% Yes 7.89% No 10.25% Yes 9.09% No 8.40% No 8.42% Yes 7.81% Yes 9.09% Yes 7.60% No 7.73% No 7.39% Yes 10.22% No 6.27% Yes 6.84% No 8.49% No 7.60% No 9.27% No 7.60% Yes 5.93% No 7.96% Yes 8.19% No 8.80% Yes 9.19% No 8.52% No 33 13 34 35 36 37 14 14 38 10 39 14 40 15 41 15 42 43 13

Answer 1

To calculate the present value in Excel, Using Formula PV(rate,nper,PMT,FV). Formula and calculation are as below:

Formulas:

Years to maturity 14 10 8 13 13 ДА В 1 Index Par Value Coupon rate 2 Bondi 1000 0.1065 3 Bond2 1000 0.058 4 Bond31000 0.1386 5 Bond4 1000 0.0837 6 Bond5 1000 0.0944 7 Bond6 1000 0.0817 8 Bond7 1000 0.0545 9 Bond8 1000 0.0781 10 Bond9 1000 0.0909 11 Bond10 1000 0.0625 12 Bond11 1000 0.0517 13 Bond12 1000 0.0547 14 Bond13 1000 0.1014 15 Bond14 1000 0.0639 16 Bond15 1000 0.0518 17 Bond16 1000 0.1101 18 Bond17 1000 0.076 19 Bond18 1000 0.104 20 Bond 191000 0.076 21 Bond20 1000 0.0906 22 Bond21 1000 0.0796 23 Bond22 1000 0.0681 24 Bond231000 0.0719 25 Bond24 1000 0.0635 26 Bond25 1000 0.0977 Required Rate of Return 0.08 0.0701 0.0789 0.1025 0.0909 0.084 0.0842 0.0781 0.0909 0.076 0.0773 0.0739 0.1022 0.0627 0.0684 0.0849 0.076 0.0927 0.076 0.0593 0.0796 0.0819 0.088 0.0919 0.0852 Semiannual Yes Yes No Yes No No Yes Yes Yes No No Yes No Yes No No No No Yes No Yes No Yes No No Fair value --PV(IF(F2="Yes",E2/2,E2),IF(F2="Yes",D2-2,D2),IF(F2="Yes",C2-B2/2,c2-B2),B2) --PV(IF(F3="Yes",E3/2,E3),IF(F3="Yes",D3*2,D3),IF(F3="Yes",C3*B3/2,C3B3),B3) --PV (IF(F4="Yes",E4/2,E4),IF(F4="Yes",D4*2,04),IF(F4="Yes",C46B4/2,04*B4),B4) --PV(IF(F5="Yes",E5/2,5),IF(F5="Yes",D5*2,05),IF(F5="Yes",C5*85/2,C5 B5),B5) --PV(IF(F6="Yes",E6/2,E6),IF(F6="Yes",D6*2,06),IF(F6="Yes",C6*B6/2,C6B6),B6) --PV(IF(F7="Yes",E7/2,E7),IF(F7="Yes",07*2,07),IF(F7="Yes",C7*B7/2,c7*B7),B7) --PV(IF(F8="Yes",E8/2,E8),IF(F8="Yes",D82,D8),IF(F8="Yes",08*B8/2,08*B8),B8) --PV(IF(F9="Yes",E9/2,9), IF(F9="Yes",D92,09),IF(F9="Yes",C9*B9/2,C9*89),B9) --PV(IF(F10="Yes",E10/2, E10),IF(F10="Yes",D10*2,010),IF(F10="Yes",C10*B10/2,C10*B10),B10) --PV(IF(F11="Yes",E11/2,E11),IF(F11="Yes",D11*2,011),IF(F11="Yes",C11*B11/2,011*B11),B11) --PV(IF(F12="Yes",E12/2,E12),IF(F12="Yes",D12*2,012),IF(F12="Yes",C12*B12/2,C12*B12),B12) --PV(IF(F13="Yes",E13/2, E13),IF(F13="Yes",D13*2,013),IF(F13="Yes",C13*B13/2,C13*B13),B13) --PV(IF(F14="Yes",E14/2,E14),IF(F14="Yes",D14*2,014),IF(F14="Yes",C14*B14/2,014*B14),B14) --PV(IF(F15="Yes",E15/2, E15),IF(F15="Yes",D15*2,D15),IF(F15="Yes",C15*B15/2,C15*B15),B15) --PV(IF(F16="Yes",E16/2, E16),IF(F16="Yes",D16*2,016),IF(F16="Yes",C16*B16/2,C16*B16),B16) --PV(IF(F17="Yes",E17/2, E17),IF(F17="Yes",D17*2,017),IF(F17="Yes",C17B17/2,017*B17),B17) --PV(IF(F18="Yes",E18/2, E18),IF(F18="Yes",D18*2,D18),IF(F18="Yes",C18*B18/2,C18*B18),B18) --PV(IF(F19="Yes",E19/2, E19),IF(F19="Yes",D19*2,019),IF(F19="Yes",C19*B19/2,C19*B19),B19) --PV(IF(F20="Yes",E20/2,E20),IF(F20="Yes",D20*2,D20),IF(F20="Yes",C20*B20/2,c20*B20),B20) --PV(IF(F21="Yes",E21/2,E21),IF(F21="Yes",D21*2,D21),IF(F21="Yes",C21*B21/2,c21-B21),B21) --PV(IF(F22="Yes",E22/2,E22),IF(F22="Yes",D22*2,D22),IF(F22="Yes",C22*822/2,c22*822),B22) --PV(IF(F23="Yes",E23/2,E23),IF(F23="Yes",D23*2,D23),IF(F23="Yes",C23*B23/2,c23*B23),B23) --PV(IF(F24="Yes",E24/2,E24),IF(F24="Yes",D24*2,D24),IF(F24="Yes",C24B24/2,c24*B24),B24) --PV(IF(F25="Yes",E25/2,E25),IF(F25="Yes",D25*2,D25),IF(F25="Yes",C25*B25/2,c25*B25),B25) --PV(IF(F26="Yes",E26/2,E26),IF(F26="Yes",D26*2,D26),IF(F26="Yes",C26*B26/2,c26*B26),B26) 9 14 15 13

Final values are as shown below:

4 A B C 1 Index Par Value Coupon rate Years to maturity Required Rate of Return Semiannual Fair value 2 Bond1 $1,000.00 10.65% 8.00% Yes $1,220.79 3 Bond2 $1,000.00 5.80% 14 7.01% Yes $ 893.18 4 Bond3 $1,000.00 13.86% 10 7.89% No $1,402.59 5 Bond4 $1,000.00 8.37% 10.25% Yes $ 899.03 6 Bond5 $1,000.00 9.44% 13 9.09% No $ 1,026.08 7 Bond6 $1,000.00 8.17% 13 8.40% No $ 982.21 8 Bond7 $1,000.00 5.45% 8.42% Yes $ 829.61 9 Bond8 $1,000.00 7.81% 13 7.81% Yes $1,000.00 10 Bond9 $1,000.00 9.09% 12 9.09% Yes $ 1,000.00 11 Bond10 $1,000.00 6.25% 13 7.60% No $ 890.91 12 Bond11 $1,000.00 5.17% 15 7.73% No $ 777.22 13 Bond12 $1,000.00 5.47% 12 7.39% Yes $ 848.95 14 Bond13 $1,000.00 10.14% 8 10.22% No $ 995.77 15 Bond14 $1,000.00 6.39% 6.27% Yes $ 1,008.16 16 Bond15 $1,000.00 5.18% 8 6.84% No $ 900.26 17 Bond16 $1,000.00 11.01% 12 8.49% No $ 1,185.18 18 Bond17 $1,000.00 7.60% 13 7.60% No $1,000.00 19 Bond18 $1,000.00 10.40% 14 9.27% No $ 1,086.66 20 Bond 19 $1,000.00 7.60% 7.60% Yes $1,000.00 21 Bond20 $1,000.00 9.06% 5.93% No $1,231.14 22 Bond21 $1,000.00 7.96% 7.96% Yes $ 1,000.00 23 Bond22 $1,000.00 6.81% 15 8.19% No $ 883.24 24 Bond23 $1,000.00 7.19% 8.80% Yes $ 917.17 25 Bond24 $1,000.00 6.35% 9.19% No $ 773.62 26 Bond25 $1,000.00 9.77% 13 8.52% No $ 1,096.03 10