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:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/018-PV-Function_files/image001.jpg