Scenario Spreadsheet Tutorial

G'.

Day.

Welcome to a, tutorial.

Tutorial, sorry.

On how to use the scenario spreadsheet you found it's quite a involved spreadsheet.

It requires a lot of inputs on your behalf, but the results are quite, quite special.

I think you really enjoy this one.

It's very very, very flexible.

Sorry, drop the mic.

And it allows you to see lots of different results for all kinds of financial scenarios that you're considering, maybe considering retiring early, going part time, taking the year off, buying a rental property and all such things.

So the sheet does rely on pretty, pretty accurate assumptions.

obviously we, we can't predict ahead of time what's going to happen.

But yeah, try and, try and be as accurate as possible as to what you think might happen and the results will thank you for it.

First of all, the purple cells in the sheet are cells that you can make changes on.

In fact you probably should use as much of the purple cells as you can to get the best possible results.

And any cell that isn't purple is basically automatic.

it calculates results based on what you're inputting and you don't need to do anything with those cells.

Also with that said, we'll go through the worksheet now and how to, how to make the most of it and how to enter your information.

another thing on the sheet as well is a lot of the sheets on my website are either pre retirement or post retirement, whereas this one can be used for the whole lot prior to retirement, after retirement.

it's basically your cash flow year to year and whether or not you might have enough at age 100 and how much you have each year along the way as well.

So yeah, it's all encompassing.

Right.

So starting up here, you can sell a one enter in any scenario you like.

You don't have to enter anything if you don't want.

but yeah, it might be handy especially if you want to run a few different scenarios.

You can save and copy the sheet and do multiple scenarios to compare.

So in this example we've got someone who wants to reduce their income by half, and retire in eight years time and also downsize house.

So you start off here entering your age at the end of the year.

Pretty simple.

And if you do have a partner, enter their age here too.

If you don't have a partner you can just leave that blank.

That's fine.

For the investment returns, it's very important that you enter the returns after all costs and inflation as well.

So if you're expecting 8% investment returns and let's just say 2% inflation, then the investment returns drop to 6%, then maybe another half a percent taken off for management costs, that takes you down to 5.5% and let's say another 1.5% for tax, then you're down to around 4%.

So very important you include all those deductions because the rest of the sheet uses income and expenses in today's dollars.

The reason for that is to make it as simple as possible for you to input your assumptions.

Most spreadsheets require you to calculate what your income might be in, say 10 years time or what your spend might be in 10 years time.

Whereas I've tried to keep it simple here.

And you can use today's dollars so you don't have to, calculate what you think your spend might be in 10 years time.

For example, if you think you be spending 100,000 a year in 10 years time in today's dollars, then you just enter 100,000.

here there's an option as well to stop the current return assumptions.

For example, coming up to retirement, you might want to reduce your return expectations to allow for de risking, in retirement.

Maybe you don't want to invest as aggressively because you, you know, you need some of those investments in the short to medium term.

So yeah, there's an option here.

You can enter any age you like, Chuck 65, 70, whatever age you want to change your return assumptions.

And you enter your new return assumption in this cell here, so 3%.

And if you want to do that again and again, you can up to four times.

So here if you want to make another change, 2% from age 75, that's all good.

And then another assumption, 1 1/2% from say age 85, you can do that as well.

So as many changes as you want up to 4.

Then in cell B12, you can enter your current kiwisaver balance.

And by kiwisaver, that's just terminology I use here.

But it could be any retirement fund you've got.

Maybe you've got an overseas pension, maybe you've got a New Zealand, retirement fund that your company provides, such as ACC or a, teacher's fund, anything like that.

So don't get hung up on the terminology kiwisaver there, that just basically means retirement fund balance.

Anything you don't have access to until you're older, anything that's locked away really, that's the point of separating that.

And then you've got your non Kiwi saver balance here, any money you've got outside of your time at funds.

So here we just chuck a million dollars.

So that's quite a few of the inputs there.

here we've got gray cells which, are locked.

You don't need to do anything that takes the youngest age if you're a couple.

And if you're single, it obviously takes your age.

So let's say you've got someone in the couple that's 50 and someone that's 52.

It's going to take the youngest person first, age 50, and it's going to go all the way across.

and it's also got the year below as well.

So you don't need to worry about the years all the way to age 100.

The spreadsheet goes up to.

Doesn't go further than that, unfortunately.

If you want to go further than that, reach out.

Maybe we can send you in just a copy.

But, yeah, that's fairly conservative planning.

it goes further across blank cells here.

But that only is to reflect if you've got, a younger person or a younger couple.

So let's say you're 30 and 28.

Now, starting at 28, the youngest, and goes all the way up to age 100.

