This article will explore Time Value of Money concepts in the context of early retirement. The intent is to find a flexible way to manage one’s portfolio savings and annual spending levels in combination with various types of irregular cash flows while waiting for stable fixed income (e.g. social security, pension) to settle in. As we will see, a few spreadsheet formulas can go a long way.
A simple situation
Jim is a 68 years old retiree. He decided to postpone social security until he’s 70 and will get $30,000 (inflation-adjusted) by then. He has $1,000,000 in savings. His plan is simple, take 4% from his latest portfolio balance every year, and complement with his social security income. Now what to do about the two years without social security? Jim will simply take the extra income ($30,000) from his portfolio, in addition to the regular 4% withdrawal. This is a reasonable plan.
A more complicated situation
Harry and Sally are in their mid-50s and have fairly comfortable savings. Harry is ready for freedom, i.e. to become an early retiree and stop working. He might do some part-time consulting work for a few more years, but he’s not too hot about it. Sally is a dedicated teacher and she wants to keep working for a few more years, until she’s 60. By then, Sally will get a state pension, which is only partly adjusted for inflation every year. Harry will be eligible for Social Security at FRA (Full Retirement Age) and Sally will get spousal Social Security, although they are not too sure if they should postpone till 70 or not. Harry and Sally are now US citizens, but they originated from another country where they worked for a decade before moving to the US. They will therefore get a modicum of retirement income from their country of origin. Harry and Sally’s surviving parents are unfortunately ailing and told them to expect some level of inheritance in the coming decade. Finally, Harry and Sally plan to downsize their (empty nest) house when Sally stops working and move to a sunnier location. By then, their mortgage will be paid for.
As you can see, there is a complicated sequence of cash flows unfolding over time until they turn 70, with diverse sources of wages, fixed income and lump sums coming their way at various points in time, as well as some extra expenses (e.g. mortgage). By the time they turn 70, the fixed income situation (SSA, pension and foreign retirement) will be stable and they plan to use a variable withdrawal method like the Bogleheads VPW to complement it, based on their portfolio savings. The question is what to do in the 15 years until then. Logic dictates to do something like Jim, take some extra money from their portfolio, but how much extra withdrawal should they do every year isn’t obvious. Furthermore, is their plan sound, e.g. is Harry correct that he can stop working that early?
This article will go through time value of money concepts, from very simple math to more complex computations, in order to answer this thorny question. It turns out that a few spreadsheet formulas can provide a solid answer, irrespective of the complexity of the cash flow situation.
The following write-up will go step by step, using a mix of basic algebra and simple spreadsheet formulas. Readers can skim though the algebra if this isn’t their thing, or even skip straight to the end, but it’s probably worth making the effort to fully understand.
All computations and formulas can be found in the ‘PV Math Blog’ tab of the following Google sheet: http://bit.ly/2WuNmvf (readers should make their own copy and review it as they proceed through the article).
Computing forwards: basics
Spreadsheet reference: ‘PV Math Blog’ tab, ‘Basic PV/FV computations’ section.
Let’s say that we invest $X (Present Value) and let it grow for a period of time with a given rate of return: we get a Final Value.
Let’s assume that PV = Present Value; FV = Final Value; R = Rate of Return; N = Number of periods. The final value math is pretty simple: FV = PV * (1+R)N. Which can be expressed in a spreadsheet with the following formula: FV(R, N, 0, -PV).
Example: say you start with $10,000 and expect an annual rate of return of 5%. After one year, you get $10,000 + $10,000 * 5% = $10,500, which is equal to $10,000 * (1+5%). After two years, you get $10,500 + $10,500 * 5% = $11,025, which is equal to $10,000 * (1+5%) * (1+5%), in other words $10,000 * (1+5%)2. Note the extra $25 at the end of year 2, due to the magic of compounding interest (the 5% return at the end of year 2 also applies to the $500 of interests received at the end of year 1).
Let’s take an example using historical (nominal) returns of US stocks (~10%) and bonds (~5%) and see what happens over the course of 20 years. The value of compounding over time is really quite remarkable.
In reality, the past 20 years were a bit more hectic, to say the least… See below (using Vanguard VTSMX and VBMFX funds, expense ratios accounted for). We’ll keep using simplified models with fixed rates of return in this article, but please never forget that reality displays much more variability.
Now, what if we account for the inflation and perform the math in real (inflation-adjusted) terms? Let’s assume that I = Inflation Rate (e.g. 2% or so).
The final value math becomes: FV = PV * (1+R-I)N. Which can be expressed in a spreadsheet with the following formula: FV(R-I, N, 0, -PV).
Note 1: this formula is simplified. The inflation-adjusted rate would be better expressed in geometric terms as (1+R)/(1+I)-1. In practice, this doesn’t change the outcome much and (R-I) is a perfectly fine formula, notably when acknowledging the uncertainty on both terms (R and I).
Note 2: one could also apply an Expense Ratio (ER) in the same manner, i.e. using R-I-ER or, to be more precise, (1+R)/(1+I)/(1+ER)-1.
If we use inflation-adjusted returns and get more conservative by using worldwide historical returns (stocks ~5%, bonds ~2%) as future expectations, the trajectory is less exciting, but probably more realistic.
Computing backwards: basics
Spreadsheet reference: ‘PV Math Blog’ tab, ‘Basic PV/FV computations’ section.
A Present Value computation is exactly the reverse process of what we just explained. Take a given value in the future (FV), N time periods from the present, assume a given rate of return (R; also known as discount rate) and figure out what is the corresponding present value (PV). In other words, discount the effect of time on the future value to get to a present value.
If FV = PV * (1+R)N, then basic algebra tells us that PV = FV / (1+R)N. In a spreadsheet, this can be expressed with the present value formula: PV(R, N, 0, -FV). Conceptually, you can simply ask yourself what would be the outcome of investing the ‘Present Value’.
Example: would you prefer to receive $15,000 today or $18,000 (guaranteed) in four years? The answer is… it depends of the rate of return! Using a 4% rate: FV = $18,000; PV = $18,000 / (1+4%)4 = $15,386; $18,000 in 4 years sounds better than $15,000 now. Using a 6% rate: FV = $18,000; PV = $18,000 / (1+6%)4 = $14,258; $15,000 now is definitely better.
Computing backwards: regular portfolio withdrawals
Spreadsheet reference: ‘PV Math Blog’ tab, ‘John’s PMT computation’ section.
Another form of Time Value of Money computation is a typical retiree question, pondering how much could one withdraw from a regular portfolio while aiming at depleting in a given number of years. In other words, how can we spread out the savings over an extended retirement period, without buying an actual annuity?
To model such a retirement scenario, the final value (FV) is zero, the present value (PV) is the current portfolio balance. Let’s plan for an extended lifetime (N years), having rough expectations for a given (inflation-adjusted) rate of return R, and let’s ponder how to ‘annuitize’ the portfolio over such period of time. How much (PMT) can we withdraw for the current year for our annual spending, while repeating the process for N years total?
If we think about the very last year (year N), the answer is obvious, we can withdraw the full portfolio balance (PV) at the beginning of the year, and the portfolio will be depleted at the end of the year (FV = 0).
If we think about the previous year, the math isn’t too hard either. We have to solve the equation: (PV – PMT) * (1+R) – PMT = 0, meaning that we withdraw PMT at the beginning of the first year, whatever is left in portfolio is subject to the annual rate of return, and we take it all (withdrawing PMT again) at the beginning of the second year, leaving the portfolio entirely depleted. This small equation can be rewritten as PMT = (PV – PMT) * (1+R), and with a little bit of algebra, we can solve for PMT = PV * (1+R) / (2+R).
We will let the interested reader try to generalize this equation to previous years, but it gets a little complicated and it is much more convenient to use the handy PMT() function that we can find in spreadsheets. Every year, we can simply compute PMT(R, N, -PV, FV, 1) with FV = 0 to aim at depleting the portfolio in N years, while withdrawing the amount (PMT) at the beginning of each year (as indicated by the last parameter ‘1’).
Interestingly enough, this approach is remarkably adaptive, meaning that it works pretty well in real life where the actual annual return is never what one might expect and “stuff happens”! Just use a remotely sound value for R (say 5% for stocks and 2% for bonds, i.e. the worldwide historical average for inflation-adjusted returns) and re-assess at the beginning of each year based on whatever value your portfolio has actually reached. What you have to be aware of though is that the actual withdrawal at the beginning of each year WILL VARY (sometimes quite significantly) following the vagaries of the stock (and bond) market that you are invested in. One has therefore to be ready to be adaptive, tightening the (spending) belt in tough years and splurging a bit more during rosier years.
Example: John has $1,000,000 in his portfolio, he’s 60 and plans (conservatively) for a maximum of 40 years of happy retirement. His portfolio is 60% stocks / 40% bonds, and he computes R as 60%*5%+40%*2% = 3.8%. At the beginning of the first year, he can withdraw PMT(3.8%, 40, -1000000, 0, 1) = $47,235. Lucky him, the actual (nominal) return during the first year turns out to be 8.5%. His portfolio is therefore (1000000-47235)*(1+8.5%) = $1,033,750. At the beginning of the second year (39 years left to go!), he can withdraw PMT(3.8%, 39, -1033750, 0, 1) = $49,374. The second year sees the portfolio tank by 15.2%, ouch. His portfolio becomes (1033750-49374)*(1-15.2%) = $834,751. With some trepidation, John does the PMT math again: PMT(3.8%, 38, -834751, 0, 1) = $40,336 (time to sit tight and spend less during year 2, while patiently waiting for the market to recover). Etc.
What we just explained is the essence of the Bogleheads VPW variable withdrawal method, which is a very direct application of Time Value of Money concepts. This is exactly what Harry and Sally plan to do for regular portfolio withdrawals, but this leaves open the question of extra withdrawals during their early retirement, while cash flows are expected to be quite irregular.
Computing backwards: social security
Spreadsheet reference: ‘PV Math Blog’ tab, ‘Mary’s Social Security computations’ section.
Before discussing Harry and Sally’s scenario, let’s study a simpler case. Let’s assume that Mary has a portfolio of $1,000,000. She is 60 and exploring early retirement. Mary could take Social Security at 66 (FRA) and get $25,000 per year (inflation-adjusted). Or she could postpone SS until 70 and get a $34,000 fixed income. She cannot envision living past 95, so she’s planning for 35 (well, 36) years of happy retirement.
Those numbers take in account the fact that the Social Security Administration revalues the annual retirement income by approximately 8% (inflation-adjusted) every year during which SS payments are postponed. Which, granted, seems pretty sweet. This being said, one could wonder, how sweet is that? Could we try to quantify?
The following table shows the expected cash flows if Mary applies for Social Security in six years, when she turns 66. The fourth column performs a simple Present Value computation of the $25,000 income for Year 6, using a 4% rate of return (PV = $19,758). The fifth column does the same for the $25,000 income for Year 7. Etc. The full table (found in the reference spreadsheet) actually assumes that Mary lives until 95.
All computations being done, the cumulative Present Value of the SS payments till Mary passes away is $355,320. One could view such value as a ‘virtual portfolio’, to be combined with her (very real) portfolio of $1,000,000.
It is a reasonable plan for Mary to use a PMT formula on both values: PMT(4%, 36, -355320, 0, 1) + PMT(4%, 36, -1000000, 0, 1) = $68,922, and use such combined amount as her spending budget for the first year. The remaining (regular) portfolio will evolve according to the market, and Mary can simply redo such math the following year, taking in account the fact that she’s now 61. When she turns 66, she can continue to do the very exact same kind of math, or simplify (just use the PMT formula on the regular portfolio and add the social security income – she’ll find the very exact same number!).
Now, what about the idea of postponing Social Security till Mary turns 70? Let’s redo the same kind of present value computation.
As you can see, Mary can then afford to spend $70,268 during the first year, i.e. approximately $1,300 more than in the previous scenario. This is unsurprising as the rate of return we assumed (4%) in the present value computation is half the rate (~8%) the SSA uses to re-evaluate social security payment post FRA. Still, in the grand scheme of things, this is only a 2% improvement over the SS/66 scenario, a little more than $100 extra per month, something to put in perspective against the lack of stable fixed income until Mary turns 70.
Computing backwards: NPV() formula
Spreadsheet reference: ‘PV Math Blog’ tab, ‘Mary’s Social Security computations’ section.
If you found the previous computations a little overwhelming, here is some welcome relief. We can actually do the whole thing with just a couple of spreadsheet formulas.
The idea is to delete all the individual PV computations and use a single NPV() formula applying to the column of cash flows ($0, $0, …, $25,000, $25,000, etc). The first parameter of the NPV() formula is the expected rate of return (here 4%), the following parameters are references to the cells containing the annual cash flows, et voila, you get the cumulative present value of the future cash flows. Well, almost. Annoyingly enough, the NPV() formula discounts the first cash flow value, so you should multiply by an extra (1+R) term, i.e. use the formula NPV(R, cash flows) * (1+R). One could also express that as cash-flow-current-year + NPV(R, future cash flows), which is equivalent.
After performing such NPV() math, you can just do the PMT math as previously explained on the ‘virtual’ portfolio and the regular portfolio and that’s it, all done in a few concise spreadsheet formulas.
Back to Harry and Sally
Spreadsheet reference: ‘PV Math Blog’ tab, ‘Harry and Sally’s computations’ section.
In the case of Harry and Sally, we have a much more complicated sequence of cash flows. No matter though, we can still use the NPV() formula in pretty much the same way. A good way to proceed is to organize in columns the various types of cash flows over time (income as well as exceptional expenses like the mortgage or a big wedding for daddy’s daughter!), then sum up each row (corresponding to a year being planned for). And then it’s just a matter of performing the NPV() math on such combined annual cash flows.
A slight complication comes from numbers which are not naturally adjusted for inflation. Pensions often work like that nowadays, they only partly adjusted for inflation. And since it is more intuitive to do the entire modeling in real (inflation-adjusted) terms, one may need to apply some adjustments to the pension numbers.
The full model is provided in the companion spreadsheet, please check the cell notes (and formulas) to better understand the scenario being modeled.
Of course, this kind of approach lends itself well to scenario analysis (what if Harry does some part-time work in the early years? what if they don’t postpone social security? what if Harry and Sally stay in the same house, not downsizing? what if another rate of return is used? Etc.)
The spreadsheet also includes some simple backtesting logic to test how things would have unfolded in the past for Harry and Sally (knowing the historical returns of the stock and bonds market), depending on the starting year. The following chart illustrates a retirement starting in 1960, and the severe effect of the oil crisis in the mid-70s. Harry and Sally would have been very relieved to get their fixed retirement income (social security, pension, etc) by then. Note how the annuitization logic mandates a significant belt tightening during the crisis, and how this would have allowed Harry and Sally to navigate the crisis without unduly impacting their twilight years.
Here is another simulation, starting in 1970. It would probably have been easier to handle in the early years, thanks to cash flows inputs coming at a fortunate time to counter balance the stock market drop. The latter years would have been more psychologically difficult (the Internet and Financial crises compounding the regular decline due to the portfolio depletion goal), but remember, this stretches the expected lifetime of Harry and Sally to an age which is very improbable to reach. In real life, it is very unlikely that the couple would have lived to see such steep drop, and if they did enjoy exceptional health in their early 90s, they could have tweaked the plan for an even more extended life, spreading the savings a bit longer.
Still, it is easy to backtest in hindsight while being aware of the remarkable recovery that followed this deep crisis, but it is an entirely different matter to navigate such situation in real life when the portfolio is hammered by the combination of regular withdrawals, extra withdrawals and a big drop in value and you just don’t know what the future will be made of. This begs some questions that we will explore in a follow-up article.
A simple calculator
Spreadsheet reference: ‘Simple Example’ tab and ‘Readme’ tab.
Now what about YOUR personal situation? If you are an early retiree (or planning to be one) and have a do-it-yourself profile like most Bogleheads, you could now develop your own ‘Time Value of Money’ calculation, make various assumptions to run what-if scenarios, and see what goes.
As a starting point, please check the ‘Simple Example’ tab in the reference spreadsheet (and corresponding instructions at the beginning of the ‘README’ tab). Remember that input cells are in yellow, the rest is calculated.
The first step is to customize the settings for your portfolio (on the upper part). The second step is to customize the expected cash flows during early retirement and during regular retirement. The structure in the ‘Simple Example’ tab is just a starting point, feel free to insert more columns, e.g. per spouse and per type of cash flow. You can also adjust the number of rows at the end, based on a VERY conservative view of your expected lifetime (e.g. plan till your late 90s or even 100). Do investigate the formulas to fully understand how this works.
The outcome of the computation will be the SPENDING BUDGET YEAR 0. Now, where does this money come from? First, use whatever cash flow income you get this year (e.g. part-time wages, rental property, pension, etc). Next, withdraw the difference from your regular portfolio (dividends included). If the cash flow income of the current year happens to exceed the spending budget, then invest the remainder in the portfolio. It is all quite simple and intuitive to do.
Finally, there is a warning system (spending gate) which we will explain in a follow-up article.
Next Steps
Based on a few simple spreadsheet formulas and making a good use of Time Value of Money concepts, we were able to model a complicated early retirement situation in a simple manner. The author developed this approach while analyzing his own financial situation (somewhat similar to Harry and Sally!) and is now using it every year to establish his annual spending budget. The author also explored the application of the same principles to the personal situation of a few fellow Bogleheads. Clearly, every case is unique, but the flexibility of the NPV/PMT approach easily allowed to accommodate every case. Any of the factors involved can be changed to accommodate changing life situations, unexpected expenses and extra income, changing market conditions, allowing to easily tweak the plan. Overall, the approach seems to hold pretty well.
In a follow-up article, we will discuss additional features of the companion spreadsheet, notably:
- considerations about the expected rate of return
- ‘double whammy’ risks and spending gates
- more backtesting features
PS. additional explanations and applications of the Time Value of Money concept can be found on the following Wiki page: https://www.bogleheads.org/wiki/Comparing_investments
Financial Page 