Spreadsheet Practice

How much to save each month to reach a goal in Excel

Formula (Excel and Google Sheets)

=-PMT(B4/12,B3*12,-B5,B2)

Use PMT with the goal as the future value: =-PMT(B4/12,B3*12,-B5,B2) is the monthly amount, after what you have already saved.

Result on the example
$520.92 in B6
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B6. Change any input and the result updates, or edit the formula to experiment.

How it works

PMT's fourth argument is a future value: the amount you want to end with. The starting $2,500 is money you put in, so it is negative, and the target is money you get back, so it is positive. PMT then returns the monthly deposit, as a negative because it goes out; the minus in front shows it as a positive amount.

=-PMT(B4/12,B3*12,-B5,B2)

Result520.9240026

  1. B44.0%

    The value in B4.

  2. B4/120.003333333333

    The / operator divides.

  3. B34

    The value in B3.

  4. B3*1248

    The * operator multiplies.

  5. B5$2,500

    The value in B5.

  6. -B5-2500

    Makes the value negative.

  7. B2$30,000

    The value in B2.

  8. PMT(B4/12,B3*12,-B5,B2)-520.9240026

    PMT: Payment per period for a loan or annuity.

    • rateB4/120.003333333333
    • nperB3*1248
    • pv-B5-2500
    • fvB2$30,000
  9. -PMT(B4/12,B3*12,-B5,B2)520.9240026

    Makes the value negative.

In Google Sheets

The same formula works in Google Sheets: =-PMT(B4/12,B3*12,-B5,B2). We ran it in Google Sheets mode on the same data and it returns $520.92.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=(B2-B5)/(B3*12)$572.92Different resultWithout any growth, the gap divided by the months. More without growth: $572.92.

Common mistakes

  • Giving the target and the deposits the same sign returns a payment with the wrong sign, or nonsense.
  • Use a cautious return: the formula assumes it every month, but real returns vary.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

How much to save each month to reach a goal

In B6, calculate how much to save each month to reach the target in B2 in B3 years, earning the return in B4, starting with the amount in B5.

Questions

How long will it take at a fixed monthly amount?

Use NPER: =NPER(B4/12,-500,-B5,B2)/12 years for $500 a month.

How do I save weekly instead?

Use weekly periods: =-PMT(B4/52,B3*52,-B5,B2).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.