How To Track Your Savings – The Saving Ninja Super Spreadsheet

I track my savings with the most powerful software in the world!!… Microsoft Excel!

Excel can do a whole lot more than most people think it can. As well as being an awesome data entry tool, you can enhance your sheet to the point of it being a fully featured form. You can even add Python and Visual Basic code to run complicated macros which calculate anything you want.

The beauty of Excel really shines when you have a lot of data, you can do tons with it! Animated pie charts, interactive graphs, anything! This is why it really shocks me when people use or even pay for external software to track their expenses and savings. With Excel, it’s fully customizable, so you really can get a tailored product. It’s pretty damn easy to do too… Sure you might spend a little bit of time Googling how to structure a certain formula, but you’ll get it in no time.

I made a mega Excel sheet especially for Saving Ninja readers. I wanted it to be as simple as possible to track your monthly savings, but also provide the most useful stats possible. I even went the extra mile and added some Visual Basic code to calculate the expected time in which you’ll reach financial independence.

The document assumes that you have a budget and you know what your annual expenses are expected to be. I have a separate Excel sheet to calculate my budget, but I’ll be writing about that in a different post. It also assumes that you know your house value (if you own a house) and your mortgage interest rate. I wanted to track my savings as effortlessly as possible, so I added formulas which use your mortgage interest rate to automatically calculate how much house equity you have paid off every month. The predicted investment growth is used to calculate your ‘Expected Time To FI’. This updates when you click the ‘calculate’ button. The Visual Basic code which runs when you click this button recursively simulates future months based on your average savings rate. It adds your predicted investment growth to your investment pots and also adds your house equity. The recursion stops when you’ve successfully got enough savings to retire on (using the 4% rule). I will increase the complexity of this in the future, and possibly add some customisability for the inputs.

This section of the Excel document also shows lots of useful information based off your savings so far such as total pot worth, total growth and total interest earnt (in the current year).

The next section of the document is where you’ll be updating your total pots each month. It will then use these figures to calculate how much interest you’ve earned and how much your pots have grown (or decreased). All of the orange boxes are user inputs; the rest is automatic. As you can see, the house equity (other than the starting figure) is calculated automatically based on your monthly contribution.

Finally, you’re monthly contribution section. Here you’ll enter how much you’ve contributed each month. It will use these figures to calculate your savings rate and figure out how much interest you’ve earned since the previous month.

New Additions

Draw Down Calculator

A lot of people will be trying to calculate how much money they need in their ISA to last them until they reach their pension pot. For this I built the Drawdown Calculator. This is detatched from all of the other data on the Spreadsheet (other than expected annual return). You simply add your pot and your yearly drawdown and hit ‘Calculate’. It then iterates month by month, drawing down and adding interest until you finally run out of money. It will then let you know how long it lasted in the ‘Years Until Depleted’ cell.

Compound Interest Calculator

Calculate all of your compound interest until your hearts content! Input your pot, yearly deposits and months of compounding and hit the calculate button.

I’ll be using this spreadsheet each month to update my savings and to help me share with you guys how much my savings have grown. I hope to make it into a bigger spreadsheet in the future, with lots more nice coding features and fancy graphs. When the new year starts, I’ll make a ‘Summary’ page which amalgamates the data from each year into one useful, graph-filled sheet. The editable copy of this spreadsheet (along with lots of other content) is available on the Secret Ninja Page which all Saving Ninja subscribers have access to. Subscribers will also get an email update whenever new features have been added. If you’re not yet a subscriber; subscribe below. It’s free, and you can always unsubscribe if you don’t want to keep in touch.

I’ll be using this page as a landing page for any special requests or questions regarding this spreadsheet. If you have any feedback or even if you’ve found it helpful, please do leave a comment below; I’d love to hear from you!


