Question about collecting a pension or taking a lump sum

[progree]

19. If she lives 20 more years and gets $290/month, that's a 6.148% annual average return

Last edited Sun Dec 11, 2022, 09:24 PM - Edit history (4)

on a $40,000 investment according to Excel.

=RATE(240, 290, -40000, 0.1) *12 = 6.148%

There are 240 months in 20 years.

The 0.1 is a "guess" on the interest rate (in decimal form, e.g. 0.1 = 10%). Not essential usually to have the "guess".

I've checked it out a couple of different ways -- doing it long-hand by putting all 240 months in a spreadsheet and present worthing it all at that interest rate.

Also by using the @IRR formula (internal rate of return). So all 3 ways it comes up with the same answer.

So if she thinks she can do better than 6.148% annualized average return, and expects to live exactly 20 more years, she should take the lump sum. Otherwise accept the pension. Or take the lump sum and get an annuity, insured by a long-stable insurance company that does this well or better.

In the below, Years is the number of additional years she lives. The longer she lives, the more lucrative the pension or equivalent annuity is.

Years   Rate of return
===   =========
10      -2.699% (yes, a negative rate of return because $290 for 120 months = $34,800
                           which is less than the $40,000 lump sum)
15      3.705%
20      6.148%
25      7.284%
30     7.874%

Edited to add: All of the above rates of return assume that each $290 payment is invested right away at this same rate of return. So if it sits in some very low return money market fund for months and months or longer, than these returns won't be realized. And if they are spent, then all of the above is null and void.

If we put the $290's under the mattress and let it accumulate, then after 20 years we'd have 240*$290 = $69,600

So over 20 years, the $40,000 investment accumulates to $69,600

That's just a 2.808% average annual rate of return ( 40,000 * 1.02808^20 = $69,600 )

So, from a rate of return standpoint, it matters a lot what one does with the $290's.

=================================================

How to Calculate Annuities Using Excel, By C. Taylor
https://smallbusiness.chron.com/calculate-annuities-using-excel-27850.html