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 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.