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:

The names are defined as follows:

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:

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:

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.

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:

Download

Please read my Disclaimer.

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