Building a Retirement Planning Spreadsheet In Numbers

Learn some Numbers spreadsheet skills by building a simple retirement planning table and chart. Learn how to use basic formulas to calculate how savings will grow with contributions and interest each year.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi, this is Gary at MacMost.com. Let's take a look at creating a retirement planning spreadsheet in Numbers. 
MacMost is brought to you thanks to a great group of more than 2000 supporters. Go to MacMost.com/Patreon. There you could read more about it. Join us and get exclusive content and course discounts. 
This isn't a tutorial about retirement planning. This is a tutorial about spreadsheets. But I'm going to use retirement planning as an example here. If you want real advice on retirement planning you should probably talk to a financial advisor. But here we're going to create a typical spreadsheet that some people may use to help in their planning. 
So in Numbers I'm going to create a blank spreadsheet here. I'm not going to use the Retirement Financial Spreadsheet that's a template. I'm going to just create something from scratch. So I've got various columns here and it kind of makes sense to maybe put the year in the first column and maybe your age in the second column. Just so every row here can represent a different year and you can use this as to how to predict where you'll be in your retirement savings on a given year. 
The next thing we want to have is your balance. This would be for your retirement account or all your retirement accounts, like that. Then we want to think about how you're going to gain money each year. So there are two different ways. One is to actually add more. We can put a Contribution column here. This how much money you'll put in that year. Then also, of course, the idea is you're going to earn interest. So we'll put that in the next column. We are going to want to see how much that interest is going to be every year, so Interest Earned. Then we will have the Ending Balance. So after that year goes by where you'll be standing. 
So let's say you're starting this year and let's say you're 50 years old. So we'll put those two values there. The first row is going to have these hard coded values in there so we have to start somewhere. Then let's say your Balance maybe you're starting, at this point you've got $50,000 put away and you're going to try this year to contribute another $10,000. Now Interest is a tough one. You basically have to go and kind of predict how much you think you'll earn every year. Looking at some stats I can see that a typical stock market return over the last 30 years averaged about 10% per year. So I'm going to use that as a number here. I'm actually going to put the percent symbol after 10 and press Return to make this a percentage value. If I actually select that I look at the actual value here you can see is 0.1, which is 10%. One would be 100%. 
Now we want to do some calculations. Interest earned and any balance aren't numbers we would enter in but things we can calculate from the rest here. So Interest Earned is going to be the balance here times the percentage interest. So I'm going to use the Equals Key to start entering a formula. Then I'm simply going to click on Balance and that will insert the cell reference there. Then I'm going to do an asterisk, which is multiply, and then click on the Interest cell to enter the cell reference there.  When I press return you can see I get 5,000 which is 10% of 50,000. Now for the ending balance we simply want to add the balance and then plus and then the contribution then plus the interest earned. Then we can see here the ending balance at the end of that year. 
Now let's go and fill in the other rows. So for Year instead of using an autofill or something like that I'm simply going to do a formula. So I'm going to use Equals there and I'm going to select the year in the row above and then do Plus one. Now I get the next year. The same thing for Age. So the row above plus one. Then the Balance. The Balance here for the start of the new year should be the ending balance here. So a very simple formula, I'm just going to use equals there and then select this cell reference and that's it. It is just a formal taking this value and doing nothing else with it. So I moved this 65,000 to here. Now for the Contribution let's say, just to start with, that we're going to have the same contribution every year and we're going to predict the same interest. So I'm going to do Equals and then the value above and I'm going to do Equals here and the value above. I could enter in 10,000 and 10% manually but instead I'm going to draw on the number above. You'll see why in a little bit. So to calculate interest earned we want the same formula but we want to move down a row. So what I'm going to do is I'm going to select this cell here, which remember has the formula C2 x E2. I'm going to Copy it. I'm going to go here and Paste it in and you can see, since I pasted it one row lower it changed the references to be C3 x E3. So it's going to correctly take the 65,000 value from here, from this row, not the 50,000 that it took in the previous row. Then the same thing here. I'm going to Copy and Paste and it is going to update all the cell references. Instead of referencing row 2 for all the cells it is going to reference row 3 for all the cells and you can see the colors here indicating which cells are referenced by this. So now we can see the amount growing here. 
Now this year is completely calculated by the values from the row above. So we can now take everything in here. Let's go and select from the first cell and I'm going to Shift click to select the last one or I can just click here on the 3 and it selects everything. I'm going to Copy. I'm going to go to this next row here and Paste. You can see how it incremented the year by one, the age by one. It took the balance from the previous ending balance. It took the contribution interest from the row above. Now these two calculations are done with cells in this row. We can just fill all the rest of this in. So I'm going to select this next row here, Copy. Then I'm going to select here and then Shift Click to select here, and Paste. It is going to paste in all of these rows. You can see here the Age increases all the way up 70, which is perfect. Exactly what we want because we're planning to retire at 70. So you can see the Balance increase, you can see the Contribution Interest are always taken from the row above so they are constant, and you can see here that the Interest earned is little bit more because the balance is more. Then Ending Balance is correctly calculated here.
Now let's format some things. I'm going to select row C here and I want to format it for Currency. But you know what? I only want the interest earned in Ending Balance to be Currency too. So I'm going to Command Click the row headings here. So I'm selecting these 3 rows. The Contribution as well. That's also a dollar amount. Now I'm going to go to Format, Cell and change the data format to Currency. I'm going to choose Accounting Style and put the comma there in the thousand separator and make sure it is set to two decimal places. As a matter of fact there's no reason to see cents so I'm just go to zero decimal places and now we just see the dollars here. So now everything looks a lot neater. 
If I look at the complete table here I can see that starting at age 50, contributing $10,000 per year, 10% interest estimated from the earnings and the balance grows and grows until the end of the 70th year and you can see it hits just over one million dollars.
Now remember how I had the Contribution values always referred to the row above it and the same thing for interest. So the neat thing about that is I could take a year here, and let's say while I plan for the next, oh, five years or something maybe to pay off some loans, things like that. Then after I'm in a more stable financial situation I want to change this value here to 12,000. I plan on at age 55 contributing more per year. So I'm going to change this to a manually entered 12, 000 and you see how it picked up the value in each of the next ones. So by having each row use the value above it I was able to manually enter one here and it automatically picked it up and I could increase the amount there. Let's say here in 2035 that is when a mortgage is paid off. So let's go ahead and say at that point 15,000 will be put away. So you could see how it grows more. With those increasing contributions here you could see how much a difference that made. In addition you could look at interest here and you could say, well, you know I think the stock market is going to do well over the next few years and I'm willing to take some risks there. But I'm going to cut back starting here and do 8% as my estimates there. The 8% will carry through and I'll see the results there. So you can kind of change these as you like to see different scenarios and see where you'll end up. Always try to look at this number as the number of what you're going for. So you may want to adjust your contribution levels here to get the right result you want there. 
So now let's complete this by creating a chart to go with it. So I'm going to move this table down a bit. I want to create a Line Chart. All I need to do is select the column I want to chart first. So I'm going to select the Ending Balance column. Then I'm going to go to Chart and select the line Chart here. It's going to insert here at the top. Let me move it over situated right here and grab the bottom right hand corner, like that, just to get it the format I want. So it took the ending balance because I selected that column and then it took the Year because that was the Header Column so it automatically used that here for the left side. So you can see how the funds grow over time. Now you could go to Format and then Axis here and one of the things you can do for the Y Axis is you could add a reference line. This could be handy because you could go and add a reference line Custom and let's say this is the goal here. The goal is one million, like that. So you could see how it puts the reference line there and you could see exactly where that hits based on all the current values. So I could change this. Let's say if I had put 20,000 here but maybe I could only do 18,000. At that point you could see where it hits the goal there. You could select other reference lines if you want as well. So I'll add another Custom Reference Line. This one, I'll call this, you know, the minimum amount here and say it is 600,000. So I can see where that line intersects to know that at that point I should have hit my minimum amount that I want in those accounts. 
That's a very basic spreadsheet table and chart to go with it for retirement planning but the skills that you used to build this could be used for lots of other things in Numbers. So I hope you found this useful. Thanks for watching.

Comments: 3 Responses to “Building a Retirement Planning Spreadsheet In Numbers”

Jeff

4 months ago

In the very first ad for his new computer, (he called it "Apple), Steve Jobs said: "Simplicity is the ultimate sophistication." Another clear and simple tutorial, Gary. Thanks!

Richard Barnsley

A spreadsheet to show folks how much
an item will really cost if they are living on credit card financing and only paying down a portion of the debt each month and in some cases losing ground on ever getting it paid off might be a helpful as well.

kathy kinan

gary. i just recently eliminated all the microsoft programs on my machine. this tutorial was amazingly timely. thank you.

Building a Retirement Planning Spreadsheet In Numbers

Comments: 3 Responses to “Building a Retirement Planning Spreadsheet In Numbers”

Leave a New Comment Related to "Building a Retirement Planning Spreadsheet In Numbers"

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information