It looks like if you try to put an extra withdrawal (column O) in a year after retirement date, it doesn’t adjust the regular withdrawal (column Q) going forward because because the PMT formula is only in the row for the retirement year. This causes the final balance to be negative in cell W132.Ben Mathew wrote: ↑Thu Feb 18, 2021 5:40 pm TPAW: ACCUMULATION PHASE
I have added a spreadsheet to the TPAW wiki that covers the accumulation phase.
The accumulation spreadsheet is similar to the withdrawal one, with only a few tweaks necessary to the wording and interpretation. That's because the underlying model covers saving and investing over a lifetime and there isn't really a conceptual difference between what's happening during accumulation vs withdrawal. In both phases, the goal is to maintain a fixed allocation on the total portfolio.
A conservative estimate of future savings during working years is now added to the retirement income column. The model will typically assume high levels of leverage when young because the savings portfolio is small relative to future savings. It's up to the user to decide what their maximum acceptable AA is going to be and whether it will involve leverage. If they don't achieve the target AA, the withdrawal calculations will be off. By mid to late career, it's more likely that the savings portfolio is large enough that the target AA can be maintained without requiring leverage. So the withdrawal distribution displayed will become more accurate at that stage. In early career, even though the withdrawal numbers won't be accurate because the assumed AA is not achieved, the calculations can still serve as a guide to AA. For example, if the spreadsheet says that you should leverage 300%, and you are not willing or able to leverage that much, you can instead just go up to the top of your acceptable AA range.
The example below shows a 25 year old with $30,000 in current savings. They expect to save $10,000 per year till retirement at age 65, and to draw $20,000 from social security starting age 70. They select an AA of 30/70 on the total portfolio, and withdrawal growth rate (g) of .30%. This gives them the following withdrawal distribution, with withdrawals scheduled to grow from $51,140 at age 65 to $56,793 by age 100:
The projected glidepath for the savings portfolio is shown in the table below, in red on the right. The 30/70 fixed AA on the total portfolio translates to a savings portfolio glidepath that starts at 1300% stocks (i.e. 1200% leverage) at age 25, gliding down to 100% stocks at age 41, and stabilizing around 50% stocks around age 65. If the user is not willing or able to take on the leverage indicated in any given year (likely to be the case in the early stages), then they would simply go up to the top of their acceptable AA range that year.
Q&A
How is this related to lifecycle investing?
The underlying model behind TPAW is a lifecycle model, which is a broad term for a model that optimizes consumption over a lifetime. Lifecycle models are the basic models of saving and investing in economics. The strategy that emerges from these models involves spreading consumption and investment risk as much as possible. Spreading investment risk across time means taking enough risk in early years, which will usually require leverage because the savings portfolio is too small to take on enough risk even with 100% stocks. High borrowing costs, call risk, psychological distress, and other such considerations may limit the amount of leverage you want to take on. TPAW leaves it to the user to decide what their personal max AA is, and whether or not it involves leverage. It takes no position on whether you should leverage. If you don't want to leverage, just go up to 100% stocks when the calculator calls for leverage.
Shouldn’t the PMT formula go in every row, and use column C as the nper each year? The PMT formula would need absolute references instead of relative for the expected real return (cell I35) and the growth rate (cell Q51) before doing a drag and drop.
Let me know if I’m missing something as to why it’s set up the way that it is currently.
Edit: I guess the problem with my solution is it only changes the regular withdrawals from the point of the extra withdrawal and going forward, when really it should impact the regular withdrawal for every year. Maybe you have a better solution than me, if you do in fact see the same issue that I’m seeing with post-retirement extra withdrawals.