Build a Savings Goal Calculator in Excel

A savings calculator in Excel tells you how much to put aside each month to reach a goal by a date, and how far along you are. It takes three inputs and a handful of formulas, and it updates the moment you change any number. This guide builds one for a 5,000 goal, first without interest and then with it, and adds a progress bar you can check every month.

Step 1: Set up the inputs#

Put each input in its own cell with a label, so the formulas can refer to them. Shade the input cells so it's clear which ones to edit.

If you'd rather enter a target date than a number of months, put the date in a cell and work out the months with =DATEDIF(TODAY(),D1,"m"). TODAY changes every day, so the months left count down by themselves.

Step 2: Work out the monthly saving#

The amount you still need is the goal minus what you have. Divide it by the months left. In B6:

=ROUNDUP((B1-B2)/B3,2)

The result is 211.12. Here's why each part matters:

• (B1-B2) must be in brackets. Without them, Excel divides 1,200 by 18 first and subtracts that from 5,000.
• /B3 spreads the remaining 3,800 over 18 months: 211.111...
• ROUNDUP(..., 2) rounds up to cents.

Why not ROUND? ROUND gives 211.11, and 211.11 × 18 = 3,799.98. You'd finish two cents short. ROUNDUP guarantees the plan reaches the goal. The ROUND function guide and the rounding lesson compare ROUND, ROUNDUP and ROUNDDOWN side by side.

Step 3: Show progress as a percentage#

Progress is what you've saved divided by the goal. In B7:

=B2/B1

The result is 0.24. Format it as a percentage with Ctrl+Shift+% (Mac: Control+Shift+%) and it shows 24%.

To show what's left as a percentage, use =1-B7, which gives 76%. You can also add a simple in-cell bar: =REPT("|",ROUND(B7*20,0)) repeats a bar character once for every 5% saved, so 24% shows five bars. Update B2 each month and the percentage and the bar move with it.

Step 4: Add interest with PMT#

If your savings earn interest, you need to save a little less each month. PMT works out a fixed monthly amount for a given rate, number of months, starting balance and target. In B8:

=-PMT(B4/12,B3,-B2,B1)

The result is about 201.19, so wrap it in ROUNDUP to get 201.20: =ROUNDUP(-PMT(B4/12,B3,-B2,B1),2).

The arguments work like this:

• B4/12 is the monthly rate. Always divide a yearly rate by 12 for monthly saving.
• B3 is the number of monthly deposits.
• -B2 is the money already saved. It's negative because you pay it in.
• B1 is the goal you'll take out at the end.

PMT returns a negative number for money you pay in, so the minus sign in front makes the answer positive. This assumes the rate stays the same and interest is added monthly. Real accounts may pay interest differently, so treat the result as a guide.

Step 5: Build a month-by-month table#

A table proves the plan works and shows how the balance grows. Put headers in row 10: Month, Opening, Interest, Deposit, Closing. In row 11:

In row 12, type =A11+1 in A12 and =E11 in B12, then copy C11:E11 down. Fill everything to row 28 with Ctrl+D (Mac: ⌘D). The $ signs keep the rate and deposit fixed on their input cells.

The first three months, with interest and the 211.12 deposit:

Because this table uses the no-interest deposit and still adds interest, month 18 closes at 5,183.84, above the goal. Point D11 at the rounded-up PMT result (201.20) instead, and month 18 closes at 5,000.13, right on target.

Step 6: Test changes with percentages#

Percentages help you compare plans. If you can save 250 a month instead of 211.12, the increase is:

=(250-211.12)/211.12

That's about 18% more each month. At 250 a month with no interest, the months needed are =ROUNDUP((B1-B2)/250,0), which is 16 months: two months sooner. With 4% interest, Excel's NPER function, =NPER(B4/12,-250,-B2,B1), returns about 14.6, so 15 months.

The guide to increasing a number by a percentage shows how to raise the deposit by a set percent each year, and the percentages lesson covers percent change from scratch.

Common mistakes in a savings calculator#

These are the slips that most often give the wrong monthly amount:

1. Forgetting the brackets in (B1-B2)/B3.
2. Using the yearly rate as a monthly rate. 4% a year is 0.333% a month, so divide by 12.
3. Rounding down. ROUND or ROUNDDOWN can leave you a few cents short; ROUNDUP doesn't.
4. Mixed-up signs in PMT. If the result looks huge or negative, check that money paid in is negative and the goal is positive.
5. Typing numbers into formulas. Keep every input in a cell so you can change it later.

The same input-and-formula pattern works for other money tools. The payroll calculator in Excel uses it for pay and tax, and the discount calculator uses it for prices.

Practise it#

Do the exercise on this page: brackets, a division and ROUNDUP in one formula. Then practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet, and practise percentages and percent change so you can track progress and compare plans with confidence.