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