I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

Mortality Annuities

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

1. [Worst] Apply a complex one-cell array formula

sbAnnuity_ArrayFormula

The names are defined as follows:

sbAnnuity_Names

This one-cell array formula was suggested in a LinkedIn Excel forum. The approach has also been described at a Google site.

2. [Better] A better way by far is a simple VBA function

sbAnnuity_VBA
sbAnnuity_VBA_Code

3. [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

If you are interested in downloading a 200 KB Excel 2010 © sample file which contains the approaches shown here, go to my Download page, please.

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
Sulprobil   Get it done   Contact   Disclaimer   Download