Re: [lmi] Spreadsheet tools for validation

[Greg Chicares]

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
> program can load them.
> 
> '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

I've added a new spreadsheet derived from the one above:
  http://download.savannah.gnu.org/releases/lmi/validate_2001cso.xls
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:
   http://www.actuary.org/content/cso-task-force-report
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.