It WAS a good article but it missed my situation. My pension includes inflation (so each year it rises according to CPI). Not only that, but it includes two forms of survivor benefits - the one specified (the survivor gets the same pension, but the pension is always lower than for no survivor)

One step at a time.... :)

IIRC from other posts you know your way around a spreadsheet. Copy the table below, then paste into cell A82. Adjust the first 3 and fifth numbers to frame the question, then use Excel's What-If Analysis to make the last cell equal zero by adjusting the Annual interest rate.

This shows the article example, except with a 3%/yr increase to the annual pension payments. Instead of 4.2%, one gets 6.9% as the hurdle rate. Not surprisingly, a COLA pension is "worth more" than a flat pension.

Lump sum | 600000 |

Initial annual payment | 50000 |

Annual Inflation rate | 0.03 |

Annual Interest rate | 0.0691033288940955 |

Number of years | 17 |

type | 0 |

Lump sum FV | =FV($B$85,$B$86,0,-B82,$B$87) |

Payment FV | =B83*IF(B84<>B85,((1+B84)^B86-(1+B85)^B86)/(B84-B85),B86*(1+B85)^B86/(1+B84)) |

Difference | =B88-B89 |

Some background (see

https://en.wikipedia.org/wiki/Time_value_of_money#Formula_table for variable descriptions):

For a lump sum, F=P*(1+i)^n

For a repeating, constant, payment, F=A * ((1+i)^n-1)/i

Equating those, A * ((1+i)^n-1)/i = P*(1+i)^n

Solving for A gets the mortgage payment formula, A=P * i * (1+i)^n/((1+i)^n-1)

Because the interest rate appears by itself and in the (1+i)^n term, there is no algebraic solution for i and one must use “guess and check”. That is what the Excel RATE function does.

For an increasing repeating payment, F=D * ((1+g)^n-(1+i)^n)/(g-i)

Similarly, one can set D * ((1+g)^n-(1+i)^n)/(g-i) = P*(1+i)^n and use Excel's Goal Seek to do the numerical solution for the interest rate.