Monthly Savings to Reach a Goal (PMT)

Excel Formulas › Financial

All versionsPMTNPER

“How much must I save each month to reach $50,000?” PMT answers it — given a target, a return rate, and a time frame, it solves for the deposit. Flip the question and NPER tells you how long a set deposit takes.

Quick formula: for an annual return B1, years B2, and goal B3:
=PMT(B1/12, B2*12, 0, -B3)
The 3rd argument (present value) is 0 if starting from scratch; the goal goes in as a negative future value. Result is the monthly deposit.

Functions used (tap for the full reference guide):

PMTNPER

The example

Reaching $50,000 in 10 years at a 5% return.

AB
1Annual return5%
2Years10
3Goal$50,000
4Monthly deposit$322.00

The formula

The monthly deposit to hit the goal:

=PMT(B1/12, B2*12, 0, -B3) // 5%, 10 yrs, $50k → ~$322/mo

How it works

PMT works backward from the goal:

  1. Use the monthly rate (B1/12) and number of months (B2*12), like any PMT.
  2. The 3rd argument is the present value (starting balance) — 0 if you begin with nothing.
  3. The 4th is the future value — your goal, entered negative so PMT returns a positive deposit.
  4. Compounding does heavy lifting: of the $50,000, roughly $38,600 is your deposits and ~$11,400 is growth.

Already have a head start? Put your current savings in the present-value argument (negative): =PMT(B1/12, B2*12, -5000, -50000) lowers the required monthly deposit because the $5,000 grows too.

Try it: interactive demo

Live demo

Set goal, years, and return.

Monthly deposit:

Variations

With a starting balance

Current savings reduce the deposit:

=PMT(B1/12, B2*12, -5000, -B3)

How long at a fixed deposit?

Solve for months with NPER:

=NPER(B1/12, -300, 0, -B3)

Lump sum needed now

What single deposit grows to the goal:

=PV(B1/12, B2*12, 0, -B3)

Pitfalls & errors

Sign convention. Enter the goal as a negative future value so PMT returns a positive deposit. Mismatched signs flip the result.

Rate/period mismatch. Monthly deposits need rate/12 and years*12. Using the annual rate with monthly periods badly understates the deposit.

Returns aren’t guaranteed. PMT assumes a constant rate; real markets vary, so treat the figure as a planning estimate.

Practice workbook

📊
Download the free Monthly Savings to Reach a Goal (PMT) practice workbook
A savings-goal calculator with live PMT, the head-start, NPER, and PV variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate how much to save each month to reach a goal in Excel?
Use =PMT(rate/12, years*12, 0, -goal). The goal goes in as a negative future value and the result is the required monthly deposit.
How do I account for money I've already saved?
Put it in the present-value argument as a negative: =PMT(rate/12, years*12, -current, -goal). The existing balance grows too, lowering the deposit.
How long will it take to reach my goal at a fixed deposit?
Use NPER: =NPER(rate/12, -deposit, 0, -goal) returns the number of months.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Future value of savings · Present value (PV) · Compound interest

Function references: PMT · NPER