Excel 2013 & 2016: Using PV to decide which lottery option to take

Updated 2014.01.09

Objective:

Use the PV function to determine how much a series of future payments would be worth to you today. This function can be used to decide which lottery option to take: the lump sum (present value) or a series of future payments.

Example: Winning the Lottery. Should you take the lump sum or the long-term payments?

You have just won a lottery that is worth $20,000,000. However, the $20,000,000 will be paid out at the rate of $1,000,000 per year (at the beginning of each year) for 20 years. You think that if you received a lump sum (present value), you would be able to invest it at an annual rate of 6%. How much money would you have to receive today, which if invested at an annual rate of 6% (compounded annually) would allow you to withdraw $1,000,000 per year for 20 years (ending up with a balance of 0 in the savings account after the last withdrawal)?

Solution

·        "…paid out at the rate of $1,000,000 per year…" Per year means there will be one period per year. The payment: $1,000,000.

·        "…for 20 years." Number of Periods: 20 years times 1 period per year = 20.

·        "…at an annual rate of 6%..." This is the rate. 6% annually; only one payment per year so rate = 6%

·        "…ending up with a balance of 0…" The amount you want to have left at the end of the 20 years is 0, so this is the future value.

·        "…at the beginning of each year…" Payments are made at the beginning of each year, so the type is 1.

 

The PV arguments are: PV(rate, nper, pmt, fv, type)

 

So the formula is:

=PV(.06/1, 20*1, -1000000, 0, 1)

 

Note that the pmt argument is negative.

 

If you use cell references, you get this:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/018-PV-Function_files/image003.jpg

 

What does this number mean? This is what you think that the offer from the lottery is worth today. If you invested it at 6% and took out $1,000,000 each year for 20 years, you would have 0 at the end. If they offer you less than this in a lump sum, don't take it. If they offer more, take it. You can invest $1,215,811.65 and still earn $1,000,000 per year, and you can do whatever you want with what is left over.