23 thoughts on “How To Track Your Savings – The Saving Ninja Super Spreadsheet

  1. Thanks for sharing this… how does it calculate savings rate without any input of income? Does it assume tax relief when inputting pension contributions or do we need to enter the amount including relief?

    Also I think there is a bug… from February onward the values in rows 51 and 52 only calculate if put a figure into row 50 (zero is fine, but you can’t leave it blank). For January it does work so I think you’ve fixed it already but not updated the rest of the year.

    Looks very interesting though I’ll need more time to figure it if it matches my own spreadsheets…

    1. Hi AJ,

      Awesome spot with the bug! I seemed to have this fixed in the ‘blank’ section but not in the ‘Savings – 2018’ section. I’ve just fixed this and uploaded v1.06 of the spreadsheet to the ninja page.

      The spreadsheet assumes that you’re investing all which aren’t part of your expenses input (cell B8) and it calculates the rate based off that. If your expenses are actually 10k per year but you’re saving 2k per year into your emergency pot or other pots which you don’t want to state as an investment, then just update your expenses by 2k and your savings rate will be correct. If you do want to count them as savings, you can put them in ‘other investments’.

      The pension contributions are for what is actually going into your investment portfolio. So if you’ve invested £1000, but you claim 40% back, you’ll still input £1000. Or the same if it’s salary sacrifice and you invest £1000 but it only costs you £600, you’ll still input £1000.

      I’m hoping to add some more fancy pie charts and graphs when I’ve collected more data, and maybe some more macros! Let me know if there’s anything you’d like added 🙂

      1. Spread sheet is brilliant one of the best I ever used which is clear and user friendly, your blog post are interesting and motivational. I some point I need to get to bed ha ha… keep up the great work good start .Respect!

        1. Thank you dude 🙂

          My next post has stick men in it. Should be up before the weekend starts 😛

          I’m glad you like the spreadsheet. I plan to improve it a lot more as I go on – when I’ve completed the first year I plan to add a lot of graphs and pie charts! More macros too.

          I’m also working on a mobile application to help you track your un-budgetted saving. This is about 65% done, but I’ve paused it for a while until I get into the right blogging flow.

    1. Hi Rob,

      The documents are on the Ninja page which is linked to in each subscriber email (and subscription confirmed email).

      If you wouldn’t like to subscribe or can’t find it, send me an email and I’ll send the file over to you.

  2. Hi Savings Ninja,

    Thanks for the spreadsheet, but when I click on the calculate button for the years until depleted calculator I get a “Microsoft Visual Basic” error message saying “Run-time error ‘6’: Overflow”. I’ve tried pressing debug, but it does not solve the error.



    1. Hey Calum!

      Hmm, what version of Microsoft Excel are you using? Also, could you tell me the figure in ‘Average Monthly Savings’, ‘Excluding House’ and everything you’ve put in the input areas at the top (House Value, Mortgage Interest etc.) You can email me this via the ‘contact’ section if you like.

      It may be overflowing because one of your input areas is 0 or not blank and I’ve not added a catch-all for this. Need to figure out which one though 🙂

      You may be able to find which one is causing the error by filling in some values for anything you’ve not entered (like a house) and seeing if it works. It should work better with 0’s instead of blanks.

      I don’t think I’ll be able to fix anything until the new year as I’m only on a Chromebook right now without Excel! But please let me know how the above stuff went.

    2. Hey! So, had a quick play with the Spreadsheet.

      The only way I can re-create this error is if I have 0 in the ‘Yearly Drawdown’ section. This is because the money will last an infinite amount of years due to being no drawdown.

      I can’t seem to re-create it any other way.

    1. Hi John,

      I’ve recently switched to a new mail provider and I’m having trouble getting my bottom subscribe box to work correctly. It won’t give you feedback, but you should still get the confirm email? Or are you not getting anything?



    2. Hey John, I’ve just checked and it says you’re an unconfirmed subscriber, so it worked – you just have to confirm by clicking on the email, it may be in your spam!

Leave a Reply

Your email address will not be published. Required fields are marked *