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.... :)
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 |
Thanks very much for that. I assume it was <> rather than [] in the formula in B89?Yes - side effect of table formatting - fixed in the original, thanks.
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) or the pension is lower for the survivor (65%) but it is the same as it would have been with no survivor.
One step at a time.... :)Ok, here's the whole thing. Copy the table below, then paste into cell A82. Adjust the first six numbers to frame the question, then use Excel's Goal Seek to make the last cell equal zero by adjusting the Annual interest rate.
Lump sum | 600000 | |
Initial annual payment | 50000 | |
Annual pension COLA | 0.03 | |
Number of years at 100% payment | 17 | |
Fraction of payment to survivor | 0.5 | |
Number of years at reduced payment | 8 | |
Annual Interest rate | 0.084520529408746 | <- Let Excel Goal Seek adjust this… |
Lump sum FV | =FV($B$88,$B$85+$B$87,0,-B82) | |
Payment FV | =IF(B84<>B88,B83*((1+B84)^B85-(1+B88)^B85)/(B84-B88)*(1+B88)^B87+B83*B86*(1+B84)^B85*((1+B84)^B87-(1+B88)^B87)/(B84-B88),B83*B85*(1+B88)^B85/(1+B84)*(1+B88)^B87+B83*(1+B84)^B85*B86*B87*(1+B88)^B87/(1+B84)) | |
Difference | =B89-B90 | <- …to drive this to zero. |