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 par value of $1000 and 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%). The company decides to do an absolute matching strategy to back the liability. (Hint: Do your assets in column C match your liabilities in column B?) At a yield rate of 5% how much money would the company need to have available to purchase bonds for this absolute matching strategy?

Fill in the excel above please.

A D F G H I J K L M N O P 1 Excel Problem #5: Asset/Liability matching 10 0.1 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 Bond Term (years) Coupon Rate (annual) Number of 1000 par bonds Liability Outflow 8 Time Time Bond Cash Flow Table WN Total Bond (Asset) $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 8.00 O Price per Bond Total Cost $0 $0 $0 $0 $0 $0 $0 SOSO Grand Total