Abstract

If you like to calculate annuities of a mortality table you have a bunch of choices:

Complex Array Formula (Worst)

Apply a complex one-cell array formula:

sbAnnuity_ArrayFormula

The names are defined as follows:

sbAnnuity_Names

This approach has also been described at a (external link!) Google site.

Simple user defined function in VBA (Better)

A better way by far is a simple VBA function:

sbAnnuity_VBA

Please read my Disclaimer.

Option Explicit
    
Function LiveAnnuityPV(lYears As Long, dInterestRate As Double, _
    rMortalityTable As Range) As Double
'Source (EN): https://www.sulprobil.de/mortality_annuities_en/
'Source (DE): https://www.berndplumhoff.de/mortality_annuities_de/
'(C) (P) by Bernd Plumhoff 22-Mar-2014 PB V0.1
Dim j As Long
Dim dSum As Double, dProd As Double, dPV As Double

dProd = 1#
dPV = 1#
For j = 1 To rMortalityTable.Count - lYears
    dPV = dPV / (1# + dInterestRate)
    dProd = dProd * (1# - rMortalityTable(j + lYears))
    dSum = dSum + dPV * dProd
Next j

LiveAnnuityPV = dSum

End Function

Pre-calculated Table plus an NPV Formula (Maybe Best)

The fastest and maybe best approach seems to be a pre-calculated table and an NPV formula:

sbAnnuity_NPV_Offset

sbAnnuity_NPV_Index

Please note: The OFFSET worksheet formula is slightly faster than the INDEX worksheet formula. On the other hand it is volatile, i.e. it is getting recalculated each time you press F9 and not just when an input value is getting updated.

sbAnnuity_Mortality_Table_plus_PreCalc

With FastExcel © you can profile your worksheet. In this case we see that the NPV approach is about 45 times faster than the suboptimal array formula and about 27 times faster than the VBA solution:

sbAnnuity_FastExcel_Profile

Download

Please read my Disclaimer.

sbAnnuity_Formula.xlsm [193 KB Excel file, open and use at your own risk]