PV (Present Value)

Use the PV function to determine how much a series of future payments would be worth to you today. This is the function that a lottery winner must use to decide whether to take a series of future payments or a lump sum (present value). You can also use the PV function to determine how much money you should start with in a savings account (its present value) to reach a desired savings goal.

The function arguments are:

· Rate per period

· Number of periods

· Payment

· Future value

· Type (1=payments at the beginning of the period, 0=at the end of the period)

Example 1: How much do you need to start with in a savings account to reach a goal?

You have a savings goal of $1,000,000 30 years from now. You think you can earn an annual interest rate of 10%. You will not be making any deposits. Interest is compounded monthly. How much money do you need to start out with now to have $1,000,000 in 30 years?

Solution 1

Periods Per Year: 12

Rate: 10% / 12

Number of Periods: 30 * 12

Payment: The amount you will save each period -- $0.

Future Value: $1,000,000

Type: Doesn't matter since you are not making any regular deposits. A 0 or 1 will work.

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

So the formula is:

=PV(.10/12, 30*12, -0, 1000000, 1)

Note that the pmt argument is negative. This is because it represents money that you will "pay out" (even though you are "paying" it to yourself in the form of a savings account). Note that in this case the sign doesn't matter because the payment is 0. See the next example, though.

If you use cell references, you get this:

Example 2: How much do you need to start with in a savings account to reach a goal?

You can also compute how much you need to start with if you make regular deposits. Change the previous problem so that you save $100 per month for 30 years (at the end of the month).

Solution 2

Periods per Year: 12

Rate: 10%/12 (because you are making monthly payments – 12 periods per year)

Number of Periods: 30 * 12 = 360 (30 years times 12 payments per year)

Payment: $100. The amount you will save each month

Future Value: $1,000,000

Type: 0 since you are making payments at the end of each month

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

So the formula is:

=PV(.10/12, 30*1, -100, 1000000, 0)

Note that the pmt argument is negative. This is because it represents money that you will "pay out" (even though you are "paying" it to yourself in the form of a savings account).

If you use cell references, you get this:

Example 3: Winning the Lottery

You have just won a lottery that is worth $2,000,000. However, the $2,000,000 will be paid out at the rate of $100,000 per year (at the beginning of each year) for 20 years. You think that if you received a lump sum, 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% would allow you to withdraw $100,000 per year for 20 years (ending up with a balance of 0 in the savings account after the last withdrawal)?

Solution 3

Payment: The amount you receive at the beginning of each year: $100,000.

Rate: 6% annually; only one payment per year so rate = 6%

Payments Per Year: 1

Number of Periods: 20 years times 1 period per year = 20

Future value: The amount you want to have left at the end of the 20 years is 0.

Type: Payments are made at the beginning of each year, so this is 1.

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

So the formula is:

=PV(.06/1, 20*1, -100000, 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 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 $100,000 per year, and you can do whatever you want with what is left over.

Updated 2006.10.23