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.
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)?
· "…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 $12,158,116.49 and still earn $1,000,000 per year, and you can do whatever you want with what is left over.