Excel 2013 & 2016: Using PV to determine how much you need to reach a
savings goal

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 determine
how much money you should start with in a savings account (its present value)
to reach a desired savings goal.

# PV arguments:

·
**RATE:** Rate per period, not rate per year.

·
**NPER:** Number of periods, not number of years.

·
**PMT:** Payment. If you are doing a savings
problem, the amount you are depositing each period. If you are doing a loan
problem, the amount you are paying to re-pay the loan each period. This number
is __always negative__.

·
**FV:** Future value. The amount you will
have at the end of the number of periods if you are doing a savings problem, or
the amount you will owe at the end of the number of periods if you are doing a
loan problem (this is usually, but not always, 0).

·
**TYPE:** Type of payment. 1=payments at the beginning
of the period, 0=at the end of the period.

## Example: How much
do you need to start with in a savings account to reach a goal if you make
regular deposits?

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 be making monthly deposits of $100. Interest is compounded monthly at the
beginning of the month. How much money do you need to start out with now to
have $1,000,000 in 30 years?

#### Solution

·
"…goal of $1,000,000…"
This is how much you want to have in the future: the **future value**.

·
"…30 years from now." This is
the number of years. It will have to be multiplied by the number of periods per
year to find the **number of periods**: 30*12 = 360.

·
"…annual interest rate of 10%."
This is the annual rate. Divide by the periods per year to determine the **rate
per period:** 10%/12.

·
"…making monthly deposits of
$100." This is your **payment**. In the argument list, the payment is
always negative: -100.

·
"Interest is compounded monthly." This
is the number of **periods per year**: 12.

·
"…at the beginning of the
month." This is the **type**. Beginning of month = 1

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

So the formula is:

=PV(.10/12,
30*12, -100, 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).

If you use cell references, you get
this: