I need EXCEL Solution in the templete. Thanks A company must make yearly payment

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?

Bond Cashflow (Each bond has 1000 par value) 10 Bond Term (years) Coupon Rate (annual) 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.1 Number of 1000 par bonds Total Time Liability Outflow (Asset) Inflow Price of One Bond Total Cost Grand Total

Explanation / Answer

Answer:

working notes:

The market price of bonds calculated as follows:

a) Market price of bonds= Par value of bond*coupon rate of interest/ yield rate

For one year =1000*0.01/5%= $200

Ditto continued for next nine years

b) Number of bonds issues=Market price of bonds/ Outstanding liability in year 1

for year 500 bonds purchased at the market price of $200

Ditto for the next nine years.

c) Total cost of bonds is (Face value of bond- Market price of bonds)* no of bonds

for 1 years= (1000-200)*500=$400000

Ditto with the al the years.

d)

working notes:

a)Total liability flow is increasing every year by 6% and $100000 for year 1 and

year 2=100000*(1+0.06)=106000 and continued for other years on a similar basis.

b) Total bond asset inflow calculation is already highlighted in the above mention calculation.

(ends)

Bond par value 1000 Bond term 1 2 3 4 5 6 7 8 9 10 coupon rate 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.1 Number of 1000 bonds issues 500 265 187 149 126 112 101 94 89 84 Yield 5% 5% 5% 5% 5% 5% 5% 5% 5% 5% Market price of one bond 200 400 600 800 1000 1200 1400 1600 1800 2000 Time 1 500 2 265 3 187 4 149 5 126 6 112 7 101 8 94 9 89 10 84 Price of one bond 200 400 600 800 1000 1200 1400 1600 1800 2000 Total cost 400000 159000 74907 29775.4 0 22304 40529 56386 70838 84474 Grand total           9,38,213

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.