And there's still a few extra spaces here, to reflect the fact that you can start from age 20 in this spreadsheet.

So we'll go back to the original assumptions, 52 and 50.

So in this cell B16 and all the rows across row 16 or the column, sorry, you, can enter any notes that you like, for any major milestones or events that you want attention brought to.

Here we've got retirement and downsizing house.

And you can see down here we've entered $200,000 from Downsizing House.

So yeah, that just reminds us why that 200,000 is there.

Maybe you've got kids going to university or maybe you're helping out with the house deposit.

Anything that you want a note for, just add it in the timeline notes so you're not confused.

Now, in all these cells here, you enter all your income from rows 19 to 26.

You don't have to use the headings I've used here, my salary, spouse, salary, chuck in anything you like, any income.

And then across here you enter your income for the years ahead for as long as you like.

and again, use today's dollars.

Don't inflate your income for inflation.

If you want to inflate your income that's fine.

Maybe you get promotions or perhaps a new job.

maybe there's a decrease in income.

Whatever the case may be, change your income, but don't do so for inflation purposes.

So these are all the income events I've put in here, starting with 70,000, 80,000 for the couple, work income and then halving in 27, basically as per this scenario.

And then rental income consistent of 10,000 across, again not increasing for inflation.

And kiwisaver contributions entered here for kiwisaver contributions or retirement fund contributions, make sure you include not just your own contributions, but also the employer and the government contributions as well.

It's important.

then further along we're adding super income here.

age 63 is probably not right for super, especially in New Zealand.

So we'll change our age to 52.

And you can see super now starting at age 65, it's not the full 15,000 in this example.

I've just assumed that the person turns 65 part way through the year, so only gets partial super.

But if you have a birthday early in the year, you might get the whole year.

If your birthday is at the end of the year, you might only get a few dollars.

So yeah, just consider that when adding super.

When's your birthday?

if you're younger than 50, maybe you don't even want to include super.

It's up to you and what you think about, future.

Future Super.

so yeah, income is totaled here in row 27 in the green.

that's all automatic.

You don't need to do anything there.

It's a green cell.

it just doesn't include kiwisaver contributions.

It includes rental income and work income, but not retirement fund contributions.

they're included later on you'll see where but later.

and then just below here in rows 28 and 29, you've got your projected spend.

So again, don't adjust it for inflation.

You might want to adjust it for your needs.

So here at age 60 in retirement, I've increased it so that we can enjoy a bit more travel with our, newfound time.

maybe some activities and entertainments, that we didn't have time for before and then decreasing actually later on in our 60s once we've got over the travel bugs and we're not doing so many activities, perhaps just wanting to slow down a bit, enjoy time around the house.

our spend might be a bit less so your situation will be different.

Just enter whatever numbers you feel like here in your spins, you can change it every year, you can keep it the same, whatever you like.

And if you've got a mortgage that's separate, so chuck that here.

the reason it's good to keep the mortgage separate is because monthly repayments tend to stay pretty consistent over time, whereas non mortgage expenses can change based on your situation.

And again that total was here in the orange.

total spend added age and year carried down.

So you can see it as you scroll down the sheet.

and then you get into one off income.

That's again all these purple cells.

You can make your own changes too don't, you don't need to use these headings.

these are just examples.

So here we've got equity from the sale of the house at age 60, year 20, 34, $200,000.

here at age 58 we're expecting an inheritance, so we chuck that here.

But any other one off income events chuck in any time you like.

And in rows 38 to 42 you've got your one off expenditures.

So any one off spend, your project, maybe helping the kids with a house deposit, education, buying a house, upsizing house, buying vehicle, going on a trip, anything that's outside of your regular expenses included above and sell in row 29 here.

And you don't need to enter minus signs here by the way as well.

You just enter in 30,000 or whatever the case may be and it automatically converts to a negative.

So it's pretty handy.

And the subtotal of your one off spend and expenditure is included in row 43.

So 35,000 in this example with the 30,000 trip and 5,000 education, if we wanted to end off into a one off spend event, let's say 50,000 inheritance, then you can see the subtotal is 15,000 out of one off spend and one off income.

50,000 minus 35,000 spend.

Yeah, we'll get rid of that again.

So we've got minus 35,000 and the trues you see here, they provide a quick snapshot of whether or not you'll have enough money, from your accounts.

So cells44 if you are going to run out of money in your non kiwisaver investments that cell A44 will turn red.

And one of the cells in cell 44 in row 44 sorry will also say false and turn red.

likewise in cell A45 if you run out of, let's just say kiwi saver money, we'll change that later.

Yeah, that should say kiwisaver so if you run out of kiwi saving money, that will turn red as well.

the neat thing about this sheet is that it always assumes you're going to use your non kiwisaver money first, just to account for the fact that you might want to use it before the age of 65.

