Californian Vinh Nguyen won the Powerball Jackpot and was the sole winner. The jackpot was $228,467,735. Nguyen had the option of taking a lump sum of $134 million or an annuity that paid 30 annual payments such that the payments increased by 5% each year and added up to the total jackpot of $228,467,735. Nguyen opted for the annuity. The tax rate is 39% and inflation is expected to be 3%.

a. Construct a spreadsheet model and use Goal Seek to calculate the payment schedule, the amount Nguyen would receive each year for the annuity. What is the amount of the thirtieth payment?

b. Use the Excel NPV function to determine the net present value (NPV) of Nguyen’s 30 payments after taxes. Assume the first annual payment occurs immediately. Hint:
The Excel function NPV is discussed in problem 10.

c. Compare the NPV of the annuity to the lump sum after taxes (assuming lump sum payment occurs immediately)

d. Use Goal Seek to determine the inflation rate that make the NPV of the annuity equal to the net value of the lump sum after taxes.