Description: The following passage mainly focuses on retirement calculator, which shows you how to build a calculator in Microsoft Excel that will help the user to determine how much money they need to have saved by retirement if they would like to maintain a given life style for a given number of years.
Thank you for reading this finance with Excel article, we are going to create a excel tool that helps us to to gauge our retirement needs, if we are expecting to retire in 40 years and we’ve got 20 years we’re expecting in retirement, we want to have the equivalent of $60,000 per year income throughout all of retirement.
This is how much money we’re going to need at retirement, this is highlighted in yellow because we have to calculate this in a few different ways, I’ll show you how to do that, we use this table that shows in each period each month of our retirement what our income is going to need to be, how much our monthly income is going to need to be to manage our lifestyle and how much our retirement savings are going to be in that period.
Let me show you how to build this, let’s start with the input, I put the inputs here which are years to retirement, length of retirement desired, retired annual income in today’s dollars, even if we expect inflation to be 2% for the next 40 years, it’s difficult for us to calculate in our minds like what would $60,000 be 40 years from now with a 2% inflation.
So we’re going to calculate that, put in what you want your income to be in today’s dollars, you’ve expected inflation, expect your retirement savings, then I highlighted this yellow because this is the number that we’re going to fiddle with to to see if we have enough assets to do we want to do.
We have desired assets at the end retirement, for example if you want to leave some money to your kids or you want to make sure that you have a little bit of cushion in case your retirement goes longer than 20 years, actual assets at the end of retirement is going to be something that we calculate.
It’ll be given if we have fifty thousand dollars, we’re trying to pull a certain amount out per month, how much is that going to leave us at the end of retirement? That gives us that, we need to add the column indicators for our actual calculator.
I add a retirement period monthly, retirement income, retired savings and indicator, indicator is something that will pull out the number for the last entry, this retirement period is going to be monthly with the months of our retirement.
Let me put in some dummy numbers, let’s say we have forty years to retirement, length retirements are going to be twenty years, we want to have $60,000 at the end expected average inflation over the next forty years, we expect to be two percent return on savings.
We’re investing in bonds, for assets at retirement we start with a million dollars, desired assets at the end of retirement let’s say we want to end with one hundred thousand dollars that we can give to our kids, the actual assets will calculate that in a second.
Now let’s do some formatting, we want these to be in percents, I have a couple of decimal places, we want these to be in dollars, those are fine as regular numbers, for this retirement period we are using a number, in this case 20 times 12, because that’s the number of periods we expect to be retired.
We can go one, then we’re going to use an IF statement, I say IF one plus whatever is in the previous cells that increased the previous cell by one is greater than 12 times 20, make sure that you anchor that you are dragging this down.
If one plus the previous number is greater than the total number of periods in retirement, give me a blank, stop counting, if not, give me one plus the previous number, in essence all this is saying is if one plus the previous number is greater than the total of retirement numbers, stop counting, but if not, keep counting.
We can look at that, if we drag that down, we can drag it down as far as we want, we notice that it stops counting when we want it to stop counting , it stops at 240, however we have to deal with this value thing, the reason or the value is that this cell is blank, it’s blanking the way I’ve written the code, it’s defined to be text.
I get rid of those blanks, I use an if error statement, deciders wrap this whole statement in if error, I say if error, the FA are saying if this whole thing gives you an error, then return something else, if not, then it automatically knows to return whatever is in that particular cell.
To calculate the monthly retirement income that we want, we’re going to use the future value function, we’re going to say equals FV, that gives us the future value which is your standard future value function that you learned in your finance classes.
We want future value $60,000 per year but we’re going to do monthly, so it’s 60,000 divided by 12, in this case the rate is going to be the inflation rate because we want to say $60,000 a year with a consumption today which is going to be how many dollars worth of consumption in the future.
This is going to be 2%, make sure that you anchor there divided by 12 because we’re doing monthly compounding, the number of periods is going to be 40, make sure that you anchor there times 12 plus whatever the retirement period is because inflation is still going to keep happening while you’re in your retirement.
You’re going to be increasing your number of your monthly withdrawals as time goes on so that your consumption can stay flat as inflation continues to increase, the payment is going to be zero, then the present value issue is going to be 60 thousand divided by 12, make sure we anchor on that 60,000, then you close that, then you can hit enter.
You notice that it is giving us a negative number, that’s simply because of the way Excel calculates this, if you put a positive number in here, it’s thinking that we’re calculating loans and loan repayments or bonds and our bonds repayments, so it’s saying that you’re getting five thousand dollars here which means that you’re going to have to pay.
Don’t worry about that too much, put a negative sign in front, then you’ll get a positive number, we’re going to want our monthly retirement income to be eleven thousand one hundred and thirty eight, what we’re going to notice is once we get down, I’m going to make this easy, I’m going to put one, so we can see easily.
Once we get down to the past here this formula, if we drag it down, it is going to error, we have to do the exact same thing with this formula, what we do with our other formulas is to wrap it in an if error function, this gives me a blank.
We can drag that all the way down, we have a dynamic formula that tells us exactly how much we need or how much we are going to want to withdraw every month, I think that the decimal places are a little bit superfluous here, so I’m going to get rid of them because they don’t matter.
Let’s change this back in twenty for example for a 20-year retirement, to calculate how much we have in our retirement savings, this is going to be how much we have in our account at the end of the month, the beginning of month one we’re going to end up having this much money in our retirement account.
It’s going to be this amount, we’re going to withdraw the amount that we have for our retirement income, this amount here is going to end up compounding, we’ll assume that we have 5% monthly compounding, it’s going to compound, so it’s going to be times 1 plus 5 percent divided by 12.
At the end of the month we’re going to end up having this much money, let me make this into dollars so that we can see better, now we need to calculate what it’s going to be for the next month, it’s the amount that we’re going to have at the end of the month.
It is going to equal the exact same things, we start with this much, we’re taking out our monthly amount, that amount is going to compound by 5 percent monthly, divided by 12, we have to make sure that we anchor here and we can drag.
You notice that I’m not anchoring here because I want those two cells to drag down, but I am anchoring here because I want to stay hooked there forever, I drag this number down, but there’s a problem because the way I have this formula written, we also have this error problem.
We have to use an if error statement again, we could also use a statement that says something along lines of if, either of these two is blank, return a blank, but I’ll use an if error statement and drag that all the way down, now if we look at this, it tells us that how much money will be in our retirement account.
We’ll be down 2.751 million dollars, so we don’t have enough money in our retirement account, last thing we need to do is this end indicator, in this end indicator we need to go through here and pick out what is the ending amount, there are a couple of ways to do this.
I’m going to say equals if, retirement period equals the max overall retirement periods, make sure you anchor that, then give me the value of the retirement savings, if not give me a blank, if we drag this down, let’s put one here, we can look at this.
Let me put that into dollars, so we can see it better, this is exactly how much are our retirement account will be worth at the end, then actual assets at the end of retirement is going to equal the sum of everything in this column which is going to be this number here.
We’ll always bring back the exact number, this calculator is finished, I click 20, I want a particular lifestyle, I’m going to use some conditional formatting here, if the actual assets are greater than the desired assets at the end, then I want this to turn green.
If they’re less than the desired assets at the end, I want this to turn red, I’m going to click on this cell, I’m going to go to conditional formatting, then I want to be greater than this cell with green, I want to highlight cells less than this particular number with red.
We can look at this, we don’t have enough money to make our retirement, we can fiddle with this, what if I have three million dollars in our retirement account, I have enough money to do what I want to do with my retirement, I need two million dollars, I barely don’t have enough money in my retirement accounts, we can fiddle with this, we can see how much money we end up needing to meet our retirement goals, thanks for reading.