Monthly savings needed to reach goal

Summary

To calculate how much to save each month to reach a savings goal, you can use the PMT function with a monthly rate and a monthly number of periods. In the example shown, the formula in C12 is:

=PMT(C7/12,C6*12,C8,-C5)

where C5 contains the savings goal, C6 the number of years, C7 the expected annual return, and C8 the starting balance. With a goal of $60,000 in 5 years, a 5% annual return, and $10,000 already saved, PMT returns $693.56. This is the amount to save each month to reach the goal on time. See below for details, and for a check with the FV function.

Generic formula

=PMT(rate/12,years*12,start,-goal)

Explanation

The question of how much you need to save each month to reach X dollars in Y years is one of the most common questions in personal finance. As you would expect, Excel has a function dedicated to this task. The PMT function returns the payment needed each period, given a rate, a number of periods, a present value, and a future value. Most people know PMT as a loan payment function, but it works just as well for a savings plan: the "payment" is the monthly deposit, the present value is what you have already saved, and the future value is the goal. Online calculators often label this figure the monthly deposit or monthly contribution; on this page it is called monthly savings.

In this example, the goal is to calculate the monthly savings needed to reach $60,000 in 5 years, assuming a 5% annual return and a starting balance of $10,000. The worksheet uses monthly periods throughout: the annual return is divided by 12 to get a monthly rate, and the number of years is multiplied by 12 to get the number of months. The article below explains the formula, shows how to check the result with the FV function, and walks through a few common variations.

Table of contents

• Worksheet setup
• The PMT formula
• Monthly rate and monthly periods
• Why the signs matter
• Checking the result with FV
• Variations
• The return rate makes a big difference
• Summary

Worksheet setup

The worksheet has four inputs in the range C5:C8 and three outputs in the range C12:C14:

The main output is the monthly savings in C12, explained below. The other two outputs are there to show where the money comes from. Total contributions in C13 multiplies the monthly savings by the number of months:

=C12*C6*12

This is the total of all savings deposits you will make over the full term. Growth in C14 is what is left over after subtracting the starting balance and the total contributions from the goal:

=C5-C8-C13

This is the amount contributed to the savings goal by investment growth. With the inputs shown, contributions add up to $41,614, and growth (the return earned on the starting balance and the deposits) supplies the remaining $8,386.

The PMT formula

The formula in C12 is:

=PMT(C7/12,C6*12,C8,-C5)

The arguments are configured like this:

• rate is the annual return in C7 divided by 12, which gives a monthly rate of about 0.417%.
• nper is the number of years in C6 multiplied by 12, which gives 60 months.
• pv is the starting balance in C8, $10,000.
• fv is the savings goal in C5, entered as a negative number.

With these inputs, PMT returns $693.56. In other words, if you already have $10,000, and you save $693.56 at the end of every month for 5 years while earning 5% per year, you will have met your goal of saving $60,000 at the end. The starting balance is entered as a positive number, and the goal as a negative number, so that PMT returns a positive result. See Why the signs matter below.

Monthly rate and monthly periods

The one rule to remember with PMT (and with the other financial functions FV, PV, and NPER) is that the rate and nper arguments must use the same units. If you save monthly, the rate must be a monthly rate and the number of periods must be a number of months. Since return rates are almost always quoted per year, the formula divides the annual return by 12 and multiplies the years by 12:

=PMT(C7/12,C6*12,C8,-C5) // monthly

If you leave the rate and periods in years, PMT returns a yearly figure:

=PMT(C7,C6,C8,-C5) // returns $8,548.74 per year

Dividing this result by 12 gives $712.40, which is close to the monthly figure but not the same. The difference comes from timing: with monthly deposits, each deposit starts earning a return as soon as it is made, and the return compounds monthly instead of annually. For a savings plan with monthly deposits, use the monthly version. The same idea applies to the FV function when calculating compound interest.

Note: dividing the annual rate by 12 treats 5% as a nominal annual rate, which is the convention used by most savings calculators. Compounded monthly, 5%/12 works out to an effective annual return of about 5.12%, which you can confirm with =EFFECT(C7,12). If your 5% is already an effective annual rate, use =NOMINAL(C7,12)/12 for the monthly rate. See the EFFECT function and the NOMINAL function for details.

