Running some different numbers on a spend-liability estimate for a balance sheet

I've been toying around lately with the valuation of the spend liability on my balance sheet (a household meta-balance sheet which should include, in addition to financial assets and debt, the NPV of flow or future assets like social security, human capital, annuities, pensions, spending, long term care, etc.). This toying around came after some recent discussions I've been having on this subject with some people with wiser heads than mine.  I haven't changed the way I look at this in years (see # 1 below) but there are probably an infinite number of ways to estimate the liability so why not reconsider. I'm thinking in this post about three ways in particular:  

1. Discounted cash flow over a conservative fixed period, say to age 95,
2. Discounted cash flow over an infinite or really long time frame but weighted by conditional survival probability, and
3. Rather than just a point estimate, we could also create a distribution of many spend liabilities by breathing some variability into some of the pieces that make up the spend liability calc (in this case I'm focusing on inflation and longevity but that randomizing impulse could snare other pieces like spending variance before we even get to inflation, spend shocks, maybe even discount rates, whatever…) in which case we would look at either an average or a median or maybe some percentile on the cumulative distribution as a "bad" or "worst" case for planning.

The Idea

I've done #1 and #2 before but not #3.  I'm not sure if it (3) is really necessary or even a legit exercise (someday I'll ask an econ or finance prof) but I was curious what it would look like.  It's achieved through simulation of course but it's also an NPV or, rather, a distribution of NPVs. I'm not really sure what it is.  I could get the same reality-check by testing the sensitivity of a spend liability NPV to different inflation or age range assumptions but I also like the idea of a seeing the shape of a distribution. Even if this is not a legit thing to do, let's take a look anyway just for the  hell of it. 

The Setup

Here is the setup and assumptions: male aged 59 with either a planning horizon of age 95 or, alternatively, to infinity but if so it's an infinity with knowledge of longevity probabilities; spend rate of 100k [not my personal assumption; this is just a placeholder]; assets are unknown and irrelevant in what I'm doing in this post; inflation is 3% plus or minus a 1% std dev [see note 1!]; longevity is random within a mortality process described by Gompertz-Makeham math with a mode of 87.25 and a dispersion of 9.5 which more or less splits a difference between the SS life table and the SOA IAM table for someone my age; sim iterations are 3000 because I have old excel that can't handle much more and I choose not to code this yet;  an entirely arbitrary discount rate of .04 is used; there are no other spend trends or shocks, and there are two steps down in spending: -20% at age 69 and -20% at age 86. Why? Well, because that's sorta what I do and I wanted to throw it in there. Where I use a probability weight I use a conditional survival probability derived from the 2012 SOA IAM table using a G2 extension for a 59 year old male.  The technique is a rudimentary spreadsheet sim just to shake out the idea.  No CRRA utility math is used but could be I presume. Discount rates could be nudged around through hyperbolic coefficients, logical analytical methods, randomizing, etc. but weren't.

The Results

1.  Discounted cash flow over a conservative fixed period:

to age 85, NPV =  2,102,004

to age 95, NPV =  2,515,221

2. Discounted cash flow over an infinite or really long time frame weighted by conditional survival probability:

            NPV = 1,977,961

3. Distribution of many spend liabilities with inflation and longevity varied via stochastic processes:

            Sum of the mean PV values = 1,957,800

            Sum of Median PV values =  2,075,502
            Median of the 3k NPVs = 2,103,181
            At the 95th percentile of NPVs = 2,653,092

            Max = 3,141,567

            Min = 197,115

Some Charts

The distribution for #3 looks like this:

The discounted cash flows (and the EV path of the sim) at T0 look like this:

Conclusions

Creating the distribution was fun (or what passes for fun for a retirement finance blogger). Note that by common sense we are more interested in the right tail where a higher spend liability might come into play through superannuation or bad luck with inflation.  The left side is interesting only in that it reminds us that if we happen to die tomorrow our liability will have been tiny…except that we won't be able to enjoy the results of that outcome. 

Ok, this may have been fun, but I haven't convinced myself that all this work adds a whole lot especially when it comes to #3: my pseudo-sim of spending. Based on the second chart it looks like method #2 and #3 are playing the same game and #2 is easier and faster to calculate so #2 wins that fight.  On the other hand it is taking us further and further from the original conservative estimate in #1.  For conservative estimates, the original to age 95 estimate of #1 and the 95th percentile of the #3 distribution are pretty close (~5%) so just by virtue of simplicity, #1 wins that fight although having a cumulative distribution does give me some flexibility in research and analysis I might not have otherwise had. Maybe if I knew how to use confidence intervals...?   

So what's my spend liability in this faked-up game?  Well, it's whatever I want it to be.  In this case the original estimate of ~2.5M was probably ok and conservative; it's not all that different than the more complex effort (at the 95th%tile) when push comes to shove. But then again that conservatism crimps current spending in that tough trade-off between future and present where maybe it doesn't have to (or shrinks my risk capacity or decreases my distance to a "free boundary"). That's no fun. So maybe my liability should be closer to $2M and I keep an eye on things. I like that idea. In any case, my liability is probably somewhere between 2 and 2.5 million here depending on my risk aversion and since that changes everyday, who knows? And anyway all my assumptions will change tomorrow so I'll have to do it all again. Which I will.  

------------------------

[1] I totally realize that this inflation assumption is neither realistic nor does it look like any particular historical process I know.  There is also no serial autocorrelation which one might expect. This was just a spreadsheet simulation prototype so I just wanted some inflation and I wanted it to move around a little.  We can fix this for realism later.