This is one of the most popular retirement calculator spreadsheets I have made a tutorial for. Here is the original video, and below is a full written guide on how to make it!
Ah, this is a nice looking calculator, isn’t it?
Building this for yourself is not too difficult, it can be done in just a few minutes! The next section is a step-by-step guide on how I made this. The goal of this calculator is to consider your annual expenses, any annual income, time horizon, growth rate, and finally anything you’d like to leave behind to help decide if your account value is in the neighborhood of where it needs to be to consider retirement. Not to mention the, at least in my opinion very cool, “Do I run out?” feature.
Finding your annual expense # is crucial, whether you make this calculator or not it is quite helpful to know how much money you are spending every year.
This can take some time to find/calculate, but many banking and budgeting apps & websites can help make this task a lot easier. Income should be pretty straightforward, no explanation needed there. Current account value, same as the above. Years planned gets tricky, this is where that nice disclaimer paragraph you carefully read earlier comes in, consult a professional.
The purpose of this calculator is to help you run through a few simulations and get an idea of roughly where you stand and maybe to project what things could be like in the future. Again, worth consulting a professional on this one or maybe a doctor) as everybody’s situation is different. Echoing the above for the annual growth rate, there are averages out there on the internet you can use. You can also simply calculate your actual rate of return in previous years to use as a rough estimate. Account performance is constantly changing and can vary significantly, something you can actually use this calculator to project or simulate as well.
Let’s Build It
No spreadsheet math required here, just add your own numbers!
There is some math here:
The first column is simply years out (from today), go down as far as you’d like. I like to do more years than needed so that if things change down the line or you want to simulate multiple possibilities, you don’t have to waste time copying things down again. I made a mistake in this video, the first year should have been 0, we all make mistakes so that’s okay.
The 0 years out value is simply what your account value is sitting at today. Next, we have our first formula (below). We have to take the previous year’s value, subtract expenses, add in any income, then multiply that value (have to use those parentheses first, PEMDAS) by 1 plus our growth rate.
Please note, this assumes that you take that full annual expense amount out on Jan 1 and your account grows all year without it. As mentioned before, these calculators are highly simplified for educational purposes and these things can be vastly more complex if we wanted to get very specific and take withdrawals throughout the year.
Let’s take a look at the formula:
Isn’t it cool how the result of the formula hovers up there before you even press enter? Nice. The color coding is really helpful as well.
It’s super easy in most spreadsheet tools, just highlight your data & headers, click insert, and select chart. Traditionally, it is a lot easier to read if you put time on the X axis & account value on the Y axis. Also label your axes, common graphing etiquette.
This is fully customizable, go wild.
The All Important, “Do I Run Out?” Feature:
This is where the rubber meets the road. Love some embedded formulas. If you’d like to learn to make these formals yourself, a certain Retirement Calc Guy has some really cool videos out there to help you learn!
It’s beautiful, isn’t it? Moving from the inside to the outside, the main formula in here, the workhorse if you will, is the Vlookup. We’re basically just asking the formula to take whatever we put in the, “Years Planned” cell & find that number amongst the leftmost column in our table to the right.
Once it finds that value, we want it to return the corresponding value just to the right of it in column 2 of the table (hence the, “2” in the formula). The 0 at the end of the formula just tells the sheet to not worry about finding an exact match, I’m not going to get into why to use the 0, just use it. The result of the vlookup by itself actually gives us $699,719.37 in this example.
That is where the formula preceding the vlookup comes in, the all-important If formula (another one that I have a video tutorial out there on). The If is fairly simple, there are just 3 things it needs: a criteria to check, what it should spit out if the criteria is true, and what to say if it is not true. In our example here, we are asking it to check whether the value given to us by the vlookup is less than our ending value or not. If it is not, we want it to say, “No”. If it is less than the ending value that we want our account to not dip below, it should say, “Yes”.
More simply put, do I run out, yes or no?
The final Iferror formula is not necessarily required, it is more of a personal preference. I can’t stand these:
So I like to add this in to basically tell the spreadsheet that instead of giving me an error, I’d prefer to see “”. Otherwise known as a blank cell. The IfError formula is quite similar to the If formula (shocking). It needs a criteria to check first. If that criteria causes an error, it just needs to know what you want it to say. In my case, I always just use “” to return a blank cell.
That’s it, not too bad!
Thank you for taking the time to read this. If you have any questions/comments/concerns, never hesitate to reach out.