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)

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

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

This is even more convenient with monthly payments since that's usually how pensions are paid.

**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!With the IRR function, you'd need about 360 spreadsheet rows!

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

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

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

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

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

Thanks very helpful!

Thanks very helpful!

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

Yes understood that this is but one variable of many to consider in a complex decision process.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

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

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

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.

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

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/

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

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.