This blog article is the third of a series intended to document the inner workings of the Simba backtesting spreadsheet. The objective is two-fold:
- Provide a better understanding of how the spreadsheet works, to facilitate peer reviews and future maintenance.
- Describe techniques that have been used to compute various investment metrics in a compact manner, which could be useful in other contexts (e.g. your own spreadsheet).
The first article of the series discussed the layered structure of the spreadsheet. The second article discussed risk metrics and ratios. The intent of this third article is to elaborate on more advanced topics (e.g. unbalancing, safe withdrawal rate, etc). We’ll notably discover that a Safe Withdrawal Rate is just a simple harmonic mean.
To follow this article in detail, you should open a separate window using version 17a of the Simba spreadsheet, which can be downloaded by clicking on this link. The cell references are of course subject to change in future updates, even if the general structure will probably stay very similar.
Rebalancing and unbalancing
When computing statistics and metrics illustrating the trajectory of a portfolio using passive indexing funds, there is often an assumption that the asset allocation is fixed, not changing over the years. The simple fact of computing a portfolio annual return by multiplying the annual returns of individual components (asset classes) by the (fixed) weight of those components (i.e. the asset allocation) makes an implicit assumption of annual rebalancing.
The Simba spreadsheet isn’t designed to provide analysis of other rebalancing techniques (e.g. trigger-based), nor of asset allocation glide paths. It does illustrate though what would have happened to a portfolio if no rebalancing at all would have been used (i.e. managing an unbalanced portfolio, or ‘unbalancing’).
Mechanically speaking, this proved a bit challenging to implement, as most formulas in the Portfolio_Math worksheet implicitly rely on the (fixed) asset allocation, while unbalancing simply lets individual asset classes components of the portfolio grow on their own, hence departing from the original asset allocation. The problem had to be solved while respecting the layering principles of the spreadsheet explained in the first article of the series.
First, in the Data_TR_USD worksheet, a table was added at the very bottom (e.g. starting from row 485), with a simple incremental computation of the growth of $1 over the years for this asset class, starting in 1871. When a historical return isn’t available for a given year and a given asset class, a growth of 0% is assumed to avoid errors in dependent cells.
Such table isn’t dependent on a given time period of interest (e.g. 1970 until 2017, or something like that), but dividing the 2017 value by the 1969 value will yield the cumulative growth during such time period.
Let’s examine how the Portfolio_Math logic leverages such per-asset-class growth data. The trick is to compute the trajectory of the portfolio first, and then to infer the corresponding annual returns (instead of the reverse way around for balanced portfolios). Rows 728 and below implement such logic. Let’s look at the formulas around year 1970.
In cell M827, for the year right before the start of the time period of interest (calculated as M$85-1), the formula’s output will simply be the value of cell M84, which holds a copy of the initial investment. After that, each individual asset class grows at its own pace, apportioned to its initial weight in the portfolio. The SUMPRODUCT() formula does exactly that, as we can see in cell M833:
- the TRANSPOSE() function provides such initial weights,
- the first reference to Data_TR_USD (referencing row 592) obtains the absolute growth of the asset class in 1975,
- the second reference to Data_TR_USD (more precisely its reciprocal) will make the growth of the asset class relative to the starting year when SUMPRODUCT() multiplies by the first two factors.
- Finally, SUMPRODUCT() -as an array formula- will sum the weighted growth of all the components of the portfolio, to end up with the portfolio’s value for this year.
Once we have such portfolio trajectory, then things become easy again. Rows 1044 and below compute the annual returns of such unbalanced portfolio, and further analysis will come easily.
Note that the asset class math is used elsewhere in the Analyze_Portfolio spreadsheet. Check the formula in cell H5 and below, and you’ll quickly understand how this works.
Safe Withdrawal Rate (SWR) and related constructs
The concept of Safe Withdrawal Rate is well known. This is the historically lowest withdrawal rate for retirees to avoid running out of money in retirement, assuming fixed portfolio withdrawals of the same amount (inflation-adjusted) every year. Mechanically, this implies to run all retirement cycles (say 30 years cycles) in all of known history (or maybe a more restricted time period) and see which cycle required the lowest withdrawal rate (aka Sustainable Spending Rate, SSR) to avoid bankruptcy. A naive implementation can become quite computationally intensive (e.g. using iterative computations, data tables in a spreadsheet, etc).
Fortunately, it is possible to compute the SSR metric in a very compact manner (and then it’s just a matter of computing the MIN() value across all cycles). It will probably not be obvious WHY it works though! Let’s start by looking at the following equation (where R1 is the real return of year 1, etc):
(All credits to David Blanchett; this formula appeared in appendix 1 of this article, albeit with an unfortunate typo – an extraneous ‘+1’ in the denominator)
(Side note: this equation was formatted with iMathEQ – then I took a screenshot. Much more intuitive than LaTeX!)
Why is that correct? Let’s take it step by step.
- Cycle of one year. Start from portfolio P (initial investment). Withdrawal is P * SSR. To bring the portfolio to zero by the end of the year, assuming a withdrawal at the beginning of the year, this means that P * (1-SSR) = 0, so SSR = 1 (i.e. 100%). Ok, no big surprise.
- Cycle of two years. After one year, the portfolio is worth P * (1-SSR) * (1+R1), where R1 is the annual return for the portfolio’s asset allocation. If the portfolio goes down to zero at the end of the second year, then P * [ (1-SSR)*(1+R1) – SSR ] is equal to zero. That is (1+R1) – SSR * [(1+R1) + 1] = 0. So SSR is equal to (1+R1) / [(1+R1) + 1].
- Cycle of three years:
- After two years, the portfolio is worth: P * [ (1-SSR)*(1+R1) – SSR ] * (1+R2).
- Therefore the goal is to bring P * ([ (1-SSR)*(1+R1) – SSR ] * (1+R2) – SSR) to zero.
- That means that (1+R1)*(1+R2) – SSR * [(1+R1)*(1+R2) + (1+R2) + 1] = 0.
- So SSR is equal to (1+R1)*(1+R2) / [(1+R1)*(1+R2) + (1+R2) + 1].
- Therefore 1/SSR is equal to [(1+R1)*(1+R2) + (1+R2) + 1] / (1+R1)*(1+R2). Which is clearly the same thing as Blanchett’s equation.
- Note that since we assumed a constant withdrawal of P * SSR, the entire math has to be performed in real (inflation-adjusted) terms. Therefore R1, R2, etc are the inflation-adjusted returns for the corresponding years.
While we’re doing some algebra, let’s ponder about the concept of a Perpetual Spending Rate (PSR) – which leads to the concept of Perpetual Withdrawal Rate (PWR). Here is the idea is that at the end of the cycle, the portfolio isn’t depleted, but is back to where it started, in real (inflation-adjusted) terms.
- Let’s jump straight to the cycle of three years. At beginning of Year 3, after the last withdrawal, what we have left is:
- P * ([ (1-PSR)*(1+R1) – PSR ] * (1+R2) – PSR)
- We need to account for the returns of the last year though (R3), so the equation to solve is:
- P * ([ (1-PSR)*(1+R1) – PSR ] * (1+R2) – PSR) * (1+R3) = P
- And… let’s leave it as an exercise for the reader to 1) solve the equation 2) re-formulate it, so that PSR becomes a direct function of SSR and of the CAGR of the entire cycle. You can also cheat, skip ahead, and find the answer in Portfolio_Math, cell M2625 and below!
Back to SSR. Now that we convinced ourselves that the Blanchett formula (minus the typo) is correct, the question becomes how to compute such quantity in a very compact manner with a backtesting spreadsheet.
First, let’s ponder about terms like (1+R1), (1+R1)*(1+R2), (1+R1)*(1+R2)*(1+R3), etc. This is essentially the cumulative growth of $1 after one year, two years, three years, etc. So we can revisit the Blanchett formula, and think of the denominators as growth of $1 (t0 to end of year1), growth of $1 (t0 to end of year2), growth of $1 (t0 to end of year3), etc.
Then one can realize that SSR is simply the harmonic mean of those cumulative growth quantities, divided by the number of years of the cycle. Lo and behold, the HARMEAN() spreadsheet function suddenly becomes very handy. Which means that we can rewrite the Blanchett formula as:
Well, we already have a handy growth computation in the Portfolio_Math worksheet, the growth of the portfolio balance for an initial investment, performed in real terms. We just need to divide by the initial investment to recalibrate. The outcome of rows 570 and below can be leveraged for our SSR needs in other words. Go to Portfolio_Math, check the example of cell M2571 (year 1975, as illustrated below) and the corresponding formula should now become crystal clear. That was a very long explanation for a very concise formula!
To further exemplify the simplicity of the formulas, an online spreadsheet was assembled to illustrate how to go from returns to SWR/PWR in just a couple of steps.
To finish with one more subtlety, let’s go back to Compare_Portfolios, and the SWR formula, which is NOT expressed with a MIN() function as one might expect.
The point of using a PERCENTILE() function (driven by cell D123) is to allow the user to obtain more flexible statistics. The absolute lowest withdrawal rate corresponds to a one-time event in the past which will certainly not occur in the future in exactly the same way. Also, one might say that gating yourself by the absolute worst case scenario is no way to live. Typical literature about Safe Withdrawal Rates tends to use more flexible perspectives like “95% probability of failure”, etc. The PERCENTILE() function allows to look at a slice of the worst cycles instead of the absolute worst. Just be careful that a percentile isn’t an average. For example, quoting Wikipedia, the 20th percentile is the value (or score) below which 20% of the observations may be found. This is NOT the average of the 20% lowest values.
Finally, moving to the Analyze_Portfolio worksheet, a flexible chart allows comparing various metrics (e.g. CAGR, SWR, PWR, etc) for all cycles that can fit in the time period of interest (constrained by starting and ending years). The chart below visualizes a comparison of safe withdrawal rates across time (for each individual cycle), while comparing between a custom portfolio and a benchmark portfolio. In this case, it is interesting to note that there are a few cases where the outcome is basically the same, notably the worst cycle (1973, the oil crisis). And yet, overall, the custom portfolio seems much more attractive. Don’t let yourself be gated by the worst case scenario.
Summary
The Simba spreadsheet occasionally uses some formulas which require extensive background to be fully understood. For such a spreadsheet processing a lot of numbers, it is important to find concise formulas, while respecting its layering principles, in order to ease usability as well as maintainability.
In addition, some of the concepts described in this article can be easily applied to other backtesting models, and hopefully will prove useful beyond the scope of the Simba spreadsheet.
Financial Page 