Compound interest
Functions are provided for basic compound interest calculations, including payment conversion and annuities-certain. Parameters allow for interest, increase rates and payment frequency,
Mortality
All the CMI SAPS mortality tables (‘S1’,’S2′ and ‘S3′ series) are built-in(1), together with the ’08’ and ’16’ 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;
- CMI Mortality Projections Models core projections (all models from 2009 to 2022)(1)
- historical cohort projections: short, medium and long.
Life financial
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.
Cash Flows and Discounted Cash Flows
Functions are provided to give the projected cash flow in future years for a range of annuity and endowment functions. A corresponding set of functions gives the discounted value of cash flows on a fixed interest or a yield curve basis.
Date
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.
Revaluation
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 from A3 to A11;
- RPI and CPI – 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.