Post
by **cjking** » Mon Oct 23, 2017 12:44 pm

I see the requirement as being to fully utilise a given $1 million portfolio to as far as possible produce exactly 40K a year for 30 years, with nothing left at the end. This is my proposal.

1. Calculate required_return, as described in my post above, as RATE(30,4%,-1,0,0) = 1.2%.

2. At the end of year n, calculate income to be taken as "=MIN(40000,PMT(required_return,payments_left,-balance,0,1))". For example, at the end of year 1 (beginning of year 2) if our balance were now 945,432, our first income payment would be =MIN(40000,PMT(1.2191%,30,-945432,0,1)) = 37,362.

3. After deducting income each year, immediately consider whether remaining balance is sufficient to fund 40K a year income for the remaining years, using safe assets. If the risk-free rate is 0.5%, for the example above that calculation would be =PMT(risk_free_rate,payments_left,-balance,0,0) =PMT(0.5%,29,-(945432-37362),0,0)=33,716, so the answer would be no. If the answer were yes, we would sell our portfolio, invest in the risk-free asset, and be guaranteed 40K a year income for the rest of retirement.

Although it's not necessary in order to implement it, for the above strategy it is possible to calculate the amount of initial capital being devoted to produce each years income. The calculation for year n is that 40000/(100%+required_return)^year supports that year's returns . So for year 1 it is 39,518, and for year 30 it is 27,809, to give a couple of examples.

I haven't got my head around the meaning of this, I certainly didn't plan or expect it, but I notice that initial balances for each year are to the dollar identical to what would have been spent on buying the lower strike options in my previous post, if we had for all years set the lower strike as close as possible to zero. (In other words, the lower strike options would have cost 1 million, and we would have got back 175K from selling higher price options, resulting in a net options portfolio price of 825K.)

If we continue to imagine our portfolio as 30 sub-portfolios supporting each year, in this strategy we do not place a cap on the balance each sub-portfolio can reach. If there is a surplus, it is in effect donated to subsequent years. (It would be valid, if more complicated, to say each sub-portfolio should switch to safe assets when it's target of a 40K income had been achieved. But I'm not sure that would be an improvement on making the switch only when all sub-portfolios can satisfy their goals.) (Perhaps it would, in the sense that we are more likely to be dead in the later years, so it might be better to reduce shortfall risk for the early years by switching the sub-portfolio to safe assets as soon as possible, than to leave the balance at risk in the hope of generating a surplus that will reduce shortfall risk for later years.)

Last edited by

cjking on Mon Oct 23, 2017 12:59 pm, edited 3 times in total.