Get documents about "Copy of CA2 SampleProjectL3 spreadsheet
Document Sample
DOCUMENTATION SHEET
Version 1
Date Created 16 Apr 2007
Written By CA2 Tutor
Purpose Answer to CA2 Project L3:-
Calculation of pure net premiums for a 10 year term assurance, whole life contract and a possible
option benefit to take out further whole life cover without evidence of health
Results/ See the audit trail in the embedded Word file below:-
Method
Document
Data The only data is values of lx for AM92 ultimate. This is stored on the DATA tab
The various parameters are recorded in named ranges on the PARAMS tab
History
Version 1
MORTALITY TABLE DATA
This sheet has values of lx for AM92 ultimate in column B as data.
These were provided in a separate file.
Values of lx for other tables may be pasted in to column B as necessary.
If values of qx are given these should be placed in a different column and values of lx
calculated in column B.
AM92 ult
Age lx
50 9712.0728
51 9687.7149
52 9660.5021
53 9630.0522
54 9595.9715
55 9557.8179
56 9515.1040
57 9467.2906
58 9413.8004
59 9354.0040
60 9287.2164
61 9212.7143
62 9129.7170
63 9037.3973
64 8934.8771
65 8821.2612
66 8695.6199
67 8557.0118
68 8404.4916
69 8237.1329
70 8054.0544
71 7854.4508
72 7637.6208
73 7403.0084
74 7150.2401
75 6879.1673
76 6589.9258
77 6282.9803
78 5959.1680
79 5619.7577
80 5266.4604
81 4901.4789
82 4527.4960
83 4147.6708
84 3765.5998
85 3385.2479
86 3010.8395
87 2646.7416
88 2297.2976
89 1966.6499
90 1658.5545
91 1376.1906
92 1121.9889
93 897.5025
94 703.3242
95 539.0643
96 403.4023
97 294.2061
98 208.7060
99 143.7120
100 95.8476
101 61.7733
102 38.3796
103 22.9284
104 13.1359
105 7.1968
106 3.7596
107 1.8669
108 0.8784
109 0.3903
110 0.1632
111 0.0640
112 0.0234
113 0.0080
114 0.0025
115 0.0007
116 0.0002
117 0.0000
PARAMETER SHEET
This sheet records the basic parameters for the workbook.
Each parameter is a named range.
Interest rate 4.25%
Loading 1.00
Mortality Rating 2.00
Option Take-up 30%
Sum Assureds:
Basic 10,000
Additional 10,000
BASE MORTALITY CALCULATIONS
This sheet calculates the net premiums assuming a mortality loading equal to the named range Loading on the
PARAMS tab.
The interest rate assumption is set on the PARAMS tab.
Whole of Life Contract 10 Year Term Assurance Contract
Base Base Loaded Net Premium Loaded Loaded Net Premium
Age lx qx q^x aduex Ax Px lx 10px adue(x:10) A(x:10) P(x:10)
50 9712.073 0.002508 0.002508 16.9435 0.3093 0.018252 9712.073 0.9563 8.2316 0.03373 0.004098
51 9687.715 0.002809 0.002809 16.6629 0.3207 0.019246 9687.715 0.9510 8.2170 0.03781 0.004602
52 9660.502 0.003152 0.003152 16.3745 0.3325 0.020303 9660.502 0.9451 8.2006 0.04238 0.005168
53 9630.052 0.003539 0.003539 16.0786 0.3445 0.021427 9630.052 0.9385 8.1823 0.04748 0.005803
54 9595.972 0.003976 0.003976 15.7753 0.3569 0.022623 9595.972 0.9311 8.1616 0.05317 0.006515
55 9557.818 0.004469 0.004469 15.4648 0.3695 0.023896 9557.818 0.9229 8.1386 0.05950 0.007311
56 9515.104 0.005025 0.005025 15.1472 0.3825 0.025251 9515.104 0.9139 8.1128 0.06653 0.008200
57 9467.291 0.005650 0.005650 14.8229 0.3957 0.026696 9467.291 0.9039 8.0841 0.07431 0.009192
58 9413.800 0.006352 0.006352 14.4923 0.4092 0.028235 9413.800 0.8928 8.0521 0.08291 0.010297
59 9354.004 0.007140 0.007140 14.1556 0.4229 0.029876 9354.004 0.8806 8.0166 0.09240 0.011526
60 9287.216 0.008022 0.008022 13.8134 0.4369 0.031626 9287.216 0.8672 7.9772 0.10283 0.012890
61 9212.714 0.009009 0.009009 13.4660 0.4510 0.033494 9212.714 0.8526 7.9336 0.11427 0.014403
62 9129.717 0.010112 0.010112 13.1139 0.4654 0.035487 9129.717 0.8366 7.8856 0.12678 0.016077
63 9037.397 0.011344 0.011344 12.7578 0.4799 0.037616 9037.397 0.8192 7.8326 0.14042 0.017928
64 8934.877 0.012716 0.012716 12.3981 0.4946 0.039890 8934.877 0.8003 7.7744 0.15526 0.019970
65 8821.261 0.014243 0.014243 12.0356 0.5093 0.042320 8821.261 0.7798 7.7106 0.17133 0.022220
66 8695.620 0.015940 0.015940 11.6708 0.5242 0.044916 8695.620 0.7578 7.6408 0.18868 0.024693
67 8557.012 0.017824 0.017824 11.3045 0.5391 0.047693 8557.012 0.7342 7.5647 0.20734 0.027409
68 8404.492 0.019913 0.019913 10.9374 0.5541 0.050662 8404.492 0.7090 7.4819 0.22734 0.030385
69 8237.133 0.022226 0.022226 10.5702 0.5691 0.053838 8237.133 0.6822 7.3921 0.24868 0.033641
70 8054.054 0.024783 0.024783 10.2038 0.5840 0.057236 8054.054 0.6539 7.2950 0.27134 0.037195
71 7854.451 0.027606 0.027606 9.8388 0.5989 0.060871 7854.451 0.6240 7.1903 0.29529 0.041068
72 7637.621 0.030718 0.030718 9.4760 0.6137 0.064762 7637.621 0.5928 7.0779 0.32049 0.045280
73 7403.008 0.034144 0.034144 9.1163 0.6284 0.068927 7403.008 0.5603 6.9575 0.34684 0.049852
74 7150.240 0.037911 0.037911 8.7603 0.6429 0.073384 7150.240 0.5266 6.8291 0.37426 0.054804
75 6879.167 0.042046 0.042046 8.4089 0.6572 0.078154 6879.167 0.4921 6.6926 0.40260 0.060156
76 6589.926 0.046578 0.046578 8.0628 0.6713 0.083259 6589.926 0.4569 6.5482 0.43171 0.065928
77 6282.980 0.051538 0.051538 7.7227 0.6852 0.088721 6282.980 0.4213 6.3961 0.46141 0.072140
78 5959.168 0.056956 0.056956 7.3892 0.6988 0.094565 5959.168 0.3855 6.2365 0.49150 0.078810
79 5619.758 0.062867 0.062867 7.0631 0.7121 0.100814 5619.758 0.3500 6.0698 0.52174 0.085957
80 5266.460 0.069303 0.069303 6.7448 0.7250 0.107496 5266.460 0.3149 5.8966 0.55190 0.093597
81 4901.479 0.076300 0.076300 6.4349 0.7377 0.114636 4901.479 0.2808 5.7175 0.58173 0.101747
82 4527.496 0.083893 0.083893 6.1339 0.7499 0.122262 4527.496 0.2478 5.5332 0.61098 0.110421
83 4147.671 0.092117 0.092117 5.8422 0.7618 0.130402 4147.671 0.2164 5.3446 0.63940 0.119633
84 3765.600 0.101007 0.101007 5.5602 0.7733 0.139084 3765.600 0.1868 5.1527 0.66675 0.129398
85 3385.248 0.110600 0.110600 5.2881 0.7844 0.148337 3385.248 0.1592 4.9585 0.69283 0.139727
86 3010.840 0.120929 0.120929 5.0262 0.7951 0.158189 3010.840 0.1340 4.7630 0.71746 0.150632
87 2646.742 0.132028 0.132028 4.7748 0.8053 0.168667 2646.742 0.1112 4.5674 0.74049 0.162126
88 2297.298 0.143929 0.143929 4.5338 0.8152 0.179800 2297.298 0.0908 4.3727 0.76182 0.174220
89 1966.650 0.156660 0.156660 4.3033 0.8246 0.191611 1966.650 0.0731 4.1802 0.78139 0.186927
90 1658.555 0.170247 0.170247 4.0834 0.8335 0.204125 1658.555 0.0578 3.9908 0.79919 0.200260
91 1376.191 0.184714 0.184714 3.8740 0.8421 0.217362 1376.191
92 1121.989 0.200079 0.200079 3.6750 0.8502 0.231342 1121.989
93 897.503 0.216354 0.216354 3.4862 0.8579 0.246079 897.503 CHECKS: N.B. Set parameter 'Interest' to 4.0%
94 703.324 0.233548 0.233548 3.3074 0.8652 0.261583 703.324 adue(50:10) 8.232
95 539.064 0.251662 0.251662 3.1385 0.8721 0.277859 539.064 adue(55:10) 8.139
96 403.402 0.270688 0.270688 2.9791 0.8786 0.294907 403.402 A*50:10 0.66442
97 294.206 0.290613 0.290613 2.8290 0.8847 0.312720 294.206 A*55:10 0.668212
98 208.706 0.311414 0.311414 2.6878 0.8904 0.331285 208.706 where A* denotes endowment assurance functions
99 143.712 0.333058 0.333058 2.5553 0.8958 0.350581 143.712
100 95.848 0.355505 0.355505 2.4310 0.9009 0.370578 95.848
101 61.773 0.378702 0.378702 2.3148 0.9056 0.391238 61.773
102 38.380 0.402589 0.402589 2.2061 0.9101 0.412514 38.380
103 22.928 0.427090 0.427090 2.1047 0.9142 0.434350 22.928
104 13.136 0.452127 0.452127 2.0103 0.9180 0.456683 13.136
105 7.197 0.477601 0.477601 1.9223 0.9216 0.479437 7.197
106 3.760 0.503431 0.503431 1.8406 0.9250 0.502537 3.760
107 1.867 0.529487 0.529487 1.7647 0.9281 0.525890 1.867
108 0.878 0.555669 0.555669 1.6944 0.9309 0.549411 0.878
109 0.390 0.581860 0.581860 1.6292 0.9336 0.573022 0.390
110 0.163 0.607843 0.607843 1.5688 0.9360 0.596674 0.163
111 0.064 0.634375 0.634375 1.5120 0.9384 0.620604 0.064
112 0.023 0.658120 0.658120 1.4599 0.9405 0.644218 0.023
113 0.008 0.687500 0.687500 1.4023 0.9428 0.672328 0.008
114 0.003 0.720000 0.720000 1.3422 0.9453 0.704281 0.003
115 0.001 0.714286 0.714286 1.2741 0.9481 0.744121 0.001
116 0.000 1.000000 1.000000 1.0000 0.9592 0.959233 0.000
'Interest' to 4.0%
surance functions
RATED MORTALITY CALCULATIONS
This sheet calculates the net premiums assuming a mortality loading equal to the named range
Rating on the PARAMS tab.
The interest rate assumption is set on the PARAMS tab
Calculation of Option Premium:-
Whole of Life Contract 10 Year Term Assurance Contract
Dx+10 Value of Value of Value of Value of Value of Basic Check
Base Base Rated Rated Rated Net Premium Rated Rated Rated Rated Net Premium Base WL over Basic Basic Extra Extra Option Option Policy ratio
Age lx qx q^x aduex^ Ax^ Px*^ lx 10px adue^(x:10) A^(x:10) P^(x:10) Premium Dx Premium Benefits Premium Benefits Premiums Premium Premium opt:basic
50 9712.073 0.002508 0.005016 15.0023 0.3884 0.0259 9712.073 0.9142 8.1142 0.06624 0.008164 0.0183 0.630686 3010.1 3276.9 683.6 1010.9 594.2 72.2 182.5 40%
51 9687.715 0.002809 0.005618 14.6709 0.4019 0.0274 9663.357 0.9041 8.0857 0.07409 0.009163 0.0192 0.627199 3119.3 3392.8 696.0 1034.4 611.9 74.5 192.5 39%
52 9660.502 0.003152 0.006304 14.3325 0.4157 0.0290 9609.068 0.8928 8.0537 0.08283 0.010284 0.0203 0.6233 3231.5 3511.4 707.6 1057.1 629.4 76.7 203.0 38%
53 9630.052 0.003539 0.007078 13.9873 0.4298 0.0307 9548.493 0.8803 8.0179 0.09254 0.011541 0.0214 0.618948 3346.6 3632.6 718.1 1078.6 646.4 79.0 214.3 37%
54 9595.972 0.003976 0.007952 13.6358 0.4441 0.0326 9480.909 0.8665 7.9779 0.10329 0.012948 0.0226 0.6141 3464.7 3756.4 727.6 1098.7 662.8 81.2 226.2 36%
55 9557.818 0.004469 0.008938 13.2784 0.4587 0.0345 9405.516 0.8512 7.9333 0.11518 0.014519 0.0239 0.608711 3585.7 3882.6 735.8 1117.3 678.5 83.4 239.0 35%
56 9515.104 0.005025 0.010050 12.9156 0.4735 0.0367 9321.450 0.8344 7.8837 0.12828 0.016271 0.0253 0.602735 3709.5 4011.2 742.6 1134.2 693.3 85.5 252.5 34%
57 9467.291 0.005650 0.011300 12.5482 0.4884 0.0389 9227.769 0.8160 7.8287 0.14266 0.018222 0.0267 0.596123 3836.0 4141.9 747.9 1149.0 707.0 87.5 267.0 33%
58 9413.800 0.006352 0.012704 12.1766 0.5036 0.0414 9123.495 0.7959 7.7677 0.15840 0.020391 0.0282 0.588824 3965.3 4274.7 751.6 1161.6 719.4 89.3 282.3 32%
59 9354.004 0.007140 0.014280 11.8015 0.5189 0.0440 9007.591 0.7740 7.7004 0.17556 0.022799 0.0299 0.580788 4097.1 4409.3 753.6 1171.8 730.5 91.1 298.8 30%
60 9287.216 0.008022 0.016044 11.4237 0.5343 0.0468 8878.962 0.7504 7.6263 0.19421 0.025465 0.0316 0.571964 4231.3 4545.7 753.6 1179.1 739.9 92.8 316.3 29%
61 9212.714 0.009009 0.018018 11.0439 0.5498 0.0498 8736.508 0.7248 7.5449 0.21439 0.028415 0.0335 0.562299 4367.9 4683.5 751.6 1183.5 747.5 94.2 334.9 28%
62 9129.717 0.010112 0.020224 10.6629 0.5653 0.0530 8579.094 0.6973 7.4557 0.23613 0.031671 0.0355 0.551747 4506.8 4822.7 747.5 1184.7 753.1 95.5 354.9 27%
63 9037.397 0.011344 0.022688 10.2815 0.5809 0.0565 8405.590 0.6680 7.3584 0.25944 0.035258 0.0376 0.540262 4647.7 4963.0 741.2 1182.4 756.5 96.6 376.2 26%
64 8934.877 0.012716 0.025432 9.9005 0.5964 0.0602 8214.884 0.6368 7.2525 0.28432 0.039203 0.0399 0.527802 4790.5 5104.1 732.6 1176.4 757.5 97.4 398.9 24%
65 8821.261 0.014243 0.028486 9.5210 0.6119 0.0643 8005.963 0.6039 7.1376 0.31072 0.043533 0.0423 0.514333 4935.1 5246.0 721.6 1166.6 755.9 98.0 423.2 23%
66 8695.620 0.015940 0.031880 9.1436 0.6272 0.0686 7777.905 0.5693 7.0134 0.33858 0.048275 0.0449 0.499827 5081.1 5388.2 708.2 1152.7 751.6 98.4 449.2 22%
67 8557.012 0.017824 0.035648 8.7692 0.6425 0.0733 7529.946 0.5333 6.8798 0.36779 0.053459 0.0477 0.484265 5228.5 5530.7 692.4 1134.6 744.4 98.4 476.9 21%
68 8404.492 0.019913 0.039826 8.3988 0.6576 0.0783 7261.518 0.4960 6.7365 0.39822 0.059114 0.0507 0.467642 5377.1 5673.1 674.1 1112.3 734.3 98.1 506.6 19%
69 8237.133 0.022226 0.044452 8.0332 0.6725 0.0837 6972.321 0.4577 6.5836 0.42970 0.065269 0.0538 0.449967 5526.4 5815.2 653.4 1085.7 721.0 97.5 538.4 18%
70 8054.054 0.024783 0.049566 7.6732 0.6872 0.0896 6662.387 0.4188 6.4210 0.46201 0.071952 0.0572 0.431264 5676.4 5956.8 630.5 1054.7 704.7 96.6 572.4 17%
71 7854.451 0.027606 0.055212 7.3196 0.7016 0.0959 6332.159 0.3796 6.2491 0.49489 0.079194 0.0609 0.411577 5826.7 6097.7 605.2 1019.5 685.2 95.3 608.7 16%
72 7637.621 0.030718 0.061436 6.9732 0.7157 0.1026 5982.548 0.3404 6.0683 0.52807 0.087022 0.0648 0.390966 5977.1 6237.5 577.9 980.2 662.6 93.6 647.6 14%
73 7403.008 0.034144 0.068288 6.6347 0.7295 0.1100 5615.004 0.3019 5.8789 0.56124 0.095466 0.0689 0.369518 6127.2 6376.0 548.7 937.0 637.1 91.6 689.3 13%
74 7150.240 0.037911 0.075822 6.3047 0.7430 0.1178 5231.567 0.2643 5.6818 0.59405 0.104552 0.0734 0.347338 6276.7 6513.1 517.8 890.2 608.8 89.1 733.8 12%
75 6879.167 0.042046 0.084092 5.9838 0.7561 0.1263 4834.899 0.2282 5.4779 0.62616 0.114308 0.0782 0.324559 6425.3 6648.4 485.5 840.2 577.8 86.3 781.5 11%
76 6589.926 0.046578 0.093156 5.6727 0.7687 0.1355 4428.323 0.1941 5.2681 0.65725 0.124760 0.0833 0.301333 6572.6 6781.7 452.0 787.5 544.6 83.2 832.6 10%
77 6282.980 0.051538 0.103076 5.3717 0.7810 0.1454 4015.798 0.1622 5.0537 0.68698 0.135935 0.0887 0.277834 6718.3 6912.9 417.8 732.5 509.4 79.6 887.2 9%
78 5959.168 0.056956 0.113912 5.0812 0.7929 0.1560 3601.866 0.1331 4.8360 0.71505 0.147860 0.0946 0.254256 6862.0 7041.7 383.0 675.9 472.6 75.8 945.6 8%
79 5619.758 0.062867 0.125734 4.8016 0.8043 0.1675 3191.570 0.1070 4.6164 0.74124 0.160566 0.1008 0.230807 7003.4 7168.0 348.2 618.3 434.7 71.6 1008.1 7%
80 5266.460 0.069303 0.138606 4.5332 0.8152 0.1798 2790.281 0.0840 4.3965 0.76535 0.174082 0.1075 0.207707 7142.0 7291.4 313.7 560.5 396.2 67.2 1075.0 6%
81 4901.479 0.076300 0.152600 4.2760 0.8257 0.1931 2403.531 0.0643 4.1777 0.78726 0.188445 0.1146
82 4527.496 0.083893 0.167786 4.0302 0.8357 0.2074 2036.752 0.0479 3.9615 0.80693 0.203695 0.1223
83 4147.671 0.092117 0.184234 3.7959 0.8452 0.2227 1695.014 0.0345 3.7493 0.82439 0.219878 0.1304
84 3765.600 0.101007 0.202014 3.5730 0.8543 0.2391 1382.735 0.0240 3.5425 0.83975 0.237048 0.1391
85 3385.248 0.110600 0.221200 3.3615 0.8630 0.2567 1103.403 0.0160 3.3423 0.85317 0.255267 0.1483
86 3010.840 0.120929 0.241858 3.1610 0.8711 0.2756 859.330 0.0102 3.1495 0.86486 0.274600 0.1582
87 2646.742 0.132028 0.264056 2.9716 0.8789 0.2958 651.494 0.0062 2.9650 0.87505 0.295123 0.1687
88 2297.298 0.143929 0.287858 2.7929 0.8861 0.3173 479.463 0.0035 2.7893 0.88397 0.316910 0.1798
89 1966.650 0.156660 0.313320 2.6245 0.8930 0.3403 341.446 0.0019 2.6228 0.89185 0.340039 0.1916
90 1658.555 0.170247 0.340494 2.4663 0.8995 0.3647 234.464 0.0009 2.4655 0.89889 0.364582 0.2041
91 1376.191 0.184714 0.369428 2.3179 0.9055 0.3907 154.631
92 1121.989 0.200079 0.400158 2.1788 0.9112 0.4182 97.506
93 897.503 0.216354 0.432708 2.0487 0.9165 0.4473 58.488
94 703.324 0.233548 0.467096 1.9272 0.9214 0.4781 33.180
95 539.064 0.251662 0.503324 1.8139 0.9261 0.5105 17.682
96 403.402 0.270688 0.541376 1.7083 0.9304 0.5446 8.782
97 294.206 0.290613 0.581226 1.6101 0.9344 0.5803 4.028
98 208.706 0.311414 0.622828 1.5189 0.9381 0.6176 1.687
99 143.712 0.333058 0.666116 1.4342 0.9415 0.6565 0.636
100 95.848 0.355505 0.711010 1.3556 0.9447 0.6969 0.212
101 61.773 0.378702 0.757405 1.2829 0.9477 0.7387 0.061
102 38.380 0.402589 0.805178 1.2155 0.9504 0.7819 0.015
103 22.928 0.427090 0.854181 1.1533 0.9530 0.8263 0.003
104 13.136 0.452127 0.904255 1.0958 0.9553 0.8718 0.000
105 7.197 0.477601 0.955202 1.0430 0.9575 0.9180 0.000
106 3.760 0.503431 1.000000 1.0000 0.9592 0.9592 0.000
107 1.867 0.529487 1.000000 1.0000 0.9592 0.9592
108 0.878 0.555669 1.000000 1.0000 0.9592 0.9592
109 0.390 0.581860 1.000000 1.0000 0.9592 0.9592
110 0.163 0.607843 1.000000 1.0000 0.9592 0.9592
111 0.064 0.634375 1.000000 1.0000 0.9592 0.9592
112 0.023 0.658120 1.000000 1.0000 0.9592 0.9592
113 0.008 0.687500 1.000000 1.0000 0.9592 0.9592
114 0.003 0.720000 1.000000 1.0000 0.9592 0.9592
115 0.001 0.714286 1.000000 1.0000 0.9592 0.9592
116 0.000 1.000000 1.000000 1.0000 0.9592 0.9592
Comparison of Whole of Life and 10 year Term Assurance net premiums
0.25
0.20
Net premium p.a. (per £1 sum assured)
0.15
0.10
Whole Life
Term
0.05
0.00
50 55 60 65 70 75 80 85
Age at Entry
Comparison of Whole of Life Premiums
Base Mortality and Rated Mortality
0.40
0.35
Net premium p.a. (per £1 sum assured)
0.30
0.25
0.20
0.15
0.10
Base Premium
Rated Premium
0.05
0.00
50 55 60 65 70 75 80 85 90
Age at Entry
BASIC PREMIUM AND OPTION PREMIUM
1200
1000
800
Annual Premium (£)
600
400
Option Premium
Basic Whole Life Premium
200
0
50 55 60 65 70 75 80
Age at Entry
