Retirement Planning & Corporate Pension Risk Transfer
Pension Lump-Sum Buyout vs Annuity Breakeven Formula
Compare a Corporate Pension Lump-Sum Buyout Offer against a Guaranteed Annuity by contrasting Naive Undiscounted Payback (Lump / Pmt) with True Discounted Breakeven Longevity -LN(1 - Lump*r/Pmt)/LN(1 + r) and Present-Value Advantage.
Never evaluate a pension buyout offer using undiscounted Lump_Sum / Annual_Pension!
During corporate Pension Risk Transfer (PRT) buyout windows, retirees offered $300,000 cash instead of a $24,000/yr lifetime pension routinely divide $300,000 / $24,000 = 12.5 years and assume living past 13 years automatically makes the annuity win! That mental shortcut assumes 0.00% investment return on the $300,000 rollover IRA! At portfolio return r > 0, the lump sum earns Lump_Sum * r = $15,000 in Year 1 alone, stretching True Discounted Breakeven Longevity to T* = -LN(1 - (Lump_Sum * r) / Annual_Pmt) / LN(1 + r)—and whenever Lump_Sum * r >= Annual_Pmt, investment earnings cover 100% of the pension payout forever so the lump sum NEVER depletes!
Verify in a shared retirement-planning workbook—Lump_Sum_Offer = $300,000 (B2), Annual_Pension_Payout = $24,000/yr (B3), Conservative_Portfolio_Return r = 0.05 (5.0%, B4), and Planning_Lifespan_Horizon T = 20 years (B5): Naive Zero-Return Payback is 300,000 / 24,000 = 12.50 years (E2); 20-Year Annuity Present Value at 5% is 24,000 * ((1 - 1.05^-20) / 0.05) = $299,093.05 (E3); True Discounted Breakeven Longevity is -LN(1 - 0.625) / LN(1.05) = 20.10 years (7.6 years longer than naive payback!, E4); and Net Lump-Sum PV Advantage over 20 years is +$906.95 (E5)!
| Pension Buyout Metric ($300,000 Lump vs $24,000/yr Annuity, r=5.0%, T=20 Yrs) | Formula | Verified Output |
|---|---|---|
| Naive 0%-Return Payback (Yrs) | Lump_Sum / Annual_Pmt | 12.50 yrs (Ignores IRA Yield!) |
| Horizon Annuity Present Value | Pmt * (1 - (1 + r)^(-T)) / r | $299,093.05 |
| True Discounted Breakeven T* | -LN(1 - Lump*r/Pmt) / LN(1 + r) | 20.10 yrs ("Perpetual" @ r>=8%!) |
| Net Lump-Sum PV Advantage ($) | Lump_Sum - Annuity_PV | +$906.95 |
Discounted Pension Buyout Breakeven Horizon Formula
Validate Lump_Sum > 0, Annual_Pmt > 0, r > 0, Horizon T >= 1, and guard the perpetual-income threshold Lump_Sum * r >= Annual_Pmt before calling LN!
=LET(lm_p, B2, pm_t, B3, rt_r, B4, hr_t, B5, IF(OR(lm_p <= 0, pm_t <= 0, rt_r <= 0, hr_t < 1), "Invalid Buyout Comparison", IF(lm_p * rt_r >= pm_t, "Perpetual: Lump Sum Never Depletes", ROUND(-LN(1 - (lm_p * rt_r) / pm_t) / LN(1 + rt_r), 2))))Self-Sustaining Endowment Threshold Check (Set r = 8.0%)
Set Portfolio_Return B4 = 0.08 (8.0%): $300,000 * 8% = $24,000/yr matches the entire pension payout out of interest alone, so E4 returns "Perpetual: Lump Sum Never Depletes"!
=IF(ISTEXT(E3), E3, ROUND(B2 - E3, 2))Try the example
Use the original values or change them to test your understanding.