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): http://www.sulprobil.com/mortality_annuities_en/
'Source (DE): http://www.bplumhoff.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 sumproduct table and an NPV formula:

sbAnnuity_NPV_Offset

sbAnnuity_NPV_Index

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