And then once you've run out of non kiwi saver money, then the calculator starts using your kiwisaver money.

So to show an example, we'll reduce our non kiwisaver balance, so from a million to 600,000.

Now you can see the reds have appeared down in cells A44 and A45.

So if you want to find out where you've run out of money, you can scroll across and see you run out of non kiwi saver money at age 82.

You can see $0 here in, in row 50.

You've run out of money.

So at that point it's going to kick in.

Start using your kiwisaver money.

Hopefully you're older than age 65.

If you are, younger than 65, the sheep will tell you you're too young to use kiwisaver.

It'll still take out the money, just because it assumes maybe you've taken out a hardship withdrawal or something like that, but it also notes that you are too young.

So take note of that.

But yeah, here we are old enough.

We've got 379,000 in Kiwi Saver and it's going to start using that because we've run out of non kiwisave money.

So you start scrolling across, the balance is reducing now at 104,000 a day JD7.46 at 8.88.

And now we've run out of money around age 99.

And you can see it's all false up here because we've run out of money money for KiwiSaver 2.

So that's why that's red.

and you can see up at the top a summary of events.

Your money run out in age 88, for example.

And you can see here, age 88, you've got $0.

So if we increase that again maybe to 850,000 instead of the original million.

Oops, not 8.5 million 850,000.

Then you can see there's only one red event, the non kiwisaver.

So that means our kiwisaver savings will be enough to take us to 100 now.

so now your non kiwisaver savings runs out at age 93 and your KiwiSaver savings reducing, but just enough to get to age 100 at $125,000.

So you see the summary.

Now you've got $125,000 at age 100.

So we scroll down further, you can still see the original assumptions, they come down with the screen.

I've frozen those screens, that screen.

So you can still see that.

And you can see the age and year, the whole way through as well.

So you don't have to keep scrolling up and down.

Now we're into the area of the sheet that is all automatic.

you don't make any changes to it.

You can just see year by year how you're doing.

so starts off here with your beginning of year investment balance in both kiwisaver and non kiwisaver.

And you can see how that's increasing over time.

Hopefully.

Rows 55 and 56 are, your savings for the year.

So here we've got 70,000 non KiwiSaver savings.

And that basically means in this particular year our income is 70,000 more than our spend.

So you can see income of 160,000, spend of 90,000.

So that's what leaves 70,000 savings for the year and KiwiSaver savings of 10,000.

Also taken from how much you entered here, 10,000 KiwiSaver savings.

In row 60 and 61 you've got your one off income and spend which you entered in sales 34 in rows 34 to 42, nothing for this particular year.

Whereas year 2027, you've got negative 35,000 which you can see in the one off income in cell C60 here, row 65 and 66 are simply your investment returns.

So, your current balance multiplied by the investment returns you entered at the beginning up here.

And then you've got in row 70 and 71, your end of year investment balance.

So your beginning of year balance plus your, annual savings, plus your one off income or minus your one off expenses, plus your investment returns, $1.068 million in this example.

And that gets carried on, carried over to the beginning of year balance the next year.

Again, you can see the red below.

testing if you run out of money for kiwisaver and non kiwisaver.

So there it is, that's the sheet basically, scrolling down here, you can see the results instead of annual results, you can see a summary of every five years here, how much you have in non kiwisaver, how much you have in kiwisaver how much you have in total and how that changes over the five year periods.

And those results are shown in this line graph here with three lines for your kiwisaver balance, non kiwisaver balance, and the total which is your kiwisaver balance plus your non kiwisaver balance.

So that's the sheet.

feel free to play around, check out the different results.

It does rely on, you know, pretty simple return assumptions like you know, you're not, if you select 4% per year return assumptions, you're not going to get 4% returns every year.

which hopefully you realize that these are all assumptions.

It's not going to turn out exactly like your plan but it's still a really valuable exercise to see how certain changes you make can make on the assumptions make on the outcomes.

For example, if you change your spend, let's say if you increase spend by just 10,000 a year, here you run out of money a few years sooner, just from increasing your spend by 10,000 a year for 10 years.

yeah.

So play around, with spend, play around with income return assumptions, anything you like, save the sheet, make another copy, run different scenarios for maybe buying rental property versus not buying rental property.

anything you like.

If you have any other questions or anything, maybe some feedback on how to improve the sheet, just shout out and let me know.

Otherwise yeah, have fun and yeah, do share it amongst people that you know that might be interested in this kind of stuff.

not, not many people visit, visit my website and I'd like that to, you know, become more common knowledge.

because these are all resources that are free and can help a lot of people I believe.

Right.

Enjoy and shout out if you need anything.

Thanks.