This past weekend I was browsing through my spreadsheet archives and came across an old spreadsheet that I created back in 2006.
While the name of the spreadsheet — SWR.xls — gave me a hint at what I would find within, I wasn’t quite sure what to expect since I had not touched this spreadsheet in roughly 13 years.
For those that may not be familiar, SWR stands for Safe Withdrawal Rate and that represents the percentage of your nest egg that you can withdraw annually and have a reasonable expectation of not exhausting your savings. You will most commonly see 4.0% stated as the standard SWR, although anything between 3.0% to 5.0% is not unheard of depending on your risk tolerance.
Dusting Off the Cobwebs
As I opened the spreadsheet, I was excited to see what I had created many years ago and whether any of it would still be relevant today.
While the spreadsheet is very rudimentary and not quite as appealing as more current spreadsheets, it included a very basic calculation of our net worth and the corresponding amount of money using the standard safe withdrawal rate of 4.0%.
As you can see from my tweet, I was definitely pleased to see that we were well ahead of the projections that I had made back in 2006 as we have just narrowly missed doubling that 13-year-old projection.
How had we accomplished that?
There are two contributing factors to how we have nearly doubled our net worth from what I had projected years ago.
- Rate of Return :: The projections were based on a 7.0% rate of return. While this projection was made before the Great Recession, it has also benefited from the great bull market that we have experienced since then and thus our returns have been greater than projected.
- Annual Contribution :: At the time, I had set a default value for our annual contribution to savings and we have exceeded that number as our income and saving percentage have grown over the years.
Let’s Look at the Data
To give you a better idea of what this spreadsheet looks like and the data that I had projected, take a look at the sample below along with a brief explanation.
Note: To maintain a level of privacy, I have altered the numbers with sample data.
The data highlighted in yellow are the editable values, so in this example I am starting with a balance of $50,000 and plan to contribute an additional $20,000 annually with an expected 7.0% rate of return.
The “Total” column would represent the net worth while the “SWR” column is calculated as 4.0% of the total.
While it was fun and interesting to look back at these old numbers, I wanted to then compare that to our actual numbers. So I added a few additional columns as shown below (again, sample numbers).
The “Actual Net Worth” column is self-explanatory and I currently have that tracked back as far as 2003, which roughly equates to a few years after graduating college and eliminating our non-mortgage consumer debt and my student loans.
The “Percent Ahead” column calculates our actual net worth in comparison to the projections that I made, and the “Percent Gain” is a basic calculation of the increase from year to year of our actual numbers. None of the calculations account for things like inflation or taxes, nor is it calculating the actual Internal Rate of Return.
As I said, this was all rather basic and rudimentary.
Take It For a Test Ride
While I am not sure that this would be of value to anyone, Holly had asked me if I had shared this anywhere.
As I replied, I figured that I would use this old spreadsheet as the basis for a post and then make the template available in case anyone wanted to take it for a test ride. The template has sample data included and all you need to do is change the data in the yellow fields to represent your situation. You’ll also need to change the year, age, and actual net worth data as applicable.
You are welcome to download the template for your own amusement.
If nothing else, it was fun to look back at some of my old spreadsheets and what projections I was making at the time. As a young 30-year-old in 2006, I was just starting to think about FIRE and seriously planning for our financial future.
Prior to that point, we were focused on eliminating our consumer debt and following the old standard of save 10.0% of your income. While that still allowed us to have a modest amount saved by 2006, we certainly had a lot yet to learn and could have done much better.
As an example, per my net worth spreadsheet, we had just shy of $52,000 in consumer and student loan debt and only $27,000 in savings in June of 2003.
However, over the years we have continued to work diligently on maximizing our tax-advantaged accounts and also contributing to taxable investments. We are now branching out into some real estate investments and are well on-track for reaching our goals for an early retirement.
Have you made any net worth or SWR projections over the years?
11 thoughts on “Net Worth and Safe Withdrawal Rate Projection Spreadsheet”
I’m missing something in the math, on the spreadsheet you are 50.36% ahead of where you estimated with the older spreadsheet, how did you calculate the 97% ? Pretty cool that you have been doing this for this long and can compare your conservative estimates to a more generous reality!
The numbers that I shared in the spreadsheet pictures, as well as the sample template, have been altered from my real numbers. So the real data equates to being 97% ahead like I tweeted, but when I put in the sample numbers I didn’t make the data come out to the same overall increase. Apologies if that was confusing.
It is fun to have records back that far, and periodically look back to see where my head was at when I was younger.
A nice surprise to do so much better than your original projections. That’s two nice surprises this week, if you count those BAC shares you almost lost track of.
Being the spreadsheet guy you are, finding it must have been like finding that extra present under the Christmas tree when you were a kid.
Thanks for sharing.
Most definitely, and I really enjoyed that BAC surprise as it added to the PADI! You hit the nail on the head re: the extra Christmas present, I had not looked in my Archive folder for quite awhile. It was like Ralphie from The Christmas Story but fortunately I didn’t shoot my eye out when I opened the spreadsheet!
Wow well done DivvyDad – most forecast I come across in my line of work are never achieved by a long-shot, so great to hear you have exceeded yours by a country mile!! There’s no doubt that the level of contributions is the biggest driver of portfolio returns, well and truly above portfolio performance. Just keep adding to that fund!
Thanks Frankie, over the years I seem to have become much more aggressive in my forecasts and goals. Looking back 10-15 years, I don’t think I had the confidence yet to really push myself in some areas. Definitely will not stop adding to the funds!
My old financial spreadsheets bring back memories. It’s also interesting to compare tracking improvement. I’m much better at forecasting now. I’m 27 and trying to save at least 40% of my after-tax income every year. A lot of delayed gratification at work… but the lower financial stress is already worth it. And the coming financial freedom is the cherry on top.
That’s great FF, and fantastic to see that you’re saving a very healthy % of your income at your age. Sounds like you’re well on your way to financial independence and a bright future!
I love articles like these. If you keep it up you could eventually have a situation where your Dividend/Capital Gains Distribution exceed 4% of your net worth, that’s where things start getting surreal.
Appreciate the feedback DH!
I am definitely working as hard as possible to build up the portfolio where my distributions cover as much of our expenses as possible, if not exceeding them entirely so that net worth can continue to grow even while we live comfortably in retirement. Across all account types I am currently a little over $17,000 in projected dividend income plus whatever capital gains distributions come from my index funds.
It is crazy to think that this is quickly closing in on surpassing my wife’s income as a preschool teacher (who tend to be more grossly underpaid than teacher’s of K-12 aged kids who are also often underpaid). Fortunately she works because she loves the kids and not because we need her income to survive.
Sounds like you’re well on your way. Sure beats dipping into principal!