Savings plan template for Excel

Savings plan template for Excel

Saving money rarely fails for want of intention; it fails for want of a number. Most people know they want to save, but they do not know how much to put aside each month to reach a goal by a given date, or how far along they are today. Without that figure, saving stays an intention that breaks in the first hard month.

This file answers that one question. You type the name of the goal, its amount, the balance you have already set aside and the two dates; the sheet works out the months available, the deposit needed per month, the progress towards the goal, the estimated completion date and the status of the plan. Below that it projects twelve months with opening balance, deposit, return, withdrawal and closing balance, and a second sheet logs every movement with the balance left after it.

It works just as well for a family saving for a home repair, a couple setting money aside for a trip, someone building an emergency fund, or someone who wants to replace a car or pay for studies without borrowing. It is a free Excel template, it also opens in LibreOffice and it needs no sign-up: download the file and work on it.

⬇ Download the template (Excel .xlsx)

What it is and what it is for

• It turns a vague goal — one day I would like to save — into a monthly deposit that is concrete and easy to review.
• It counts the months between the start date and the target date without you having to count them by hand.
• It spreads what is still missing across those months and rounds the figure up, so the plan does not fall short over a rounding difference.
• It shows progress towards the goal as a share of the total, instead of a loose balance that says nothing.
• It projects twelve months of balance with the deposits and withdrawals you type, so you can see whether the plan survives real life.
• It keeps a log of every deposit with its date, its method and the running balance, which is what lets you review the plan later without arguing.

What the file contains

The file is deliberately short: three sheets and no hidden formulas. This is what each one does.

There are no protected cells and no passwords. The sheets can be renamed, extra rows can be added to the projection and the column headings can be rewritten without breaking anything. What is better left alone is the column structure of the plan control, because the deposit needed, the progress towards the goal and the estimated completion date are calculated from those cells. And if a cell is left blank, the sheet reads it as zero, so there is no need to type filler zeros to make the figures work.

Worked example

A twelve-month plan with round figures takes two minutes to understand. The goal is 12,000,000, there are already 3,000,000 set aside and twelve months remain until the target date.

What is still missing is 9,000,000. Spread across the twelve months available, the deposit needed per month is 750,000. The balance you already hold covers one quarter of the goal, so the plan starts with a quarter of the road done and three quarters ahead. With deposits of 750,000 every month, the projection closes month 12 exactly on the goal.

This is how the projection looks at three points of the year. The deposit and withdrawal columns are typed in; the opening balance, the return and the closing balance calculate themselves.

In the example the annual return sits at zero, and that is why the closing balance is a plain sum: each month the deposit goes in and nothing else. If you fill that cell in — with a conservative annual return in your own currency, for instance — the projection applies it month after month to the opening balance, so the later months grow a little more than the early ones and the closing balance stops being a plain sum of deposits. That is the detail that explains why the closing figure can come out above the sum you worked out in your head.

How to use it, step by step

1. Download the file and save it under a name you recognise, for example the name of your goal.
2. Type the name of the goal. A concrete name works better than a generic one: kitchen repair says more than savings.
3. Type the goal amount and the current balance, which is the money already set aside for that purpose.
4. Type the start date and the target date. Those two dates produce the months available.
5. Read the cells that calculate themselves: months available, deposit needed per month, progress towards the goal and estimated completion date.
6. Check whether the deposit needed is realistic for your normal month. If it is not, move the target date or the goal amount; the figure not to move is the deposit, because that is the one you pay every month.
7. If you already know how much you can put aside, type it into the deposit column of the projection and add any withdrawals you expect. The sheet will tell you whether that gets you there, gets you there with nothing to spare, or falls short.
8. Write each movement in the deposits sheet when it happens, not from memory at the end of the month, and leave the running balance as your check.

How to read the result

The first thing to look at is the status of the plan. If it says on track, the planned deposit covers the one needed; if it says behind, the planned deposit is not enough and one of the two variables has to move: the date or the goal amount.

The second is the estimated completion date: it is the real date you would arrive at if you keep the deposit you typed, and it rarely matches the target date you had in mind at the start. That gap, when it appears, is the most useful piece of information on the sheet.

Then come the control figures. Progress towards the goal is shown as a share of the total, with no loose numbers, and it is there to place you; that cell is not added to anything. The return column of the projection also calculates itself and, while the annual return sits at zero, it stays at zero all year.

It is also worth comparing the month-by-month projection with the deposits sheet. The projection says what should happen if the deposit is kept up; the log says what actually happened. When the two drift apart for two or three months in a row, the gap is no longer the slip of one weak month: it means the planned deposit has fallen below the one needed, and it is better to correct that before the difference grows. Reviewing the plan once a month, with the log in front of you, takes less than five minutes.

Common mistakes

• Fixing the target date before looking at the deposit needed, and finding out late that the plan was impossible from the start.
• Typing an optimistic annual return into the return cell: the projection applies it month after month and the closing balance looks better than it is.
• Leaving the deposits sheet empty and trusting memory. With no log there is no way to know whether the real figures match the planned ones.
• Writing withdrawals in the notes instead of the withdrawal column, after which the running balance stops adding up.
• Adding the progress cell to the other figures, as if it were an amount and not a share.
• Dropping the plan in the first month you cannot deposit, instead of writing down the weak month and catching up later.

When a system is worth it

This file is an internal control tool and, for a personal or family goal, it is all you need. When saving turns into a steady stream of movements — several deposits a week, withdrawals that have to be justified, two or three goals open at the same time and the need to square that money against the day-to-day running of a business — the sheet starts to fall short and it is worth moving to a system that keeps every movement with its date and its method, and lets you check the balance without opening the file. That is the ground Kardex Tauro covers, and you do not have to get there to start saving seriously.

⬇ Download the template (Excel .xlsx)