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.