Hi! Welcome back to this mini investment series where I build a financial spreadsheet that hopefully will enable you

to understand your the prospects of your investments or your overall finances better. If you haven’t done so already, please go back and start with the first video where I start with a completely blank spreadsheet and build up to a fairly elaborate model that outlines potential scenarios for your finances. In this video I’m going to be adding a small part to the spreadsheet that illustrate the potential cash needs you may have in

retirement. This is important obviously for lots of reasons but one of them is that, by better understanding our future cash needs we can understand quite well what money we need to have now or what money we need to put aside every year in order to to reach that target. And also frankly the risk that we don’t reach that target. I think there is a huge advantage to building these spreadsheets that you will hopefully understand very well how all the numbers fit together and be able to adapt them, both inputs and the model itself to your specific circumstances. My name is Lars Kroijer. I’m

a former hedge fund manager that has written a couple of books about finance and I’m now doing these videos as a hobby. I’m not a financial planner so do your own

work, take your own advice before you do anything I say in this or any other video. But let’s get back to the spreadsheet. So here we are back at the original spreadsheet and just like before I’m going start by adding a making a copy of this sheet and move move it to the end just so it is easy to follow these, and let us say adding a retirement. Really could be any kind of spending here but just for now let us call it retirement. And insert row. We’re going make this. I’m sure I’m not going to add enough rows. Let’s just copy them down. Let’s say we do this to age 90. So that should be no that’s not going to be enough if it re-calculate nope. Need to insert a couple more. And so here you go that is 90. And and now let’s do this instead. Instead of making a contribution let’s assume that we start spending the money here. So let’s we do that simply by putting in one let us say minus ten thousand for

now and then make that equal. Let’s say we keep that constant every year so we don’t grow it. Also we should make the minimum risk allocation. Let’s just for now assume we make that a hundred percent in retirement

which is probably pretty reasonable. And there you have it. So now we have a very, very simple, realistically added retirement spending. you can obviously change the numbers

this somewhat morbidly assumed that you no longer need money after the age of 90. You can obviously extend it. Let’s start by going up here and make this savings at A84 and there you go. So that is row eighty four not age eighty four. So this essentially is saying that if you have this kind of spending now that was a very happy year. So if you have this kind of spending in this scenario you would have

five hundred and thirty one thousand. Let’s go ahead and hide these cells. But what I want to do is also show you now what is important is how many cases if you ran this one thousand times and again maybe hide a few of these cells. Sorry to be such a stickler for formatting but so here you,

I’ve hidden a bunch of the cells. So if you see this and let us say the scenario where you go a bit low zero, how many times would you not have enough money. And in this case it’s one point three percent of the time. So thirteen, thirteen cases out of a thousand you would not you would not have enough money past your ninetieth birthday. Now so is that a lot or a little it really depends on your attitude towards risk. In reality most people should change these allocations before It’s not like you should have a fifty-fifty allocation one year and then change it all to the minimal risk assets in retirement. In reality you should increase your low risk allocation as you grow closer and closer to retirement. You might even have a higher equity allocation at a very young age where you

still have room for things to go wrong. in a later video and I want to mention

this because is really important and in In a later video I’m also going to

add the volatility to these contributions. You see here, we sort of blindly assuming that you can make these contribution with no risk to it but in reality life

gets in the way in some years you might be able to contribute more than the

other years you may not be able to contribute any any at all or you may

even withdraw money now a further thing that’s very important to think about is

that it’s also possible that your annual contribution might depend upon the world

around you and the world around you might be well Illustrated with your with

with the equity return so if you take this scenario for example as is saying

this you have equities are assumed to be down 51 percent well maybe it’s not a

terrible assumption to say that in the following year you would not have been

able and the such a terrible state of the world and in the following year you

would not be able to make your regular contributions so how do we incorporate

both the randomness part of it and the fact that your your spending would be be

influenced by by the broader economy similarly we should probably incorporate

that and this is in this case it assumes you can absorb 10,000 a year

and there might also be volatility in terms of what you actually need and so

that’s important and and and finally yeah I think it’s also important to know

that you could also get to a point where if things have gone really really well

for you and you’re 90 years old you may not actually be you know let’s say that

you are you’re one of these people would weigh in excess of a million in this

case you may not actually need to have it all in minimum risk assets because

you you would probably not just be saving for yourself anymore you’d be

saving for your descendants and and and and that can start to influence things

so but I wanted to show how simple it is to make this change we can by the way if

you wanted to change the allocations in these modes you’re simply going to

change change these numbers so we can start by unhiding the stead of the row

as let’s go and format row untied and let’s say we say well every year from

here we want to make it this but we want to add one percent in minimum risk asset

I don’t forget following let’s see do that up to the final yeah yes and now we

recalculate and you see how this would be one way of doing it a very simplistic

way maybe not actually a terrible way but a very simplistic way but again yeah

let’s do this through that in a later video please do comment if there are

specific scenarios you want me to show how I can incorporate this in the video

that’s really helpful to me actually because I’m not you know I’m I’m very

aware very conscious of the fact that it’s hard to generalize these models but

they really can be used for a lot of good stuff and in quite in quite an easy

way but in yeah so here you shown it so I want to emphasize that it’s important

that you look at this way you rerun the model a thousand

and you get a sense for what happens if it’s if you fall short how likely are

you to fall short given these assumptions how sensitive are you to the

assumptions let’s Rick let me just show you as an example let’s say what I can

contribute four thousand a year let’s just say that’s fixed but I can’t grow

it that’s it and these are real numbers what you see is all of a sudden if you

can’t grow it your shortfall goes up too you know in this case just over twenty

five percent so that’s essentially saying but if you can’t grow your

contribution by the three percent we assumed earlier you’re going to fall

short in retirement three percent of the case similarly what if you say well I

need 15,000 not ten thousand a year yeah again you’re going to start falling

short more and let’s say well I need fifteen thousand but I also can’t grow

you know the the contributions is fall short in more than fifty percent of the

cases so it’s really really important to to understand how all these numbers work

and perhaps start to use them as a way to think about what would happen in your

life if you fall short yeah are you able to recover do you have other sources to

see you to retirement in the next video I’m going to walk through a way where we

can show how we build a data table so we can in one easy overview get a sense for

them how do these numbers the median return the median outcome the max and

the minimum and cases where you fall short

how do those vary depending on how much you put into equities and the minimum

risk asset to start with which is obviously a major major assumption and

and and one way you’ll see the outcomes will vary quite dramatically based on

that assumption so thanks for watching I hope this has been interesting a useful

and please leave some comments if there’s anything else you want me to add

to these videos and you can subscribe to my channel if you want to hear about

future videos and or share this on social media but in any case I hope to

see you in the next video

Nice video! One item to potentially add would be the 4% safe draw down at retirement that many people talk about over on the other websites. Is the 4% really safe, or would we run out of cash more often than not?

Hi Lars. You mentioned in a previous video about the lack of correlation in the model, in regards to the allocation amounts (min risk/equities in £) between years and how this wasn't too much of a concern: I was wondering whether you can elaborate on this? Is it because it would introduce far more complexity to the formulae than necessary?

How can we incorporate the role of dividends, assuming we stay invested inequities, after a certain age?

Just wondering if there is a way of incorporating a geared asset such as a buy to let property. You could assume, say 5% growth in the value of the asset value … how does one calculate this growth on the value of the asset net of the mortgage?