Build your own Financial Planning Spreadsheet (part 5) – adding retirement spending

Updated : Oct 23, 2019 in Articles

Build your own Financial Planning Spreadsheet (part 5) – adding retirement spending

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?

  • 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?

Leave a Reply

Your email address will not be published. Required fields are marked *