A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

1 | ||||||||||||||||||||||||||

2 | This spreadsheet illustrates the use of 'time-value of money' techniques to approach adaptive budgeting during pre/early retirement years. | |||||||||||||||||||||||||

3 | The principles being used extend the concepts behind the Bogleheads VPW withdrawal method and are geared at 'hands on' users who are familiar with spreadsheets and require more than a pre-canned formula. | |||||||||||||||||||||||||

4 | Author: Siamond | Last change: 02/05/2019 | Bogleheads forum discussion thread: | https://www.bogleheads.org/forum/viewtopic.php?f=2&t=263790 | ||||||||||||||||||||||

5 | Spreadsheet link: | http://bit.ly/2WuNmvf | Explanatory blog article: | https://finpage.blog/2019/02/01/early-retirement-and-time-value-of-money/ | ||||||||||||||||||||||

6 | ||||||||||||||||||||||||||

7 | General idea | |||||||||||||||||||||||||

8 | The point is to make a reasonable projection of future cash flows (from now till the end of retirement) in inflation-adjusted terms, combine with the current portfolio (savings), and derive a spending budget for the year. | |||||||||||||||||||||||||

9 | - cash flows can include lifelong recurring income, inflation-adjusted or not, e.g. social security, pensions, annuity payments, etc. | |||||||||||||||||||||||||

10 | - cash flows can include time-limited recurring income, e.g. wages (full-time or part-time), temporary property rental, etc. | |||||||||||||||||||||||||

11 | - although uncommon, cash flows may also include time-limited exceptional recurring expenses (e.g. last few years of mortgage, insurance premiums before Medicare, extras for relatives staying at home for a few years, etc) | |||||||||||||||||||||||||

12 | - cash flows can include one-time lump sums, income (e.g. house downsizing, inheritance, etc) or exceptional expenses (e.g. big wedding, buy a second home or a boat when retiring, purchase an annuity, etc). | |||||||||||||||||||||||||

13 | - cash flows do NOT include regular expenses, basic or discretionary. The entire point is to compute a spending budget for such regular expenses. | |||||||||||||||||||||||||

14 | - cash flows are analyzed using a 'time value of money' spreadsheet formula, i.e. NPV(), leading to a Present Value of such cash flows. | |||||||||||||||||||||||||

15 | - the spending budget for the year (pre-tax) is derived from annuitizing the regular portfolio (savings) plus the present value of the cash flows, using a simple actuarial formula, i.e. PMT(). | |||||||||||||||||||||||||

16 | - the spending money will come in priority from income cash flows, then by portfolio withdrawals (from any type of retirement/brokerage account). Extra income is assumed to be invested in the portfolio. | |||||||||||||||||||||||||

17 | - optional 'spending gate' computations will raise a warning if the portfolio is at severe risk of depletion before full SS/Pension income kicks in | |||||||||||||||||||||||||

18 | - if such a 'spending gate' warning is raised, it is strongly recommended to tighten the belt according to such upper gate and/or reassess the whole plan. | |||||||||||||||||||||||||

19 | - creating multiple instances of this spreadsheet (or additional tabs) could allow to perform scenario analysis (e.g. what if Social Security is postponed or not, what if house downsizing happens or not, etc). | |||||||||||||||||||||||||

20 | - this spreadsheet is intended as a starting point, advanced users will probably modify and extend it in various ways. | |||||||||||||||||||||||||

21 | ||||||||||||||||||||||||||

22 | ||||||||||||||||||||||||||

23 | Simple Example tab | |||||||||||||||||||||||||

24 | ||||||||||||||||||||||||||

25 | - This tab provides a basic example of the computations being performed. Check the individual cell notes for more information. Yellow cells are inputs to be customized. | |||||||||||||||||||||||||

26 | - Columns B to E allow to capture the various types of cash flows being expected (to be customized at will), while column G automatically sums up the annual cash flows. Use negative numbers for expenses. | |||||||||||||||||||||||||

27 | - Note that it could be a good idea to add more columns to make things more clear (e.g. separate cash flow columns per individual and per type of income/expense). Adjust the SUM() formula in column G as needs be. | |||||||||||||||||||||||||

28 | - Cells I7 to G11 allow to document the existing portfolio (savings), plus its (fixed) asset allocation, and to derive a very coarse estimate of expected returns (assuming the future will roughly rhyme with historical averages). | |||||||||||||||||||||||||

29 | - Cells I14 to I18 compute the present value of future cash flows, and derive an annuitized value for the current year. | |||||||||||||||||||||||||

