Act-fx : the Actuarial Toolkit for Excel
Functions are provided for basic compound interest calculations, including payment conversion and annuities-certain. Parameters allow for interest, increase rates and payment frequency,
All the SAPS ‘S1’ and 'S2' series mortality tables are built-in(1), together with the '08' series tables and selected tables from the ‘00’ and ‘92’ series and earlier tables. These are coded into the add-in modules, which means that they are secure and do not appear on Excel worksheets unless required.
Functions are provided for: mortality rate, survival probability, expectation of life and expected age at death
Improvement projection parameters are included for:
- year of birth, calendar year and year of use;
- cohort projections: short, medium and long;
- proportion of improvement rate and minimum rate for cohort projections;
- CMI Mortality Projections Models core projections (all models from 2009 to 2017)(1) .
A range of single and joint-life functions is provided for the calculation of annuities, assurances and endowments. Annuity functions include last-survivor annuities and provide for a variety of conditions of payment including: in-advance or arrear, guaranteed period, frequency of payment, increases (including accurate calculation of annual increases with differing frequencies of payment).
All built-in mortality tables and improvement projection parameters are available to these functions.
Excel provides only a limited number of standard date manipulation functions.
Act-fx considerably expands these with functions to assist in benefit calculation and valuation formulae. These include ages last, next and nearest birthday; date differences to complete weeks, months, years and tax years; anniversary dates; date comparisons; and tax year calculations.
Functions are provided for benefit revaluation, including limited, fixed and s148 GMP revaluation and statutory non-GMP revaluation.
Market, statistical and statutory
These functions are grouped under the following headings:
- HMRC - earnings cap;
- Market yields - FTSE-Actuaries fixed and index-linked yields including annualised calculation (values supplied by user in a table);
- PPF - pre and post-retirement yields for a given date according to PPF assumptions guidance A3, A4, A5 and A6;
- RPI - index values for specified month and year together with increase ratios and rates for specified periods.
- State Scheme - lower and upper earnings limits.
(1) Standard mortality tables and projection methods provided under licence from CMI Limited and available only to CMI subscribers for publications after 1 March 2013.