Struggling with the rest. Need help computing the number of par bonds bought, and the rest of the problem. The problem is below:

A company must make yearly payments starting at $500,000 and increasing by 7% 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 redemption of 1000 and 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 4% how much would the company need to have available to purchase bonds for this absolute matching strategy?

Use the excel template posted to show your work; fill in all empty-box cells.

Excel Project: Asset/Liability matching Bond Cashflow (Each bond has 1000 par value) 1 2 3 4 5 6 7 7 8 9 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 Time Time 1 1010 30 40 701 80 90 100 20 1020 60 60 2 30 40 70 90 80 80 100 100 3 1030 50 50 50 50 1050 40 60 70 90 4 10401 60 70 80 90 Total Bond Liability Outflow (Asset) Inflow 1 500000 1550 2 535000 1540 3 572450 1520 4 612522 1490 5 655398 1450 6 701276 1400 7 750365 1340 8 802891 1270 9 859093 1190 10 919230 1100 100 5 60 80 90 6 1060 70 70 1070 90 80 80 100 100 100 7 90 8 1080 90 1090 100 100 9 10 1100 Price of One Bond Total Cost $971 $0 $962 $0 $972 $0 $1,000 $0 $1,105 $1,045 $0 $1,180 $0 $1,269 $0 $1,372 $0 $1,487 $0 $0 Grand Total $0 Excel Project: Asset/Liability matching Bond Cashflow (Each bond has 1000 par value) 1 2 3 4 5 6 7 7 8 9 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 Time Time 1 1010 30 40 701 80 90 100 20 1020 60 60 2 30 40 70 90 80 80 100 100 3 1030 50 50 50 50 1050 40 60 70 90 4 10401 60 70 80 90 Total Bond Liability Outflow (Asset) Inflow 1 500000 1550 2 535000 1540 3 572450 1520 4 612522 1490 5 655398 1450 6 701276 1400 7 750365 1340 8 802891 1270 9 859093 1190 10 919230 1100 100 5 60 80 90 6 1060 70 70 1070 90 80 80 100 100 100 7 90 8 1080 90 1090 100 100 9 10 1100 Price of One Bond Total Cost $971 $0 $962 $0 $972 $0 $1,000 $0 $1,105 $1,045 $0 $1,180 $0 $1,269 $0 $1,372 $0 $1,487 $0 $0 Grand Total $0