Savings Goal Calculator in Excel: How Much to Save Each Month (PMT, NPER and Goal Seek)

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: savings-goal-calculator-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

New functions in one line each (all use the monthly rate = yearly rate / 12):

• PMT(rate, nper, pv, fv) gives the saving needed each month.
• NPER(rate, pmt, pv, fv) gives how many months it takes.
• FV(rate, nper, pmt, pv) gives what you will have at the end.

The sign rule: money you put in (the deposit, what is saved so far) is negative; the target you get back is positive.

Salman's bike (made-up): target ₹1,50,000, saved ₹20,000, 24 months, 6% a year.

• Without any return he would need (1,50,000 − 20,000) / 24 = ₹5,416.67 a month.
• With 6% a year, =-PMT(6%/12,24,-20000,150000) = ₹5,011.68 a month, because his money earns while it waits.
• His plan is ₹5,000 a month. =NPER(6%/12,-5000,-20000,150000) = 24.05 months, just over the deadline.
• =FV(6%/12,24,-5000,-20000) = ₹1,49,703 after 24 months: ₹297 short. Saving ₹12 more a month fixes it.

2. Build it step by step

Step 1. Goal table

1. In A4:J4 type Goal, Target, Saved so far, Months left, Expected return (yearly), Monthly saving needed, Progress, Plan to save per month, Months at that plan, Value at deadline with plan.
2. Type the four goals in rows 5 to 8 (yellow cells: B to E and H).

Step 2. Saving needed with PMT

1. In F5 type =-PMT(E5/12,D5,-C5,B5): ₹5,011.68.
2. The minus inside (-C5) is money already put in; the minus in front makes the answer positive.

Step 3. Progress bar

1. In G5 type =C5/B5 and format as %: 13%.
2. Select G5:G8 > Conditional Formatting > Data Bars (minimum 0, maximum 1) (data bars).

Step 4. Months with NPER

1. In I5 type =NPER(E5/12,-H5,-C5,B5): 24.05 months.
2. Add a formula rule on I5:I8: =$I5>$D5, red fill: the plan is behind the deadline.

Step 5. Value at deadline with FV

1. In J5 type =FV(E5/12,D5,-H5,-C5): ₹1,49,703.
2. Add a rule on J5:J8: =$J5<$B5, red fill.
3. Copy F5:J5 down to row 8.

Step 6. Totals and a check

1. In B10, C10, F10 and H10 type SUMs of the column; in G10 =C10/B10 (26% of all targets saved).
2. In F12 type =(B5-C5)/D5: ₹5,416.67, the no-interest check for the bike.

Step 7. Exact plan with Goal Seek

1. Data > What-If Analysis > Goal Seek: Set cell J5, To value 150000, By changing H5 (Goal Seek).
2. H5 becomes about ₹5,011.68, the same as PMT gave in F5.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. Using the yearly rate. Divide by 12 for monthly saving, as in E5/12.
2. Wrong signs. Saved money and deposits are negative, the target positive. If NPER shows #NUM!, a sign is usually wrong.
3. Expecting a fixed return from shares. PMT assumes the same return every month. Use a careful rate and treat the result as a plan, not a promise.
4. Months vs years. D5 is months. Two years is 24.
5. Forgetting what is already saved. Leaving C5 out makes the monthly saving look bigger than it is.

5. More real examples

Example 1: Child's school fees. Target the next year's fees, months left until June, and a recurring-deposit rate.

Example 2: Down payment for a flat. Target 20% of the flat price; then use the EMI calculator for the loan part.

Example 3: Wedding fund. Several goals (jewellery, venue, travel) as rows; F10 gives the total monthly saving.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate monthly savings for a goal in Excel?

=-PMT(rate/12, months, -saved, target). For ₹1,50,000 in 24 months with ₹20,000 saved at 6%: ₹5,011.68.

How long will it take to reach my savings goal?

=NPER(rate/12, -monthly, -saved, target) returns months.

What will my monthly saving grow to?

=FV(rate/12, months, -monthly, -saved).

Why do PMT and NPER give #NUM! or negative answers?

Usually a sign problem: keep deposits negative and the target positive, as in the template.

Can I do it without the finance functions?

Yes, roughly: (Target − Saved) / Months, ignoring interest. The template shows this check in F12.

7. Quick quiz

Ten questions on this page. Sign in to save your score.