Why the signs matter

Excel's financial functions follow a cash flow convention: money you pay out is negative, and money you receive is positive. From the point of view of a saver, the starting balance and the monthly deposits are money paid into the account, and the goal is money received at the end. If you enter the inputs that way, PMT returns a negative number:

=PMT(C7/12,C6*12,-C8,C5) // returns -$693.56

The result is correct, but a negative savings amount looks odd in a worksheet. To get a positive result, flip the signs of both the starting balance and the goal, which is what the formula in C12 does. Another option is to keep the inputs as cash flows and negate the result:

=-PMT(C7/12,C6*12,-C8,C5) // returns $693.56

Both formulas return the same number. The important thing is that the starting balance and the goal must have opposite signs. If they have the same sign, PMT will return an incorrect result in the form of a much larger number.

One other detail to know about the sign: if the starting balance will grow past the goal on its own, PMT returns a negative result, which means no savings are needed, and the number shows how much could be withdrawn each month while still reaching the goal.

Checking the result with FV

A good way to build confidence in a formula like this is to work the problem in reverse. The FV function returns the future value of an investment given a rate, a number of periods, a periodic payment, and a present value. If the monthly savings figure is right, FV should return the goal. In the worksheet below, the inputs are the same as before, and the formula in C13 is:

=FV(C7/12,C6*12,-C12,-C8)

The rate is 5% divided by 12, the number of periods is 5 times 12, the payment is the monthly savings in C12, and the present value is the starting balance in C8. Both the payment and the present value are entered as negative numbers (money paid in), so that FV returns a positive number. The result is $60,000, the savings goal.

Variations

No starting balance. If you have nothing saved yet, enter 0 as the starting balance. With the other inputs unchanged, PMT returns $882.27 per month. In this case, the pv argument can also be entered directly as 0:

=PMT(C7/12,C6*12,0,-C5) // returns $882.27

Deposits at the start of the month. By default, PMT assumes each payment is made at the end of the period. If you make deposits at the start of each month, each deposit earns one extra month of return, and the required savings drop slightly. To model this, set the optional type argument to 1:

=PMT(C7/12,C6*12,C8,-C5,1) // returns $690.68

No return at all. If the return is zero, there is no growth, and the monthly savings figure is just the gap between the goal and the starting balance, divided by the number of months. PMT handles a rate of zero without trouble:

=PMT(0,C6*12,C8,-C5) // returns $833.33

This is the same as =(C5-C8)/(C6*12), and it makes a useful sanity check. Any positive return should yield a smaller number.

The return rate makes a big difference

Over a short period like 5 years, the return rate matters, but the effect is moderate. The table below shows the monthly savings needed to reach $60,000 in 5 years with $10,000 already saved, at several rates of return:

Over longer periods, the rate matters much more, because the return has more time to compound. For example, with the same starting balance and a 10-year goal at 7%, the monthly figure drops to $230.54. If you are saving for retirement rather than a fixed date, see Calculate Coast FI number for a related calculation.

Unless you are investing in a fixed-rate investment like a Treasury or a CD, the return rate is an assumption, not a guarantee. Savings accounts and bonds pay a fairly predictable return, but the return on stocks varies a lot from year to year, which can have a larger impact on short-term savings goals. In addition, none of these formulas account for taxes, fees, or inflation. Treat the result as a planning estimate and revisit it as you go.

Summary

The PMT function calculates the monthly savings needed to reach a goal:

• Divide the annual return by 12 for the rate argument and multiply the years by 12 for the nper argument, so both are in months.
• Enter the starting balance as pv and the goal as a negative fv so the result is positive. Use 0 for pv if you are starting from scratch.
• Check the result with the FV function: the same rate and periods, with the monthly savings and the starting balance as negative numbers, should return the goal.
• Set type to 1 if deposits are made at the start of each month.

For the same calculation with annual payments and annuity terminology, see Payment for annuity. To calculate the monthly payment for a loan instead, see Calculate payment for a loan.