30 | - Cells I21 to I24 compute the annuitization on the regular portfolio (in a very similar way to the VPW withdrawal method) and then combine the outcome with the cash flows math to derive a spending budget for the current year (cell I24). | |||||||||||||||||||||||||

31 | - The various rates used in NPV and PMT computations can be customized for advanced testing, but in general, it is recommended to keep them consistent, i.e. all equal to the same value. | |||||||||||||||||||||||||

32 | - Note that discount rates do not depend on the nature of the various types of cash flows, this is better understood as the expected rate of return for the portfolio capital you don't have to spend thanks to such (cash flow) income. | |||||||||||||||||||||||||

33 | - If the cash flows are positive in the current year and exceed the spending budget, it is assumed that the difference is invested in the regular portfolio via some investment account (e.g. 401k, brokerage, etc). | |||||||||||||||||||||||||

34 | - If the spending budget exceeds the cash flows in the current year, it is assumed that the difference is withdrawn from the portfolio. | |||||||||||||||||||||||||

35 | - Such budgeting approach allows to amortize the trajectory of annual spending, notably in the pre/early retirement years when cash flows can be quite different from one year to the next. | |||||||||||||||||||||||||

36 | - Such budgeting approach is strongly linked to the performance of the portfolio over the years, it implies flexibility and adaptiveness from the retiree, e.g. through expanding or constraining discretionary expenses. | |||||||||||||||||||||||||

37 | ||||||||||||||||||||||||||

38 | - A primary risk of such 'present value' approach is a "double-whammy" on the regular portfolio, possibly leading to spend a lot of devalued shares (e.g. during a deep stock crisis in early retirement) and subsequent portfolio depletion. | |||||||||||||||||||||||||

39 | - This is a side effect of the computation of present value of future cash flows, which leads to withdraw more aggressively from the regular portfolio in early retirement years (this is usually ok, but can get troublesome during a deep crisis). | |||||||||||||||||||||||||

40 | - To try to mitigate such risk, a (soft) spending gate is computed and a warning (cell I33) is displayed if the regular spending budget exceeds such spending gate. | |||||||||||||||||||||||||

41 | - This spending gate (cell I32) is based in part on the present value of the cash flows until full SS/Pensions income kicks in, and in part on the current value of the portfolio vs. the desired outcome once full SS/Pensions kick in. | |||||||||||||||||||||||||

42 | - If the spending gate is exceeded, it is strongly recommended to tighten the belt, e.g. 'gate' the spending to such lower value. Furthermore, the whole early retirement plan may need to be revisited (some extra income would be welcome!). | |||||||||||||||||||||||||

43 | - Cell I27 allows to enter the number of pre/early retirement years until full SS/Pensions income kicks in. | |||||||||||||||||||||||||

44 | - Cell I30 allows to enter the minimum tolerable limit (aka stretch goal) for the portfolio by the time full SS/Pension income kicks in. | |||||||||||||||||||||||||

45 | - Careful thought has to be given to such portfolio limit, which is highly dependent on one's personal circumstances. It is NOT a good idea to set it above a strict minimum, and should definitely not exceed 50% of the initial portfolio. | |||||||||||||||||||||||||

46 | ||||||||||||||||||||||||||

47 | ||||||||||||||||||||||||||

48 | Scenario tabs | |||||||||||||||||||||||||

49 | - Those tabs allow to illustrate the effect of the spending budget computation over the years and to backtest such trajectory against various past time periods. | |||||||||||||||||||||||||

50 | - Although the future might be somewhat different from the past, such backtesting is a good way to check the robustness of an early retirement plan. | |||||||||||||||||||||||||

51 | ||||||||||||||||||||||||||

52 | - Scenario1 is intended to be fairly realistic. Starting portfolio of $1M (cell J6), a few years of part-time work in early retirement (column E), a $200k lump sum (column D), before SS/Pensions kick in 10 years later. | |||||||||||||||||||||||||

53 | - Columns D to I perform the cash flow math, while columns K to L do the math about the regular portfolio, and column N sums up the outcomes, providing a raw spending budget. Same logic as the Simple Example. | |||||||||||||||||||||||||

54 | - Columns O to P perform the (soft) spending gate math IF cell P1 is set to TRUE, while column S applies the constraints (floor and upper gates) to the actual spending budget. Same logic as the Simple Example. | |||||||||||||||||||||||||

55 | - In addition, a spending floor can be defined in column R (spending budget will never go under such value - unless the portfolio/income is insufficient, i.e. bankruptcy occurs). Be careful to set it to no more than a truly barebone budget. | |||||||||||||||||||||||||

