Description: The following passage is talking about retirement calculator, which shows you how to create a worksheet in Excel 2010 to calculate and analyze retirement savings over time. The tutorial includes calculating retirements savings with a company match plan.
Are you concerned about how much money you need to be saving each month to add to your retirement savings? I’m going to show you how to very easily create an Excel worksheet. I’m using Excel 2010. In this excel worksheet you can analyze how much you need to be saving each month.
We’ll calculate in a company match, we can see what will be of effect, I’m accumulating money over a 25-year period or 50-year period or in those catch-up year using the same worksheet, let’s get started, we will begin by opening up Excel, I’m using version 2010.
Once you get Excel open, it won’t be so boring, let’s format it a little bit and apply a theme, you see of the page Layout tab across the top is your theme, I like the technique theme, let’s scroll down until we find the technique theme.
Here it is, choose your technique thing, let’s widen out our columns, I select A, hold down my control key and select D, so I can change these columns at the same time, I’m going to change columns A and D to 15, I’m going to get back down between the columns into my mouse pointer changes to a cross error and drag it over till it says 15.
I am going to change column B to about 17, it doesn’t have to be right on it, that’s 17.25, let’s go with that, select C, hold down your control key and select F, let’s change the width to 13, once we add our information, if we need to widen them some more, we can do so.
Let’s enter retirement savings in cell B2, this is going to be our worksheet title, we’re going to merge and center this, select columns B through F, then we’re going to choose merge and center them, which will be on your Home tab in the alignment group.
This way retirement savings will be centered over columns B through F, now we’re going to apply the title cell style to be two, let’s apply your cell styles, let’s apply the title cell style, that enlarges, we put up a border around it, let’s change our font size to 36 in the font group, font size, I’m going to click the drop down arrow and change to 36.
These are a few more formatting steps, then we’re going to get to the good part, get to the formula, let’s change this background color to a light green, this is your field color in the font group, this field color will be your background color, click the drop down arrow and change it to light green.
If you don’t see this light green, these are theme colors, so the colors that you see in this palette are based on the theme that you’ve chosen, so if you didn’t choose technique theme, you may see different colors here, but I’m going to choose the light green for my background color.
Let’s add a border, you’re on borders, click the drop-down arrow, I’m going to go all the way down to more borders at the bottom, this looks like a good width, it’s about a medium sized folder, I want it to outline, choose the center one outline and click OK.
We’ll click off to look at it, that looks good, that gives us a nice format source, worksheet will not be so boring, we need some row headings, we’re going to put some row headings in column B and enter your data in column C, some more row headings in column D, some data in column E.
Let’s begin in cell B4 and put name, down arrow in B5 put annual salary, then in B6 let’s put percent to invest, then in B7 enter in company match, if you have a company match plan, what percentage do they invest? Next is annual return, what annual return do you expect in years?
We’ll be able to manipulate all this data and change the number of years, we can see how that will affect your income and the money that you accumulate, you can change the annual return, change how much the percentage that you invest or how much your company will match, we can play with this data and see how that will affect the accumulation over time.
Let’s enter some row headings in column D, we want to leave our column C blank now, we are going to enter some data there in a minute, but we need some row headings in column D, in D4 I put monthly contributions, in D5 we want to put the employee that would be you, then in D6 we put in the employer which is your company.
Next is the total, then in D8 we want future value, now we’re ready to enter some data, let’s open column C4, put your name, I’m going to put John Smith, for your annual salary I’m going to say that John Smith is going to make fifty five thousand dollars annually.
He wants to invest fifteen percent of his check each month, the company that he works for will match three percent, he wants to invest where he expects to receive an annual return of at least 12 percent, for over a period of 35 years John Smith is a young person, so he’s going to plan to work 35 years and invest 35 more years.
We’re ready now to calculate the monthly contribution of the employee, I’m going to calculate John Smith monthly contribution, to do that I would take his annual salary times the percent that he’s going to invest and divide it by twelve months in a year, let’s put in that formula, we always begin our formula with an equal sign.
On equal I’m going to use the point and click method, equal the annual salary, when I click on it, it puts C5 there, we use our little times symbol shift percent invested, I will click on the percent invested the 15% and divided by 12 because of the 12 months in the year, complete that entry, we’ll see that his monthly contribution would be six hundred eighty seven dollars and fifty cents.
We’re going to calculate the company match, what the employer will contribute to your savings, we’re going to use what’s called an if function, we’re going to say if you are John Smith, if you are invested less than the company match, then we will say his annual salary times that one or two percent but if he invest or you invest more than this, the company will match this three percent.
Because he’s only going to match up to three percent, we’ll take the company match three percent times the annual salary divided by twelve, let’s put in that formula, we always start with an equal sign, I form if and put in your opening parenthesis.
We’re going to use the point-and-click C6, the percent of the investment is less than symbol C7, the company match, if that is true, put a comma there, I’ll put the true statement, if that is true, we’re going to put C6 times C5, the annual salary divided by your forward slash twelve, twelve months in a year, now you need another comma.
This is our false statement if C6 is less than C7, then we complete that entry by clicking your check, so you see now that the employer will match up to 3 percent, that will be one hundred and thirty seven dollars and fifty cents a month on John’s salary of $55,000 a year.
Let’s calculate the total monthly contributions, that’s easy to do, we add your contribution and the employer’s contribution to get the total, we can do that several different ways, I’m going to use the autosum, put the formula in, for me it equals sum E5 and E6, I click the check to complete that.
The total contribution between what the employee takes out of his check the 15% and the company match of three percent gives us eight hundred and twenty-five dollars a month, let’s quickly calculate our future value, to do that we’re going to use the future value function.
We begin always with an equal sign, FV is our future value, open up the parentheses, we’re going to need three arguments in our future value function, we want to say C8 which is our annual return divided by twelve, twelve months in a year, then enter comma, that’s our first argument.
Then put in twelve times C9 which is your number of years, then put your comma, then we want the total, so that would be E7, click on E7, then complete the entry, we don’t want this negative because that’s going to be a positive number.
To get that positive, put at the negative symbol before your future value function, add the negative symbol, we have five million three hundred and five thousand five hundred forty one dollars at retirement, it is not bad, you can manipulate this, play around with this, change this as long as you have your formulas.
You can change your annual salary, change the percent you’re going to invest, you can change the company match, you can change all these values, it will automatically recalculate, if we change the 55,000 to 98 thousand, you’ve seen how the values changed, all the values changed.