I need EXCEL Solution in the templete. Thanks A company must make yearly payment
ID: 2804420 • Letter: I
Question
I need EXCEL Solution in the templete. Thanks
A company must make yearly payments starting at $100,000 and increasing by 6% every year for 10 years. Payments are due at the end of each year. They can invest in a portfolio of coupon-paying bonds that vary in term from 1 to 10 years (a total of 10 unique bonds). Each bond has a coupon rate equal to the term of the bond (i.e. the 10-year bond has a coupon rate of 10%, the 8-year bond has a coupon rate of 8%). They decide to do an absolute matching strategy to back the liability. At a yield rate of 5% how much would the company need to have available to purchase bonds for this absolute matching strategy?
In the templete.
Each Total bond (Asset) flow is SUM(F8:O8), SUM(F9:O9) ..... SUM(F17:O17)
Total Cost is F19*F6, G19*G6 .... O19*O6.
Grand Total is SUM(F20:O20)
Please provide the explain how to get the values.
Bond Cashflow (Each bond has 1000 par value) 10 0.1 0.06 0.07 0.08 0.09 Bond Term (years) 0.03 0.04 0.05 0.01 0.02 Coupon Rate (annual) Number of 1000 par bonds Time otal B 7 Time Llablility Otow (Asset) Inflow 10 13 14 15 Price of One Bond Total Cost 20 21 Grand TotalExplanation / Answer
Bond Term 1 2 3 4 5 6 7 8 9 10 Coupon Rate 1% 2% 3% 4% 5% 6% 7% 8% 9% 10% Bond Par value $ 1000 Number of 1000 Par bonds 100 106.00 112.36 119.10 126.25 133.82 141.85 150.36 159.38 168.95 =100000/1000 =106000/1000 =112360/1000 and so on Time Liability Outflow Total Bond Asset Inflow Time Matching Stratey 1 100,000.00 10,000.00 (sumf11:011) 1 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 =(total outflow in coupon % ) i.e total outlfow x 1% 2 106,000.00 19,080.00 Sum(g12:012) 2 2,120.00 2,120.00 2,120.00 2,120.00 2,120.00 2,120.00 2,120.00 2,120.00 2,120.00 total out flow x 2% 3 112,360.00 26,966.40 3 3,370.80 3,370.80 3,370.80 3,370.80 3,370.80 3,370.80 3,370.80 3,370.80 and so on 4 119,101.60 33,348.45 4 4,764.06 4,764.06 4,764.06 4,764.06 4,764.06 4,764.06 4,764.06 5 126,247.70 37,874.31 5 6,312.38 6,312.38 6,312.38 6,312.38 6,312.38 6,312.38 6 133,822.56 40,146.77 6 8,029.35 8,029.35 8,029.35 8,029.35 8,029.35 7 141,851.91 39,718.54 7 9,929.63 9,929.63 9,929.63 9,929.63 8 150,363.03 36,087.13 8 12,029.04 12,029.04 12,029.04 9 159,384.81 28,689.27 9 14,344.63 14,344.63 10 168,947.90 16,894.79 10 16,894.79 1 YTM 5% 2 =(1+6%)*100000 3 =(1+6%)*106000 and so on Price of Bond 142.8571429 189.1156463 233.223194 275.2827063 315.3922068 353.6450564 390.130175 424.932252 458.131952 489.806099 Total Cost 14,285.71 20,046.26 26,204.96 32,786.61 39,817.54 47,325.69 55,340.71 63,894.10 73,019.27 82,751.71 Grand Total 455,472.56 $ Bond Price =Cx[1-(1/(1+i)^n)/I + M/((1+i)^n) C Coupon Payment of the Year n No of years (for 1 yr i=1,2,3,4--10) i Yield to maturity 5% M Par value , here 1000$ Bond Term 1 2 3 4 5 6 7 8 9 10 Coupon Rate 1% 2% 3% 4% 5% 6% 7% 8% 9% 10% Bond Par value $ 1000 Number of 1000 Par bonds 100 106.00 112.36 119.10 126.25 133.82 141.85 150.36 159.38 168.95 =100000/1000 =106000/1000 =112360/1000 and so on Time Liability Outflow Total Bond Asset Inflow Time Matching Stratey 1 100,000.00 10,000.00 (sumf11:011) 1 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 =(total outflow in coupon % ) i.e total outlfow x 1% 2 106,000.00 19,080.00 Sum(g12:012) 2 2,120.00 2,120.00 2,120.00 2,120.00 2,120.00 2,120.00 2,120.00 2,120.00 2,120.00 total out flow x 2% 3 112,360.00 26,966.40 3 3,370.80 3,370.80 3,370.80 3,370.80 3,370.80 3,370.80 3,370.80 3,370.80 and so on 4 119,101.60 33,348.45 4 4,764.06 4,764.06 4,764.06 4,764.06 4,764.06 4,764.06 4,764.06 5 126,247.70 37,874.31 5 6,312.38 6,312.38 6,312.38 6,312.38 6,312.38 6,312.38 6 133,822.56 40,146.77 6 8,029.35 8,029.35 8,029.35 8,029.35 8,029.35 7 141,851.91 39,718.54 7 9,929.63 9,929.63 9,929.63 9,929.63 8 150,363.03 36,087.13 8 12,029.04 12,029.04 12,029.04 9 159,384.81 28,689.27 9 14,344.63 14,344.63 10 168,947.90 16,894.79 10 16,894.79 1 YTM 5% 2 =(1+6%)*100000 3 =(1+6%)*106000 and so on Price of Bond 142.8571429 189.1156463 233.223194 275.2827063 315.3922068 353.6450564 390.130175 424.932252 458.131952 489.806099 Total Cost 14,285.71 20,046.26 26,204.96 32,786.61 39,817.54 47,325.69 55,340.71 63,894.10 73,019.27 82,751.71 Grand Total 455,472.56 $ Bond Price =Cx[1-(1/(1+i)^n)/I + M/((1+i)^n) C Coupon Payment of the Year n No of years (for 1 yr i=1,2,3,4--10) i Yield to maturity 5% M Par value , here 1000$
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.