Net Worth and Safe Withdrawal Rate Projection Spreadsheet

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.

1. 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.
2. 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.

Summary

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?