lmi
[Top][All Lists]

## Re: [lmi] Spreadsheet tools for validation

 From: Greg Chicares Subject: Re: [lmi] Spreadsheet tools for validation Date: Wed, 15 Jul 2015 17:01:14 +0000 User-agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Icedove/31.3.0

```On 2010-01-27 16:13, Greg Chicares wrote:
> Two spreadsheets, useful for validating certain life-insurance
> calculations, will appear in our "ftp" area here:
>   http://savannah.nongnu.org/files/?group=lmi
> once I've uploaded them and they've propagated to the mirrors.
> Both were saved, using openoffice.org 'calc', in 'excel 97' format
> so that people who are forced to use a proprietary spreadsheet
>
> 'validate_gpt.xls', the protoplast, performs guideline-premium
> calculations from first principles using the techniques in Eckley,
> TSA XXXIX, page 18:
>
> www.soa.org/library/research/transactions-of-society-of-actuaries/1987/january/tsa87v395.pdf

with many useful 2001 CSO tables in its "ANB" and "ALB" tabs. The
mortality rates (qx) built into its first tab are smoker-indistinct,
ANB, 80% male, ultimate rates from the authoritative source:
For example, the following values are seen at age forty-five, for a
product with a three percent load:

0.294676004028479  NSP cell AC54
0.0476087955884075 7PP cell AB54
16442.1009500619   GLP cell AK54 = cell AQ54
183048.194002217   GSP cell AL54 = cell AR54 (change cell A2 to 6% for this)

Of course, CVAT "corridor" multipliers would be the reciprocal of NSP.
These calculations use monthly commutation functions as described here:
http://www.nongnu.org/lmi/7702.html

Values for other tables can be produced by copying qx from other tabs
into column B on the first tab, and setting the appropriate statutory
interest rate in cell A2.

```