For those of us that strive to control our finances and get ahead in life, being able predict what a potential portfolio will be worth in the future is absolutely essential. If I save $100/month for the next 10 years, what will the value of my portfolio be? More importantly, what will my purchasing power be? What if I increase that $100 per month by 10% per year? What if I only make quarterly deposits to my account? These are all questions that effect real people every day.
The problem is that projecting an investment portfolio into the future requires many assumptions. The most important of which is the rate of return you expect to earn on your investments. The second most important is what the inflation rate will be during that same time frame. While most people track stock market returns like a hawk, they don’t look at the other side of the coin. If the stock market returned 10% in one year, that’s great right? But what if inflation was 12%? Congratulations, your purchasing power decreased by 2%. Dammit!!!
In searching for high quality investment calculators online, I found that the majority are just too simple. They either don’t allow you to input a recurring payment (such as monthly or quarterly), or they don’t take into account inflation, or they don’t allow you to differentiate what your returns would be between stocks and bonds, etc. Most of these online calculators just don’t have enough customization options to truly get a picture of what an individual’s future portfolio could look like. Therefore… they’re useless! Plain and simple! These calculators are about as good as a large pile of mierda.
So I wrote my own portfolio and retirement spreadsheet, and I made it customizable. Take a look at the input screen below. You’ll notice that it allows you to specify a payment frequency, as well as a growth rate. It will take into account your allocation between stocks and bonds, and the corresponding returns. It allows you to input an inflation rate. And best of all, it allows you to build up a projected portfolio value, and then analyze it using three different retirement approaches.
The problem is that projecting an investment portfolio into the future requires many assumptions. The most important of which is the rate of return you expect to earn on your investments. The second most important is what the inflation rate will be during that same time frame. While most people track stock market returns like a hawk, they don’t look at the other side of the coin. If the stock market returned 10% in one year, that’s great right? But what if inflation was 12%? Congratulations, your purchasing power decreased by 2%. Dammit!!!
In searching for high quality investment calculators online, I found that the majority are just too simple. They either don’t allow you to input a recurring payment (such as monthly or quarterly), or they don’t take into account inflation, or they don’t allow you to differentiate what your returns would be between stocks and bonds, etc. Most of these online calculators just don’t have enough customization options to truly get a picture of what an individual’s future portfolio could look like. Therefore… they’re useless! Plain and simple! These calculators are about as good as a large pile of mierda.
So I wrote my own portfolio and retirement spreadsheet, and I made it customizable. Take a look at the input screen below. You’ll notice that it allows you to specify a payment frequency, as well as a growth rate. It will take into account your allocation between stocks and bonds, and the corresponding returns. It allows you to input an inflation rate. And best of all, it allows you to build up a projected portfolio value, and then analyze it using three different retirement approaches.
I’ll use an example portfolio to show how this tool works.
Based on the generic inputs above, this theoretical person is 30 years old and they’re hoping to retire by the time they’re 45. It would be nice to retire 20 years earlier than most people right? They currently have $50k saved up, they’re contributing $1,000 per month towards retirement and plan to grow that deposit by 5% annually. In pre-retirement they have 75% of their portfolio in stocks, and they expect to earn 12% annually.
Before the flaming begins, maybe 12% is a realistic expected rate of return and maybe it’s not. In my case, I can earn 12% on my money pretty easily. Between capital appreciation, dividends, and intelligent use of stock options, 12% isn’t very hard to obtain.
On the other hand, if you’re strictly a passive investor with no intention of conducting intrinsic analysis to find undervalued stocks or learning additional methods of using stock options (Covered Calls, Cash Secured Puts, etc), then maybe 6 or 7% is a more accurate number. Read my previous post, “The Stock Market Always Looks Better With a Birds Eye-View” to get an idea of historical market returns.
The resulting portfolio value at retirement would look something like:
Based on the generic inputs above, this theoretical person is 30 years old and they’re hoping to retire by the time they’re 45. It would be nice to retire 20 years earlier than most people right? They currently have $50k saved up, they’re contributing $1,000 per month towards retirement and plan to grow that deposit by 5% annually. In pre-retirement they have 75% of their portfolio in stocks, and they expect to earn 12% annually.
Before the flaming begins, maybe 12% is a realistic expected rate of return and maybe it’s not. In my case, I can earn 12% on my money pretty easily. Between capital appreciation, dividends, and intelligent use of stock options, 12% isn’t very hard to obtain.
On the other hand, if you’re strictly a passive investor with no intention of conducting intrinsic analysis to find undervalued stocks or learning additional methods of using stock options (Covered Calls, Cash Secured Puts, etc), then maybe 6 or 7% is a more accurate number. Read my previous post, “The Stock Market Always Looks Better With a Birds Eye-View” to get an idea of historical market returns.
The resulting portfolio value at retirement would look something like:
The total value of this portfolio would end up in the range of $750,000. Is this enough to get you through 40 years of retirement? Any financial advisor would probably say “hell no”. But then they almost all give blanket statements that you’ll need 2 million, 3 million, or 10 million to retire. Let’s face it, those number are about as arbitrary as it gets. Half of these so called financial advisors would give you a target retirement number, yet never ask what your annual living expenses are. Explain to me how they could know what you’ll need in retirement without knowing how much you spend annually.
In the end, it depends on how much you plan to live on. Planning to travel around the world in retirement will cost an order of magnitude more than living a low key lifestyle. It’s up to each one of us to determine how we want to spend our time in retirement.
The better answer to how much you’ll need in retirement is to take your annual cost of living expenses and multiple that number by something in the range of 20-30. If you use the special number of 25 you’re essentially withdrawing according to the 4% rule, a special rate very well known among financial bloggers. I’ll discuss it a little more in the next section but here’s a quick example. If my annual expenses were $40,000 per year and I expected that to continue into retirement, the easy math would look like this:
$40,000 * 25=$1,000,000
So let’s see how the retirement years could look with this theoretical portfolio.
In the end, it depends on how much you plan to live on. Planning to travel around the world in retirement will cost an order of magnitude more than living a low key lifestyle. It’s up to each one of us to determine how we want to spend our time in retirement.
The better answer to how much you’ll need in retirement is to take your annual cost of living expenses and multiple that number by something in the range of 20-30. If you use the special number of 25 you’re essentially withdrawing according to the 4% rule, a special rate very well known among financial bloggers. I’ll discuss it a little more in the next section but here’s a quick example. If my annual expenses were $40,000 per year and I expected that to continue into retirement, the easy math would look like this:
$40,000 * 25=$1,000,000
So let’s see how the retirement years could look with this theoretical portfolio.
Retirement Analysis - Withdraw a % of your portfolio each year
The first of the three retirement options assumes that you’ll withdraw a certain percentage of your portfolio each year, based on the beginning portfolio value, and you’ll increase it each year by the inflation rate. A rule of thumb financial independence bloggers like to use is the 4% rule. The idea is that if the long term stock market returns around 7%, and inflation eats up 3% of that, you’ll be left with roughly 4%. Therefore, if you withdraw that same amount, you’re essentially living off the interest that your portfolio generates. The Trinity study gives a good breakdown of the calculations involved and how the 4% rule was determined.
You’ll notice that this portfolio actually increases in value each year, even with the $30,000+ in withdrawals each year. This is due to the fact that the post-retirement returns are higher than the 4% withdrawal rate. Essentially, the interest the account is earning is more than the annual withdrawals.
You’ll notice that this portfolio actually increases in value each year, even with the $30,000+ in withdrawals each year. This is due to the fact that the post-retirement returns are higher than the 4% withdrawal rate. Essentially, the interest the account is earning is more than the annual withdrawals.
In this case, the total beginning monthly income is $2,510 (just over $30K annually). However, if you increased the withdrawal rate to 5% annually, your monthly income would increase to $3,138 ($37.6K annually) and you still end up with a final portfolio value higher than when you started retirement. Also remember that any pensions, social security, or supplemental income will be on top of this income.
Reducing the expected rate of returns in retirement completely changes the numbers and you’ll notice very quickly that a portfolio that would have lasted till death suddenly runs out of money at 80 years old or even earlier. Make sure to use realistic investment returns and update them as needed.
Reducing the expected rate of returns in retirement completely changes the numbers and you’ll notice very quickly that a portfolio that would have lasted till death suddenly runs out of money at 80 years old or even earlier. Make sure to use realistic investment returns and update them as needed.
Retirement Analysis – Annual Dollar Amount Withdrawal
This analysis assumes that you you’ll input a set dollar amount that you’d like to live on annually. Most of us financial fanatics know very accurately how much we spend in any given year, so this analysis method may be the best. To continue with our example, suppose we want to live on $42,000 per year. Assuming no debt, this would be very doable. The analysis is shown below.
You’ll notice the output screens look very similar between this and the previous analysis method. This is to ensure consistency and ease of understanding for the user. In this example, $42K per year equates to a starting monthly income of $3,500 which then increases each year with the inflation rate. Once again, based on the returns that the portfolio earns in retirement, it actually increases each year until the age of 69, at which point the annual inflation increases finally exceed the interest earned and the portfolio peaks in value. This portfolio doesn’t run out of money until the age of 93.
Most importantly, you wouldn’t have to increase your withdrawal rate by the inflation percentage each year. Even if you only increased it every other year that would be the difference between a portfolio that runs out of money and one that lasts until your long dead.
Most importantly, you wouldn’t have to increase your withdrawal rate by the inflation percentage each year. Even if you only increased it every other year that would be the difference between a portfolio that runs out of money and one that lasts until your long dead.
Retirement Analysis – % of Pre-Retirement Income
The final analysis method is what most financial planners like to use. They assume that whatever your final income was before retirement, you’ll need to live on a large percentage of that to ensure that you can maintain the same lifestyle in retirement that you had in your working years. The validity of this argument is debatable, but that’s for another blog post. I included the analysis because it is so common.
To finalize our example, say this working family topped out at $80K per year and they could live off of 55% of that in retirement. In this case, the portfolio would run out of money about 85 years old, which just happens to be the same as the life expectancy input. That’s definitely cutting it tight, however, by simply reducing the annual increases to 1% instead of the 2% inflation rate, the portfolio would outlive them. They could also increase it by the full 2% until social security kicked in, and then stop increasing it all together and they’d still never run out of money.
To finalize our example, say this working family topped out at $80K per year and they could live off of 55% of that in retirement. In this case, the portfolio would run out of money about 85 years old, which just happens to be the same as the life expectancy input. That’s definitely cutting it tight, however, by simply reducing the annual increases to 1% instead of the 2% inflation rate, the portfolio would outlive them. They could also increase it by the full 2% until social security kicked in, and then stop increasing it all together and they’d still never run out of money.
In the end I’m always reminded by the motto I live by as an engineer.
Garbage In = Garbage Out.
It doesn’t matter how good the model is, if your inputs are garbage, the outputs will be garbage as well. As I stated before, the most important inputs for this sheet are the expected rate of returns. I would advise that you err on the side of conservatism and underestimate what you could actually earn. Ending up with some extra money to pass on to your family when you die is much better than running out of money. Good luck and I hope you enjoy the spreadsheet.
You can find it in the Free Tools section or Download it directly.
Please feel free to message me if you have any questions or comments regarding the tool .
**The YBI Portfolio and Retirement Tool V2 is currently in work. The enhanced version of the tool will use historical stock market return data, along with your retirement inputs, to determine if your retirement method would have worked in the past. Sign-up to my email list and I’ll let you know when version 2 of the tool is released.
RSS Feed
