Excel 2013 & 2016: Using PMT to Compute Savings Payments

Updated 2014.01.09

Objective:

·        Learn how to use the PMT function to determine the amount you must "pay" (deposit) in a savings account to reach a desired savings goal.

PMT arguments

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

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

·        PV: Present value. If doing a savings problem, the amount you have in the bank at the beginning (a negative number). If doing a loan problem, the amount you have borrowed (a positive number).

·        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 (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: Reaching a Savings Goal

You can also use the PMT function to determine how much you need to save each month to reach a savings goal. Assume that you currently have $10,000 in the bank but that your goal is to save $100,000 over the next 20 years. You know that you can get an annual return of 8%, and that you will be making deposits at the beginning of every month. How much do you have to save every month to reach your goal?

Solution

Once you realize that you need to use the PMT function, you need to identify the arguments:

·        "…need to save each month…". The number of periods per year is 12.

·        "…you currently have $10,000 in the bank…". This is how much you have now: the present value. Note that this is a negative number, because it represents the accumulation of a bunch of previous payments, and payments are always negative!

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

·        "…over the next 20 years." We are saving monthly, so there are 12 periods per year times 20 years, so the number of periods is 240.

·        "…get an annual return of 8%". Annual rate is 8%, but since we are making 12 payments per year, the rate per period is 8%/12 = .08/12.

·        "…you will be making deposits at the beginning of every month." Type is "beginning of period", so use 1.

 

The PMT arguments are: PMT(rate, nper, pv, fv, type)

 

So the formula is:

=PMT(.08/12, 240, -10000, 100000, 1)

 

The pmt function will return a negative value, because it represents money that you are "paying out" (even though you are paying it to yourself).

 

Note that the pv argument is negative because it represents the sum of all of the money that you have "paid out" (to yourself).

 

It is never a good idea to build numbers into a formula. Use references to the cells that hold the numbers:

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