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