finance "bond. Duration. immunized. liability" Excel

As part of a legal settlement related to a concussion lawsuit, the NFL has agreed to pay $900 million in damages to former players. The NFL will pay the total of $900 million in 65 equal annual payments, with the first payment to occur in exactly one year.

To insure its ability to make these future payments, the NFL will invest today in risk-free 30-year bonds that pay annual coupons. Assume the pure yield curve is flat at 5%.

Using Excel and (in the case of question 3) Goal Seek, answer the following questions.

1. How much money should the NFL invest in its bond portfolio today to fully fund its future liability?

2. Calculate the duration of the NFL’s liability.


3. If the NFL wants its bond portfolio to be immunized against interest rate risk, what should be the coupon rate of the 30-year bonds it selects?

• Please confine all work to a single spreadsheet.

• Your Excel file should be professional, well-organized and clearly labeled, as these criteria will factor into your grade