FREE NEWSLETTER
Go to main Forum page »
Engineering Economics was a required course for my college major. Being introduced to concepts such as present worth, future value, continuous cash flow, compounding, rate of return, time values of sums, and how to factor in taxes and depreciation among other economic principles have been most useful in managing my own finances. When MS Excel was introduced a few years later, I found it useful to create economic models to evaluate various financial decisions. I also use Quicken to track our investment portfolio, including transactions, portfolio value, asset allocation, performance and distributions, which encompass income and unrealized and realized capital gains.
I’ll provide a few examples of models I’ve created primarily to stimulate ideas for readers to pursue for their own financial needs. I export quarterly net worth data from Quicken into Excel to project our future net worth, using the trendline option and a 3rd order polynomial curve fitting function, achieving an R2 = 0.99. This shows graphically the power of continuous investing, compounding and careful tax management. Another example involves evaluating when my wife should claim Social Security (SS). This model projects a time sequence of cumulative gross benefits, including cost-of-living adjustments (COLA) and net value after factoring in an investment rate of return (ROR) and taxes owed on the benefit payments and investment income. In her case, I used an annualized COLA of 1.5%, an effective tax rate of 20% and a modest investment ROR of 5%. It’s easy to analyze perturbations of these factors with Excel. The results indicated, waiting until 67 (full retirement age), the break-even age is 80, meaning the cumulative net benefit received will exceed that if benefits began at age 62 and were not invested. If benefits were invested, by waiting until 67, the break-even age is 88 and by waiting until 70, the break-even age is 89. There are additional factors to consider such as Medicare’s Income-Related Monthly Adjustment Amount, general health and other tax implications.
When my wife retired from the US military, she enrolled in the spousal survivor benefits plan (SBP) with the premium deducted from her monthly annuity. After making SBP payments for 10 years she had an opportunity last year to cancel the plan; otherwise, she would have to continue paying premiums for another 20 years, which so far increased at an annualized rate of 1.64%. Out of curiosity, I created what became a fairly complex Excel model to analyze whether my wife, who is 6 years younger, should cancel SBP. The premium amount paid thus far represents a sunk cost if she opted out. Instead of paying the premium, those funds could be invested at an assumed ROR of 5% per year compounded and an effective tax rate of 20%.
The objective of the analysis was to determine, over her lifespan, at what age I would reach a spousal benefit that exceeds the value of premiums invested. This analysis required applying future value and future value of an annuity functions for an arbitrary period of 25 years. I generated a table and series of curves to visualize the results, while also considering the actuarial probabilities of us both living to progressively older ages. The analysis quantified the cumulative SBP payout compared to the premium amount invested over time. For instance, if she were to pass away 18 years from now, it would take 7 years for SBP payouts to surpass the amount paid into SBP and an amount that could be made by investing the premium. I would be 91 then (a 51% probability of achieving). We decided she would opt out of SBP.
Ed. Very good stuff. I enjoyed. I’ve used Excel is many ways, some similar to you. Keep on calculating!
I have no clue what any of this meant. I probably could if I were to take the time to try. In the end over thinking stresses me out and all of the above is based on assumptions.
As far as when to take S.S. we can both took it at FRA , why… If we wait until seventy we do not know if we will live until eight-one years old to recoup the difference. We do know historical market return averages, so that is where we put our S.S. checks every month.
Wow, as Jed Clampett would say, that is some serious ciphering. Way above my pay grade but no doubt helped you maximize options.
Knowing the probability of any possible event in a plan may be less important than fully appreciating its consequences.
David, you and Richard make good points. If we don’t know for certain we have to make assumptions. While models may be mathematically correct, results are only as good as the input. If probability based data is available for these assumptions, it’s better than a guess. So decisions can be made either subjectively or objectively, qualitatively or quantitatively. As a starting point in making a decision and if available I prefer basing it on a quantitative analysis. But it’s also paramount to consider the consequences. Sometimes, something just doesn’t feel right.
As regular HD readers know, for me this is all Greek. But you clearly enjoy this type of analysis, so enjoy.
As you no doubt know, it all depends on assumptions and their fulfillment. I hope reality doesn’t throw you for a loop.