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:

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.