What is in the file
The sheet has the inputs at the top and then one row per year: the deposits made in the year, the interest earned in the year and the balance at its end. The example is 10,000 to start and 500 a month at 7% for 20 years, which ends at 292,465. Of that, 130,000 is money paid in and 162,465 is interest, more than half. None of it is typed in as a number. Every cell of the table is a formula pointing at the blue cells.
It is a plain .xlsx with no macros, so there is no macro warning to click through. It opens in Excel, Google Sheets, Numbers and LibreOffice.
Changing the numbers
The blue cells are the only ones you need to touch: the starting amount, the monthly deposit, the annual return and the number of years. The return is a percentage, 7% or 0.07, and means the effective annual return, what the money actually grows by in a year. The sheet derives the monthly rate that gives exactly that over twelve months, so 7% a year does not quietly become 7.23% for being split into months.
Set the starting amount to 0 to see what the monthly deposits do on their own. Set the deposit to 0 to see a lump sum grow by itself. Change the number of years and the table grows or shrinks with them.
The formulas in the cells
A year’s balance is computed directly, not year by year from the row above, with Excel’s FV: it takes the monthly rate, the number of months to the end of the year, the monthly deposit and the starting amount, and gives what the whole is worth then. This is the shape of the formula:
=FV((1+return)^(1/12)-1, 12*year, -deposit, -start)
The deposits in a year are twelve times the monthly deposit, and on row zero the starting amount. The interest in the year is the balance minus last year’s balance minus the year’s deposits: what the money grew by on its own. A localized Excel shows FV under its own name the moment the file opens.
Computing every year directly matters if you want to build on the sheet: a row can be deleted or copied without disturbing the others, since no cell leans on the row above except to find the year’s interest.
The minus signs
Excel tells money going in from money coming out by the sign. In FV the deposit and the starting amount are money you pay in, so they are negative, and the answer then comes out positive. Flip their signs and the answer flips too. It is not a bug, just Excel’s way of keeping track of direction.
Checked in three programs
Every time the export changes, the file is recalculated and compared with our own engine in Excel’s syntax, in LibreOffice and in Apple Numbers, with the file opened for real and every cell compared. A sheet doing the same thing in all three programs is not a given.
Opening it in Google Sheets, Numbers or LibreOffice
- Google Sheets: File → Import, choose the file, and choose to create a new spreadsheet.
- Numbers: double-click the file. Numbers recalculates everything on opening.
- LibreOffice Calc: open the file. LibreOffice shows the stored numbers until you change a cell, or until you set “Always recalculate on file load” under Tools → Options → LibreOffice Calc → Formula. The stored numbers are the ones the formulas give.
Want your own numbers right away?
The compound interest calculator runs the same plan on the page, compares two plans side by side, shows what happens once you start spending the money, and downloads that exact plan as Excel with your numbers already filled in.
Frequently asked questions
Why does the table start at year 0?
Row zero is the starting position: what you put in on day one, before any return has arrived. Year 1 is the balance after twelve months. That way every row shows both what went in during the year and what the year earned.
Can I include withdrawals?
Not in the template. The calculator on the page has a spending phase, a fixed amount or a percentage a year from a chosen year, but that plan does not follow a single formula and so downloads as values rather than formulas.
Why is it not the same as my bank’s figure?
Banks usually count actual days, and sometimes use a nominal rate divided by twelve rather than an effective one. The difference is small over a year and visible over twenty. The template uses an effective annual return, the same model as the compound interest calculator: a lump sum grows by exactly the rate you type in one year.