Please help using excel solutions!

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 a 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?

Excel Project: Asset/Liability matching Bond Cashflow (Each bond has 1000 par value) 1 2 3 4 5 6 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 1 1010 20 30 40 60 70 80 90 Total Bond Time Liability Outflow (Asset) Inflow 1 5000001 1550 2 535000 1540 3 3 5724501 1520 4 6125221 1490 5 655398 1450 2 1020 60 70 80 90 30 1030 3 40 40 1040 50 50 50 50 10501 100 100 100 60 70 90 4 70 90 100 60 60 90 80 80 80 80 80 5 6 100 6 1400 1060 70 70 1070 701276 750365 90 100 100 7 1340 90 7 8 8 1080 100 802891 859093 919230 9 1270 1190 1100 90 10901 9 100 10 10 1100 L (Ctrl) - Price of One Bond Total Cost $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Grand Total $0 SO 8 Excel Project: Asset/Liability matching Bond Cashflow (Each bond has 1000 par value) 1 2 3 4 5 6 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 1 1010 20 30 40 60 70 80 90 Total Bond Time Liability Outflow (Asset) Inflow 1 5000001 1550 2 535000 1540 3 3 5724501 1520 4 6125221 1490 5 655398 1450 2 1020 60 70 80 90 30 1030 3 40 40 1040 50 50 50 50 10501 100 100 100 60 70 90 4 70 90 100 60 60 90 80 80 80 80 80 5 6 100 6 1400 1060 70 70 1070 701276 750365 90 100 100 7 1340 90 7 8 8 1080 100 802891 859093 919230 9 1270 1190 1100 90 10901 9 100 10 10 1100 L (Ctrl) - Price of One Bond Total Cost $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Grand Total $0 SO 8