Excel 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:

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.