Another look at a spend liability distribution

In a past post (Running some different numbers on a spend-liability estimate for a balance sheet) I was playing around with the estimation of a spend liability for a balance sheet.  For a balance sheet it seems to make sense to have a single number as an approximation for the present value of future expected spending.  This is often done with a discounted cash flow approach using a cash flow that is either run to a fixed age (and then discounted) or alternatively it can be run to infinity with probability weights at each age based on conditional survival probability (CSP) from a life table or elsewhere.  I kinda like the latter because it mimics (but will always be a different from) an annuity calc that uses the same math and it is relatively easy to come up with. On the other hand in the last post I also wanted to illustrate, to at least myself, that the estimate is maybe better visualized as a distribution when the numerator (the future forthcoming expected cash flow) is allowed to vary a bit by using some rudimentary simulation.  In that post I did the illustration by doing a simple spreadsheet sim letting inflation vary a little bit (though not very realistically). I also had a step-down function in spending at age 69 and 86 to add a custom element to the spend path that some particular person might follow.  I ran the simple sim 3000 times.  The "distribution" of results that comes out the back end is the pile of different spending liability NPVs that come from shaking up the assumptions inside each year of each sim. I then used the distribution to see how close the average was to the more deterministic DCF estimate and also to see how wide the distribution might be if I were to want to use something like the 90th or 95th percentile as a more conservative estimate for a balance sheet liability number[1].

This time I wanted to see what happens if in addition to inflation and step downs I added some random spending variance on top of all that.  My theory (and experience) is that spending can hop around for all sorts of reasons other than inflation or planned changes.  I wanted to see what happens incrementally to the distribution of the NPVs when we do this in a model.  I am using the same set of assumptions as before but notably it is $100k in spending, inflated at 3%, stepped down 20% at 69 and then 86 and discounted to PV at 4% (though subsequent to that post I'm thinking a lower rate/higher liability might make more sense). 

The probability weighted discounted cash flow for the assumptions I set up, if you'll recall, was about $1.98M using the following approach:

where CSL.a, is the baseline custom spend liability estimate, "s" is the custom cash flow weighted by a vector of CSPs (tPx) extracted from an SOA annuitant mortality table, and r is a fixed discount rate.  

To get a distribution of CSLs -- or what I'll call CSL.b -- I have to simulate.  At this point I'll note that I am not a trained math guy so I do not often know what formal or "standard" notation is supposed to be.  I provide an attempt here just so there can be a general guess as to what I am trying to do.  As in the past I invite the knowledgeable to email me and I will correct it if it makes sense.  Given that disclaimer, this is my attempt at a description of what I am trying to do:  

where

CLS.b is the distribution of NPVs 

s is a spend amount starting at 100k at t=0

x is a randomized inflation factor 3% +/- ~1% (this was in last post)[2]

y is the 20% step down at 69 and 86 (otherwise zero; this was in last post)

z is the new random spending factor with a mean of zero and sd = .10

r is the discount rate, a placeholder and illustrative only, of 4%

n is a random longevity factor/age per sim iteration using Gompertz-Makeham math

When I do this I get frequency distributions like these (lines rather than columns so it's easier to see):

CLS.b (~last post) is the old distribution of NPVs without a spending randomization factor (before)

CLS.b (this post) is the new distribution of NPVs  with the "z" term added (i.e., after)

Some simple summary stats:

CSL.a                         = ~1.98M

mean CSL.b (before) = ~1.96M

mean CSL.b (after)    = ~1.97M

So the baseline and central estimates look like they are all more or less playing the same game.  The chart itself and the broader summary stats, on the other hand, are a different story:

CSL.b before

197,714	min
3,152,645	max
1,957,564	mean
2,098,441	med
571,678	stddev
1,639,477	quart1
2,377,171	quart3
2,662,464	95th
564,023	95th-med.

CSL.b after

178,068	min
7,705,815	max
1,967,738	mean
1,871,623	med
842,830	stddev
1,441,483	quart1
2,408,553	quart3
3,446,683	95th
1,575,060	95th-med.

I will decline to make any firm conclusions at this point.  This is just me playing around to see what happens and what it looks like when I add a little more uncertainty.  I guess the knowledge that the spend liability could be any number rather than one number -- and more random variance in the terms creates wider distributions -- is useful conceptually (and common sense) but I think that after that it is a policy choice on how conservative one might want to be if one were to want to pluck liability estimates from the right side of a modeled distribution. And this is not a policy post. 

I will also note here that I think that were we to modify (randomize) the denominator of CSL.b to be something like [(1+r1)...(1+rt)] -- where r is a random variable and probably non-normally distributed and where we would have a more formal type of stochastic present value calc set up -- that means we would effectively have a geometric return factor and then that also means that I think maybe we will have gone all the way around the horn -- from deterministic analysis to what is effectively now a spreadsheet version of a Monte Carlo sim except with "asset value(s) needed to defease spending" more or less the output.  At that point we might as well do "real" simulation since we would have more flexibility doing that than what I am doing here. We could also use at that point some form of perfect withdrawal rate (PWR) math that is, in my mind, as interesting an exercise and maybe more meaningful in some ways.  On the other hand we would also be pretty far from managing a household balance sheet. We'd be at a place where I don't think the complexity I'm describing helps so much and where the focus on spending would fade into the general analytic background. This would likely be way too far away from my original purpose or I would at least be mixing purposes ... which adds to confusion that undermines the planning task. But it was fun to play with.

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

[1] I am completely ignoring here the concept of whether the liability has offsetting assets, what those assets are composed of, how much risk is on the table, and whether the assets -- or any assets of any kind for that matter -- will ever defease my spend liability over a long enough time span. This is just a liability calc "game" that I am not taking all that seriously at this point.  

[2] I realize now in a postscripted note here that this begs the question of real vs nominal discount rates.  If I did this again I might take it out and use real rates for discounting. But then again having inflation randomized in the numerator gives me some flexibility in the design.  Who knows.  Maybe a financial economist wants to chime in...