On your path to financial independence (FI) do you wanna think like an actuary? Wanna learn some of the secret actuarial skills they only teach you at actuary school? Yeah I know, what do actuaries think? Hard stuff I guess. But I’m getting off the point.
++UPDATE 2019++ This post is still relevant so I’m not going to delete it. But really it has been superseded by my new course – Create a kickass financial independence spreadsheet – for beginners. I strongly suggest you check that out. There is a 30 day money-back guarantee so it’s risk free!
If you are powering your way to FI then you need to get yourself some spreadsheet smarts. There are plenty of great off-the-shelf tools for the aspiring FI’er such as Mad Fientist Lab, Personal Capital or Mint but there is no substitute for rolling up your sleeves and getting some real spreadsheet dirt under your fingernails. You might want to get a second opinion with a quick calculation, or maybe you have a unique and specific situation that needs some kickass modeling.
This is the first in an indeterminate series of posts to give you the tools to be spreadsheet kickass and make you popular at cocktail parties (if there are many actuaries present).
Example
Let’s take an example where I want to achieve FI at age 50 in 2022 and let’s assume that I have identified that my target pot is $1m. In this example I’ve assumed that if I can obtain a total worth of $1,000,000 at age 50 then I can retire. Your numbers are likely to be different to this example so go ahead and plug them in.
In the spreadsheet below, I have shown two rows; my age starting at 45 and the year starting at 2017. I have put $1,000,000 in 2022 at age 50. Note that I have put zeros in columns D, E, F, G, this is important and I’ll explain more about that soon.
Now we are going to use a spreadsheet function “npv”. This stands for Net Present Value and it calculates the current money required today to meet a future obligation. You can think of it as the opposite of compound interest. Compound interest takes dollars today and rolls them up in the future with investment returns. NPV takes a future amount and asks – what amount today will roll up into that future amount? Actuaries love NPV and we’re going to see how useful it is.
The final ingredient is an interest rate. In the formula below I’ve used 4%. So the formula has taken our future cashflows [0, 0, 0, 0, 1,000,000] and calculated the NPV with 4%.
The answer is $821,927. So what does that mean? It means that if I want to accrue $1m by 2022 then I need to be sitting on an egg that’s worth $821k today. Any less and I will fall short of my future goal. Currently this does not include additional savings in the interim, but stay calm we’ll tackle that soon. 
Let’s now introduce some additional obligations. Let’s assume I have two kids and I want to pay their college fees of four years each. Let’s also assume these are $30k a year, and one kid starts in 2019 and the other starts in 2021, so there are two overlapping years where I have to find an additional $60k (ouch!). I’ve added another row below with Kids’ college, and made a third row to sum. I’ve then NPV’d the sum.
The total required today to meet these future obligations has risen to $1,023,446. If I still want my FI pot of $1m in 2022 and I want to meet these college costs I need to have at least $1,023k today. That’s a big increase on the previous amount and reflects the high college fees.
Now let’s assume that during these intervening years I’m not sitting on my hands but making additional savings. I’ve added another row below to show additional savings of $50k as year from 2018 to 2021. Note that these numbers are negative since additional assets subtract from my future obligations. Again I have totalled each year’s cashflow.
With these additional savings of $50k per year the amount required today has fallen to $841,950. So I can easily tell whether I am on track with this future plan by checking the size of my savings. If it’s lower that $841k then I will have problems in the future.
Being able to project forward with compound interest and bringing future values back to the current date with NPV is a very powerful tool. In future blogs we’ll explore this some more.
Your Turn
What did you think, was this useful? Would you like to see other posts on kicakssing your spreadsheets? Are you looking for beginner or pro tips?
Technical note: Did you spot that the benefit of an additional $200k in savings reduced the NPV from $1,023 to $841k, which is only $181k? This is due to the fact that a dollar today is worth more than a dollar in the future because of the power of compound interest. Would you rather have a dollar today that has plenty of time to accumulate in value between now and retirement, or would you prefer to receive a dollar the day before you retire? Clearly the former. Feel free to change the interest rate from 4% to 0% and the benefit will go from $181k to $200k.
Warren Buffett knew this instinctively when he said that he would give away his fortune to be a young man again. In investing the power of time trumps everything.
Very useful! Didn’t know about this function (yet), and can see why it’s so interesting for us FIRE people. I think both beginner and pro tips are very welcome, depending on what you want to achieve with it. Would be awesome if there was a page where you could find all tips collected on this blog or something. Going to follow this 🙂
Thanks for dropping by Div and the comments. The present value functions are a family and they have their different uses. So I’ll be posting more on this subject soon…
Any and all tips would be great. More along the pro tips, although I think there’s always the possibility to learn even from so called beginner info.
Thanks for dropping by, I’ll get busy on some follow-up and give some thought to potential pro tips.
I love me a good spreadsheet.
I like that you use NPV. I’ve always thought of it differently, but this looks like a pretty simple way to answer “can I retire yet?” I’m in the middle of rebuilding my spreadsheet, so I might incorporate this as a validation section.
I love me a good spreadsheet.
I like that you used NPV. I usually think of it differently, but this is a good way of quickly answering “how much do I need?” I’m in the middle of rebuilding my spreadsheet, and I think you convinced me of adding this as a validation section.
Let me know how you get on with your spreadsheet.
Spreadsheet the love man!
Pingback: Month-One Blog Report (that I didn’t want to produce dammit!) - actuary on FIRE
Pingback: Mathtastic Advice on Sequence of Returns Risk - actuary on FIRE