Estimating Rate of Return on Pension vs. Lump Sum Option

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
Topic Author
tealeaves
Posts: 215
Joined: Sun Apr 29, 2018 1:21 pm

Estimating Rate of Return on Pension vs. Lump Sum Option

Post by tealeaves »

Assuming the following:
- Lump sum option: $750K
- Life Annuity (pension) option: $40K annually
- Time Horizon (lifespan going forward): 30 years

I put the following formula in a spreadsheet to estimate the implied comparative rate of return for the pension option (comes out to 3.35%), which I could use as one variable in order to make the lump sum vs. pension decision (i.e., I would want to be fairly confident that I could exceed that rate of the return on the lump sum with low risk over the next 30 years in order to take that option). Is this a roughly right calculation, or am I missing something obvious?

IRR (B2:B32, .1)

(Years in column A, Payments in column B)

Year Payment
0 -750,000 (cell B2)
1 40,000
2 40,000
...
30 40,000 (cell B32)
User avatar
#Cruncher
Posts: 3057
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Estimating Rate of Return on Pension vs. Lump Sum Option

Post by #Cruncher »

Yes, your use of the Excel IRR function is correct, tealeaves. With such a regular cash flow, however, the same 3.35% result can be reached more compactly with the RATE function:
3.35% = RATE(30, 40, -750, 0, 0)

This is even more convenient with monthly payments since that's usually how pensions are paid.
3.47% = (1 + RATE(30 * 12, 40 / 12, -750, 0, 0)) ^ 12 - 1

With the IRR function, you'd need about 360 spreadsheet rows!
User avatar
Stinky
Posts: 5567
Joined: Mon Jun 12, 2017 11:38 am
Location: Sweet Home Alabama

Re: Estimating Rate of Return on Pension vs. Lump Sum Option

Post by Stinky »

Your math may be correct.

But a "definitive" calculation like that, with a fixed input for the number of years, doesn't capture the large set of inputs necessary to decide on the "lump sum vs. pension" calculation.

If you haven't seen it already, I encourage you to read the wiki article on lump sum vs. pension.

https://www.bogleheads.org/wiki/Lump_sum_vs_pension
It's a GREAT day to be alive - Travis Tritt
Topic Author
tealeaves
Posts: 215
Joined: Sun Apr 29, 2018 1:21 pm

Re: Estimating Rate of Return on Pension vs. Lump Sum Option

Post by tealeaves »

#Cruncher wrote: Sun Oct 18, 2020 4:35 am Yes, your use of the Excel IRR function is correct, tealeaves. With such a regular cash flow, however, the same 3.35% result can be reached more compactly with the RATE function:
3.35% = RATE(30, 40, -750, 0, 0)

This is even more convenient with monthly payments since that's usually how pensions are paid.
3.47% = (1 + RATE(30 * 12, 40 / 12, -750, 0, 0)) ^ 12 - 1

With the IRR function, you'd need about 360 spreadsheet rows!


Thanks very helpful!
Topic Author
tealeaves
Posts: 215
Joined: Sun Apr 29, 2018 1:21 pm

Re: Estimating Rate of Return on Pension vs. Lump Sum Option

Post by tealeaves »

Stinky wrote: Sun Oct 18, 2020 6:09 am Your math may be correct.

But a "definitive" calculation like that, with a fixed input for the number of years, doesn't capture the large set of inputs necessary to decide on the "lump sum vs. pension" calculation.

If you haven't seen it already, I encourage you to read the wiki article on lump sum vs. pension.

https://www.bogleheads.org/wiki/Lump_sum_vs_pension
Yes understood that this is but one variable of many to consider in a complex decision process.
jebmke
Posts: 11428
Joined: Thu Apr 05, 2007 2:44 pm
Location: Delmarva Peninsula

Re: Estimating Rate of Return on Pension vs. Lump Sum Option

Post by jebmke »

Isn’t this calculation only valid for a 30-year certain payment option? It doesn’t account for mortality.
When you discover that you are riding a dead horse, the best strategy is to dismount.
Topic Author
tealeaves
Posts: 215
Joined: Sun Apr 29, 2018 1:21 pm

Re: Estimating Rate of Return on Pension vs. Lump Sum Option

Post by tealeaves »

jebmke wrote: Sun Oct 18, 2020 7:00 am Isn’t this calculation only valid for a 30-year certain payment option? It doesn’t account for mortality.
In this case the mortality time frame (in this case 30 years until "2nd to die" for a lump sum vs. joint life pension decision) was just an assumption. Certainly that assumption has significant bearing on the result.
User avatar
Watty
Posts: 20701
Joined: Wed Oct 10, 2007 3:55 pm

Re: Estimating Rate of Return on Pension vs. Lump Sum Option

Post by Watty »

tealeaves wrote: Sat Oct 17, 2020 9:09 pm Is this a roughly right calculation, or am I missing something obvious?

Part of the annuity payment includes a "Mortality Credit" to compensate you for the fact that you might not live the full 30 years and your estate would not get any of the remaining payments.

https://www.kitces.com/blog/understandi ... nt-income/
User avatar
gr7070
Posts: 1467
Joined: Fri Oct 28, 2011 10:39 am

Re: Estimating Rate of Return on Pension vs. Lump Sum Option

Post by gr7070 »

That Wiki really needs to be updated that...

Some pensions, often government provided, may have added benefits associated with them in addition to the lifetime payment; such as subsidized health insurance.

Opting for the lump sum would likely remove that health insurance benefit. Some of these pensions do not guarantee the health insurance subsidy. It is dependent upon the government's financial means and may be removed in the future, often through legislation.

OP know if you have other benefits to this pension.
Post Reply