56 | - Cell L4 allows to extend the duration of the regular portfolio annuitization, adding X years beyond the expected lifetime. This protects the portfolio against fast depletion during twilight years (e.g. LTC and/or bequest goal, longevity risk, etc). | |||||||||||||||||||||||||

57 | - Cell X4 allows to select a portfolio asset allocation fairly closed to yours (or the one you want to experiment with). | |||||||||||||||||||||||||

58 | - Cell U4 allows to select the starting year for backtesting purposes (1929, 1937, 1946, 1965/66, 1969, 1973 are tough starting points; 1975 is a very rosy one; 1955 is a mild one) | |||||||||||||||||||||||||

59 | - The combinations of cells U4 and X4 determine the annual (real) returns for the backtesting simulation. Cell X1 provides a default rate-of-return, computed based on historical averages (VPW-like). | |||||||||||||||||||||||||

60 | - The charts around line 50 show the resulting spending budget trajectory and the (regular) portfolio trajectory, in inflation-adjusted terms. | |||||||||||||||||||||||||

61 | - Outcomes should work well most of the time, although tough starting years like 1969 or 1973 show opportunities for some belt tightening (note how low the portfolio hovers by then). Try enabling the soft spending gate and see the changes. | |||||||||||||||||||||||||

62 | - The portfolio low limit (cell P4) aims at getting around $20k above fixed income (SS/Pensions) out of the portfolio after a deep crisis in early retirement. A 5% average rate is probably more realistic by then (rebound after crisis). | |||||||||||||||||||||||||

63 | - It is recommended to duplicate this tab before starting to customize with your own numbers. | |||||||||||||||||||||||||

64 | ||||||||||||||||||||||||||

65 | - Scenario2 is designed as a stretch (not quite plausible) scenario to better illustrate double-whammy risks and spending gates. | |||||||||||||||||||||||||

66 | - Scenario2 is characterized by a VERY LOW starting portfolio, some part-time work in early retirement, a lump sum occurring mid-way, before HIGH SS/Pensions kick in 10 years later. | |||||||||||||||||||||||||

67 | - Using the regular annuitization math may lead to quickly deplete the portfolio and even the lump sum doesn't save the day. Example: starting year 1973, see how both portfolio and spending budget fall to zero (twice!). | |||||||||||||||||||||||||

68 | - This is due to the strong imbalance between the starting portfolio and the full SS/Pension regimen, compounded by a deep crisis in the early years. | |||||||||||||||||||||||||

69 | - Enabling the (soft) spending gate, based on a low portfolio limit of $0 (i.e. use all of the -meager- starting portfolio in early years), restores a more satisfying trajectory, avoiding premature bankruptcy. | |||||||||||||||||||||||||

70 | - This is all walking on a thin line though. If one were to eliminate the lump sum mid-way, this may still fall apart due to the annuitization of the cash flows over the pre-SS years (and the lack of any buffer with the low starting portfolio). | |||||||||||||||||||||||||

71 | - Although we're reaching the limits of plausibility here, another ('harder') approach to the spending gate is implemented in column Q to fully prevent spending money not yet available. This leads to a more hectic spending trajectory though. | |||||||||||||||||||||||||

72 | - Column Q (hard spending gate) computation removes the annuitization of cash flows till full Pension/SS performed in the soft spending gate, and replaces it by the income (or lack thereof) coming from the year's cash flow. | |||||||||||||||||||||||||

73 | ||||||||||||||||||||||||||

74 | ||||||||||||||||||||||||||

75 | PV Math Blog tab | |||||||||||||||||||||||||

76 | ||||||||||||||||||||||||||

77 | - This provides all computations and graphs used in the explanatory blog related to this general topic (see link above). | |||||||||||||||||||||||||

78 | ||||||||||||||||||||||||||

79 | ||||||||||||||||||||||||||

80 | ||||||||||||||||||||||||||

81 | ||||||||||||||||||||||||||

82 | ||||||||||||||||||||||||||

83 | ||||||||||||||||||||||||||

84 | ||||||||||||||||||||||||||

85 | ||||||||||||||||||||||||||

86 | ||||||||||||||||||||||||||

87 | ||||||||||||||||||||||||||

88 | ||||||||||||||||||||||||||

89 | ||||||||||||||||||||||||||

90 | ||||||||||||||||||||||||||

91 | ||||||||||||||||||||||||||

92 | ||||||||||||||||||||||||||

93 | ||||||||||||||||||||||||||

94 | ||||||||||||||||||||||||||

95 | ||||||||||||||||||||||||||

96 | ||||||||||||||||||||||||||

97 | ||||||||||||||||||||||||||

98 | ||||||||||||||||||||||||||

99 | ||||||||||||||||||||||||||

100 |

Loading...

Main menu