hpr-knowledge-base/hpr_transcripts/hpr1525.txt

Episode: 1525
Title: HPR1525: 30 - LibreOffice Calc - A Savings Model
Source: https://hub.hackerpublicradio.org/ccdn.php?filename=/eps/hpr1525/hpr1525.mp3
Transcribed: 2025-10-18 04:38:17

---

music
Hello, this is Ahuka, welcoming you to Hacker Public Radio in another exciting episode.
In our ongoing series on LibreOffice, for now focusing on the program Calc, the spreadsheet
program.
And in the last tutorial that we did, we talked about the fundamental ideas of building
models and doing what-if analysis.
Now that was at kind of a high level, so what I want to do now is take these ideas and
put them into practice so you can see exactly how this technique works.
Now to do this, I will create a simple model of savings over time.
I do want to be clear that this is a very oversimplified model and should not be taken
as a good predictor of actual results.
The idea here is to illustrate the techniques involved in building a model and doing what-if
analysis.
So what are the variables, parameters, assumptions, etc.
Whatever you want to call them that we need for this.
I've identified these in my model.
The first is an initial amount of money already saved.
This is the amount you start with.
Then an amount of money that you add to your savings each year.
And finally, the rate of return on your savings.
And that is it.
As I said, this is a very simple model.
And to make it even simpler, I will assume that the added savings you put in your account
is always added as a lump sum on December 31st.
This lets me avoid the added complication of compounding.
I know this is not realistic, but again, please remember, the purpose of this exercise is
to show you how to build the model and do the analysis.
The numbers will vary when we do our what-if analysis, but for now I will use these numbers.
For the initial amount already saved, I'm going to assume 0.
Someone's starting from scratch.
Then for an annual addition, I'm going to put in 1000.
So I'm assuming you can save $1,000, $1,000, $1,000, $1,000, $1,000, whatever.
And again, this goes in December 31st as a lump sum.
The rate of return, I'm going to put in as 0.05 or 5%.
And again, I'm just going to simplify this, that this is an interest payment that is calculated
on December 31st and added to your account.
So here's how I do it.
Well, first, I click on cell I1 to select it, and I enter the word assumptions.
And I bold the word.
But when I enter my assumptions, I know I'm going to need two columns, one for the description
and one for the actual number.
So while I have cell I1 selected, I hold down the shift key and click on cell J1.
These are obviously adjacent cells.
Now with both cells selected, I can use the merge and center cells button.
Now this is found on the formatting toolbar, just above the input line.
And it's a little hard to see.
First of all, if you have not already selected two adjacent cells, it's going to be grayed
out.
You know, there's nothing to do until you've actually selected two adjacent cells.
When you do, look for something that has a 3x3 grid with a blue two-headed arrow in
the middle, that's the merge and center cells button.
When you click it, well, you know, does what it says.
It combines the cells into one and centers whatever is in them.
This is a good technique for putting a heading on top of a group of columns, which of course
is what I'm doing here.
Then in cell I2, I enter initial amount and in cell J2, I enter zero.
In cell I3, I enter annual addition and in cell J3, I enter 1000.
And in cell I4, I enter rate of return and in cell J4, I enter 0.05.
Now I have completed entering all of my assumptions, and I can move to the main model and start
building that.
This simple model is actually pretty easy.
In cell A1, I enter the word year.
In B1, I enter beginning amount.
In C1, I enter interest earned.
In D1, I enter annual addition and in E1, I enter ending amount.
So I've put in a heading row that covers the first five columns of the sheet.
But to make it a little prettier, I click on cell A1 to select it.
Hold down the shift key and click on cell E1.
I now have all five of my header cells selected and then I click on the bold button and the
center button on the formatting toolbar.
That just makes it look a little better in my opinion.
Now to fill in the model, I start in cell A2.
This will be the first year of the savings program so I can enter first here.
Calc actually knows all about ordinal numbers.
Those are numbers first, second, third.
All of those are called ordinals.
And you have numbers like 1, 2, and 3, those are called cardinal numbers.
And Calc knows all about both.
Then in cell B2, I put in the initial amount.
Since that is in my assumptions area, I can enter the cell address so it goes in as equals
J2.
Or it could.
But one useful thing to remember is that any reference to the assumptions area is one
that should never change if you later do a fill.
So in fact, cell B2 should contain equals dollar sign J, dollar sign 2.
In cell C2, I want to calculate a return on my savings.
In our simplified model, there is no compounding going on.
Whatever money we have at the beginning of the year earns interest at the rate of return
and is deposited into our account on December 31st.
So the formula is simple.
We multiply the amount in column B by the rate of return and that is the interest earned.
In the first year, that is the amount in cell B2 multiplied by the rate of return, which
is in our assumptions area in cell J4.
As before, any cell reference to the assumptions area must be a fixed reference.
So the formula that goes into cell C2 should read equals B2, asterisk, dollar sign J, dollar
sign 4.
Now, cell D2 is where we add to our savings each year, which we assume for simplicity
again.
This is a lump sum added on December 31st.
This amount is stored in our assumptions area in cell J3.
So in cell D2, we just make a reference to this.
And since it is in our assumptions area, we make it a fixed reference.
So cell D2 will contain equals dollar sign J, dollar sign 3.
The last cell on this row is E2.
And it adds up the amount of money you had at the beginning of the year, plus the interest
you earned on December 31st, plus the amount you added to your savings is a lump sum on
December 31st.
When these are added together, you get your ending amount for the year.
So cell E2 will read equals B2 plus C2 plus D2.
This ends the first year in completes row 2 with a model.
We could go cell by cell through the spreadsheet, filling in each cell manually, but that's not
the best way to do this.
We want to do a fill, but we have one more thing to do.
You see, cell B3 is not like cell B2.
Cell B2 is filled with an initial amount from the assumptions, but at the beginning of
the second year, we need to take into account what happened during the first year.
And that means that the amount we have at the beginning of the second year is equal
to the amount we had when the first year ended.
And that is in cell E2.
So in cell B3, we enter the address equals E2.
Now we can click and drag to fill the columns.
When I first developed this simple model, this was for college students, and these students
were at an average age of 20, so I wanted to make a point about how savings over time
works.
So I usually carried the model out 40 years.
At roughly the amount of time most students would end up spending and working before
they retire.
So I will go to cell A2, which right now has the label first.
If I click on it to select it, then use the fill handle to fill the column that turns
out that Calc is ready for this, and fills the column with successive ordinal numbers.
By watching the black square that goes with my cursor, I can easily do a fill up to the
40th year.
When I do this, I get a column full of ordinals, and this looks great.
Now I go to cell B3 and select it, and do a fill of this column, but this is puzzling.
In cell B3, I had the number 1000, but all of the other cells now read zero.
Why is this?
Well, let's take a look.
Click on cell B4, which has a zero in it, and see what it contains.
If we do, we see that it reads equals E3, and cell B5 has equals E4, and so on.
What happened is that the B column is referencing other cells that are empty right now.
Each cell in this model is tied to the other cells, and the model won't make any sense
at all until it's complete.
So let's fill the other columns.
To make this go more quickly, I click on cell C2 to select it, hold down the shift key,
then click on cell E2, with all three cells selected, that's C2, D2, and E2.
The fill handle is on cell E2, so I fill all three of those columns.
Now I have the model completed, and all of the cells are filled with meaningful data.
But it looks like I have too many decimal places here.
Money never needs more than two decimal places.
I want to change how the numbers are formatted.
In this case, I think the best thing is to change it for the columns.
That way, if I wanted to add more years, more rows to the column later, I would have
my formatting consistent.
So I click on the letter B at the top of column B, this selects the entire column, then
I hold down the shift key and click on the letter E.
I now have all four columns, B, C, D, and E selected.
I go to the format menu, select cells, then currency.
I click OK, and now my numbers are proper monetary amounts.
In fact, they're all in dollars because Libraf is calc, already knows I live in the
United States and assumes that that's the currency unit that I want to use.
Now one thing that can go wrong if you are not careful, if you did not make a fixed
or what is often referred to as an absolute cell reference when you first entered your
formulas in cell C2 and D2, when you did the fill, your numbers would get bad in a hurry.
If you had left out the absolute reference in cell C2, for instance, when you did your
fill, cell C3 would contain equals B3 times J5 instead of equals B3 times dollar sign J
dollar sign 4.
And since cell J5 is empty, cell C3, and indeed all of the other cells in column C would be
showing zeros.
So if you get funny looking results, you have to check your cell contents to see where
the problem occurred.
Remember that we made a distinction between what a cell contains and what it displays.
And by clicking on a cell and looking at the input line in the formula bar, you can quickly
find the error.
Returning to our spreadsheet, we see that by saving $1,000 a year for 40 years and
receiving a 5% rate of return, we end up with about $120,000 at the end of 40 years.
So we tripled our money.
We saved $40,000, we wind up with $120,000.
Okay, not bad, but also not a lot if you plan to live off of it in your retirement.
So what's the best way to improve our result?
We have three numbers in our assumption area, let's do some analysis.
So to do the what if analysis, all we need to do is copy our sheet.
To examine changes to all three of our assumptions, we need three more copies.
So hold down the control key or the option key if you're on a Mac.
Click on the tab for the sheet you just completed.
It probably says sheet one if you didn't rename it, then drag it to the right and let
go the mouse.
You should have a duplicate sheet that says sheet one underscore two.
Do it one more time and you get sheet one underscore three and one last time gets you sheet
underscore four.
Since these are not very descriptive, I will rename sheet one to base model, sheet one
underscore two to high start, sheet one underscore three to high savings and sheet one underscore
four to high return.
I won't make any changes to base model.
That stays the way I built it as a reference against which I will make comparisons.
For high start, I will put in a beginning amount in cell J2.
For this example, I will put in $5,000 as a starting point.
Then I go to the high savings sheet and for this one, I'm going to double the amount
I save each year.
So now in cell J3, instead of 1000, I'll put in 2000.
Finally for the high return sheet, I will double the rate of return.
So in cell J4, instead of 0.05 or 5%, I will put in 0.10 or 10%.
Now when I make these changes, the sheet immediately recalculates to incorporate that new information
and now I can compare the results.
In my base case, I started with nothing, added $1,000 a year, earned 5%.
At the end of 40 years, I have $120,800.
We already looked at that one, that's our base case.
Now let's say I started with $5,000 in there, but still added $1,000 a year, continued
to earn 5%.
I end up with $156,000.
So adding $5,000 at the start adds about $35,000 to the final result.
Now if I look at doubling the amount I save each year, going to $2,000 instead of $1,000,
but keeping everything else the same, it gets me $2,241,600.
In fact, what happens very simply is that by doubling the amount I save each year, I
get double the payoff at the end, so it's purely multiplied by two kind of thing.
Now let's look at doubling the rate of return from 5% to 10%, but keeping everything
else constant, my payoff climbs to $442,593.
This is almost four times the base model amount and nearly twice the payoff from saving
twice as much.
Now our what if analysis has revealed something interesting, which is that the best way to
get a good retirement nest egg is to focus on getting a high rate of return rather than
focusing on saving more.
And for my college students, that really was the point I was making.
Now you would not want to use this overly simplistic model to actually forecast how much
money you will have, but using the what if analysis you can get valuable insights, and
that's why this technique is so powerful.
Now I have placed a copy of this calc spreadsheet on the website, and I'm also going to put a
link to that in the show notes.
Please feel free to download and inspect this file.
You might learn something interesting or just keep it around for your own use if you
want to do your own what if analysis.
You can go in and plug in whatever set of numbers is plausible and see how this works
out.
So, this is Ahuka signing off for Hacker Public Radio, and as always reminding everyone
please support free software.
Goodbye.
You have been listening to Hacker Public Radio at Hacker Public Radio, those are it.
We are a community podcast network that releases shows every weekday Monday through Friday.
Today's show, like all our shows, was contributed by a HBR listener like yourself.
If you ever consider recording a podcast, then visit our website to find out how easy
it really is.
Hacker Public Radio was founded by the digital dog pound and the infonomicum computer
cloud.
HBR is funded by the binary revolution at binref.com, all binref projects are proudly sponsored
by linear pages.
For shared hosting to custom private clouds, go to lunarpages.com for all your hosting
needs.
Unless otherwise stasis, today's show is released under a creative commons, attribution, share
a line, free those own license.