## 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
Topic Author
tealeaves
Posts: 215
Joined: Sun Apr 29, 2018 1:21 pm

### Estimating Rate of Return on Pension vs. Lump Sum Option

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)
#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

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

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

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.

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

#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

Topic Author
tealeaves
Posts: 215
Joined: Sun Apr 29, 2018 1:21 pm

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

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.

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

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

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.
Watty
Posts: 20701
Joined: Wed Oct 10, 2007 3:55 pm

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

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/
gr7070
Posts: 1467
Joined: Fri Oct 28, 2011 10:39 am

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

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.