保险精算实验指导书(修改).doc

实验教学指导书修订计划汇总表填报学院：金融学院（公章） 填报日期： 2010年 12 月 12 日序号课程实验项目名称课程实验要求修订人修订类型1保险精算实验必修徐临新编写保险精算实验实 验 教 学 指 导 书课程编号：撰写人：徐临审核人：冯文丽河北经贸大学金融学院2010年12月12日 前 言一、实验总体目标通过本实验课的训练，可以使学生掌握excel的基本知识和使用技巧，使学生能够利用excel这一强大的计算工具处理日常遇到的现实问题。通过运用excel工具对基本的保险精算问题的建模和计算，使学生进一步理解在保险精算学等课程中学到的相关知识，通过实验训练将所学知识与实际问题结合起来。课内实验是锻炼学生分析和解决实际问题能力的重要环节。本实验课利用excel这一工具，学生通过对实际数据的计算和处理，使学生能够进一步将所学知识与实践相结合，提高学生的实际动手能力。二、适用专业年级保险学、金融工程，三、四年级三、先修课程保险学、概率论与数理统计四、实验项目及课时分配实验项目实验要求实验类型每组人数实验学时实验一确定年金必修验证性22实验二生存分布与生命表必修演示性22实验三基本死亡保险的趸缴纯保费必修验证性21实验四生存与两全保险的趸缴纯保费必修综合性21实验五递增型与递减型人寿保险的趸缴纯保费必修综合性21实验六传统寿险的毛保费必修设计性21实验七责任准备金的计算必修设计性21实验八典型保险产品的净保费和毛保费必修验证性21五、实验环境安装EXCEL2003以上版本，安装分析工具库六、实验总体要求根据实验指导书提示的实验步骤和建模方法，学生能够独立完成实验，实验步骤完整，实验结果正确，并能够根据要求作出相应的图表。通过本课程实验，学生能够基本掌握EXCEL函数在保险精算中的应用，能够运用EXCEL解决大部分保险精算建模问题，初步具备运用EXCEL建模方法解决实际保险问题的能力。七、本课程实验的重点、难点及教学方法建议1. 本实验课程需要与专业理论课程的学习紧密地结合起来。需要学生在熟悉了保险精算理论与Excel原理的背景下，才能将二者很好地结合起来。因此，本实验课程需要与理论教学同步进行，或者作为其后继课程开设。2.本实验课程主要运用Excel来解决精算中有关计算问题。因此在实验教师的引导下，学生在掌握了精算基本原理的同时，能根据具体的实验项目利用Excel而不必使用专门的精算软件，进行自主设计、自主实验，并最终达到自主创新的目标。3.实验的课程重视与实践相结合，如实验八就要求学生在课下寻找选择保险市场上典型的产品类型，自己进行费率测算,然后与实际结果进行比较。 实验一：确定年金一、实验目的1掌握EXCEL建模的基本操作和技巧2利用EXCEL计算有关年金的各种计算二、实验内容1利用EXCEL计算利息的各种度量值2利用EXCEL计算贴现的各种度量值3利用EXCE计算各种类型年金现值与终值，或在已知年金现值或终值的情形下求利率。三、实验仪器设备和材料清单EXCEL2003以上版本，安装分析工具库。四、实验要求1正确输入公式求出单利复利的积累值，绘出图形对积累函数进行比较。2正确输入公式求出积累值和贴现值，并对积累函数和贴现函数进行比较。3正确输入公式求出类型年金现值与终值，或在已知年金现值或终值的情形下求利率五、实验过程1.单利与复利积累函数的比较 2.给定利率求累计函数和贴现函数，绘图并对二者进行比较3. 计算各种类型年金现值与终值。4. 计算各种类型年金现值与终值，或在已知年金现值或终值的情形下求利率。六、实验报告要求1详细写出实验步骤2绘出图形对单利复利积累函数进行比较。3绘出图形对积累函数和贴现函数进行比较。七、思考题1、基本年金的公式主要有哪些？2、不同的时期选择，单利复利哪个会产生较大的积累值？八、注意事项48实验二：生存分布与生命表一、实验目的通过本次实验使学生学会如何运用Excel利用生命表基础函数计算各种死亡、生存概率。二、实验内容1999 年颁布的人寿保险精算规定中规定，保险公司在厘定保险费时，预定死亡率应当采用中国人寿保险业经验生命表(19901993)所提供的数据。根据保险责任的不同，保险公司应当按照下表所列经验生命表的适用范围，选择使用相应的经验生命表。CL1 (1990l993) 适应非年金保险男表，CL2 (1990l993) 适应非年金保险女表，CL3 (19901993) 适应非年金保险混合表，CL4 (1990l993) 适应年金保险男表，CL5 (1990l993) 适应年金保险女表，CL6 (1990l993) 适应年金保险混合表。2005 年12 月22 日，中国保监会颁布了中国第二张寿险业生命表中国人寿保险业经验生命表（2000-2003），简称：CL(2000-2003)。其中，非养老金业务表两张，养老金业务表两张，分别是：非养老金业务男表，简称CL1(2000-2003)；非养老金业务女表，简称CL2(2000-2003)；养老金业务男表，简称CL3(2000-2003)；养老金业务女表，简称CL4(2000-2003)。实验内容一：中国人寿保险业经验生命表（19901993）（男女混合）的输入；利用中国人寿保险业经验生命表（19901993）（男女混合）计算整数年龄各种死亡、生存概率；利用中国人寿保险业经验生命表（19901993）（男女混合）计算各年龄内各种死亡、生存概率。实验内容二：中国第二张寿险业生命表中国人寿保险业经验生命表（2000-2003）输入。三、实验仪器设备和材料清单EXCEL2003以上版本，安装分析工具库。四、实验要求利用生命表基础函数计算各种死亡、生存概率。五、实验过程1在Excel输入中国人寿保险业经验生命表（19901993）（男女混合）。 2利用生命表基础函数计算各整数年龄存活人的各生存概率、死亡概率、等。如计算x岁人在未来5年内死亡的概率，其公式为：，0岁人在未来5年内死亡的概率在Excel中F2单元格输入“=（C2-C7）/C2”，然后按回车键得到其结果；拖动整F整列得到所有整数年龄存活人在未来5年内死亡的概率，其结果如下图F列所示：3在死亡均匀分布假设、死力常数假设及巴尔杜奇假设下利用生命表函数计算各年龄内的生存概率、死亡概率、等。如在均匀分布假设下计算x+0.2岁人在未来0.5年内死亡的概率，其公式为：。0.2岁人在未来0.5年内死亡的概率在Excel中F2单元格输入“=0.5*B2/(1-0.2*B2)”，然后按回车键得其结果；拖动整F整列得到所有x+0.2岁存活人在未来0.5年内死亡的概率，其结果如下图F列所示：4全体同学分成五组分别在Excel输入中国第二张寿险业生命表中国人寿保险业经验生命表（2000-2003）。（1）中国人寿保险业经验生命表（20002003）年龄X非养老金业务表养老金业务表男(CL1)qx女(CL2)男(CL3)女(CL4)0.000000 0.000722 0.000661 0.000627 0.000575 1.000000 0.000603 0.000536 0.000525 0.000466 2.000000 0.000499 0.000424 0.000434 0.000369 3.000000 0.000416 0.000333 0.000362 0.000290 4.000000 0.000358 0.000267 0.000311 0.000232 5.000000 0.000323 0.000224 0.000281 0.000195 6.000000 0.000309 0.000201 0.000269 0.000175 7.000000 0.000308 0.000189 0.000268 0.000164 8.000000 0.000311 0.000181 0.000270 0.000158 9.000000 0.000312 0.000175 0.000271 0.000152 10.000000 0.000312 0.000169 0.000272 0.000147 11.000000 0.000312 0.000165 0.000271 0.000143 12.000000 0.000313 0.000165 0.000272 0.000143 13.000000 0.000320 0.000169 0.000278 0.000147 14.000000 0.000336 0.000179 0.000292 0.000156 15.000000 0.000364 0.000192 0.000316 0.000167 16.000000 0.000404 0.000208 0.000351 0.000181 17.000000 0.000455 0.000226 0.000396 0.000196 18.000000 0.000513 0.000245 0.000446 0.000213 19.000000 0.000572 0.000264 0.000497 0.000230 20.000000 0.000621 0.000283 0.000540 0.000246 21.000000 0.000661 0.000300 0.000575 0.000261 22.000000 0.000692 0.000315 0.000601 0.000274 23.000000 0.000716 0.000328 0.000623 0.000285 24.000000 0.000738 0.000338 0.000643 0.000293 25.000000 0.000759 0.000347 0.000660 0.000301 26.000000 0.000779 0.000355 0.000676 0.000308 27.000000 0.000795 0.000362 0.000693 0.000316 28.000000 0.000815 0.000372 0.000712 0.000325 29.000000 0.000842 0.000386 0.000734 0.000337 30.000000 0.000881 0.000406 0.000759 0.000351 31.000000 0.000932 0.000432 0.000788 0.000366 32.000000 0.000994 0.000465 0.000820 0.000384 33.000000 0.001055 0.000496 0.000855 0.000402 34.000000 0.001121 0.000528 0.000893 0.000421 35.000000 0.001194 0.000563 0.000936 0.000441 36.000000 0.001275 0.000601 0.000985 0.000464 37.000000 0.001367 0.000646 0.001043 0.000493 38.000000 0.001472 0.000699 0.001111 0.000528 39.000000 0.001589 0.000761 0.001189 0.000569 40.000000 0.001715 0.000828 0.001275 0.000615 41.000000 0.001845 0.000897 0.001366 0.000664 42.000000 0.001978 0.000966 0.001461 0.000714 43.000000 0.002113 0.001033 0.001560 0.000763 44.000000 0.002255 0.001103 0.001665 0.000815 45.000000 0.002413 0.001181 0.001783 0.000873 46.000000 0.002595 0.001274 0.001918 0.000942 47.000000 0.002805 0.001389 0.002055 0.001014 48.000000 0.003042 0.001527 0.002238 0.001123 49.000000 0.003299 0.001690 0.002446 0.001251 50.000000 0.003570 0.001873 0.002666 0.001393 51.000000 0.003847 0.002074 0.002880 0.001548 52.000000 0.004132 0.002295 0.003085 0.001714 53.000000 0.004434 0.002546 0.003300 0.001893 54.000000 0.004778 0.002836 0.003545 0.002093 55.000000 0.005203 0.003178 0.003838 0.002318 56.000000 0.005744 0.003577 0.004207 0.002607 57.000000 0.006427 0.004036 0.004676 0.002979 58.000000 0.007260 0.004556 0.005275 0.003410 59.000000 0.008229 0.005133 0.006039 0.003816 60.000000 0.009313 0.005768 0.006989 0.004272 61.000000 0.010490 0.006465 0.007867 0.004781 62.000000 0.011747 0.007235 0.008725 0.005351 63.000000 0.013091 0.008094 0.009677 0.005988 64.000000 0.014542 0.009059 0.010731 0.006701 65.000000 0.016134 0.010148 0.011900 0.007499 66.000000 0.017905 0.011376 0.013229 0.008408 67.000000 0.019886 0.012760 0.014705 0.009438 68.000000 0.022103 0.014316 0.016344 0.010592 69.000000 0.024571 0.016066 0.018164 0.011886 70.000000 0.027309 0.018033 0.020184 0.013337 71.000000 0.030340 0.020241 0.022425 0.014964 72.000000 0.033684 0.022715 0.024911 0.016787 73.000000 0.037371 0.025479 0.027668 0.018829 74.000000 0.041430 0.028561 0.030647 0.021117 75.000000 0.045902 0.031989 0.033939 0.023702 76.000000 0.050829 0.035796 0.037577 0.026491 77.000000 0.056262 0.040026 0.041594 0.029602 78.000000 0.062257 0.044726 0.046028 0.033070 79.000000 0.068871 0.049954 0.050920 0.036935 80.000000 0.076187 0.055774 0.056312 0.041241 81.000000 0.084224 0.062253 0.062253 0.046033 82.000000 0.093071 0.069494 0.068791 0.051365 83.000000 0.102800 0.077511 0.075983 0.057291 84.000000 0.113489 0.086415 0.083883 0.063872 85.000000 0.125221 0.096294 0.092554 0.071174 86.000000 0.138080 0.107243 0.102059 0.079267 87.000000 0.152157 0.119364 0.112464 0.088225 88.000000 0.167543 0.132763 0.123836 0.098129 89.000000 0.184333 0.147553 0.136246 0.109061 90.000000 0.202621 0.163850 0.149763 0.121107 91.000000 0.222500 0.181775 0.164456 0.134355 92.000000 0.244059 0.201447 0.180392 0.148896 93.000000 0.267383 0.222987 0.197631 0.164816 94.000000 0.292544 0.246507 0.216228 0.182201 95.000000 0.319604 0.272115 0.236229 0.201129 96.000000 0.348606 0.299903 0.257666 0.221667 97.000000 0.379572 0.329942 0.280553 0.243870 98.000000 0.412495 0.362281 0.304887 0.267773 99.000000 0.447334 0.396933 0.330638 0.293385 100.000000 0.484010 0.433869 0.357746 0.320685 101.000000 0.522397 0.473008 0.386119 0.349615 102.000000 0.562317 0.514211 0.415626 0.380069 103.000000 0.603539 0.557269 0.446094 0.411894 104.000000 0.645770 0.601896 0.477308 0.444879 105.000000 1.000000 1.000000 1.000000 1.000000 （2）中国人寿保险业经验生命表（20002003）换算表（CL1）i= 2.5%年龄xDxNxSxCxMxRx0100000034417466.98949358027.3713.1407688162544.067111402304.011974905.365933417466.98914940560.3580.6545589161830.926311239759.952950553.656532442561.61881523093.3468.5060999161250.271811077929.023926906.663631492007.96849080531.7380.8617883160781.765710916678.754903922.995630565101.29817588523.7319.6336755160400.903910755896.985881560.381629661178.3787023422.4281.2500795160081.270210595496.086859781.109928779617.92757362244.1262.4124559159800.020110435414.817838551.646327919836.81728582626.2255.1047821159537.607710275614.798817847.192627081285.16700662789.4251.229494159282.502910116077.189797651.553326263437.97673581504.2245.8135827159031.27349956794.67910777953.8425465786.42647318066.3239.7433062158785.45989797763.40611758742.554624687832.58621852279.9233.8229329158545.71659638977.94612740005.684823929090.02597164447.3228.7796867158311.89369480432.22913721730.793223189084.34573235357.3228.1199569158083.11399322120.33614703902.282222467353.54550046272.9233.6090794157854.99399164037.22215686503.191321763451.26527578919.4246.8209454157621.38499006182.22816669515.418721076948.07505815468.1267.1652785157374.56398848560.84317652921.887320407432.65484738520293.4341739157107.39868691186.27918636707.129619754510.76464331087.4322.6228663156813.96458534078.88119620859.023219117803.63444576576.6350.7737562156491.34168377264.91620605369.650618496944.61425458773371.3216844156140.56788220773.57521590237.771717891574.96406961828.4385.359904155769.24618064633.00722575461.097117301337.19389070253.4393.3327705155383.88627908863.76123561036.954216725876.09371768916.2396.773408154990.55357753479.87424546961.2212161648391398.7043424154593.78017598489.32125533226.891615617877.92338878201399.7531427154195.07577443895.54126519826.509615084651.02323260323.1399.9760143153795.32267289700.46527506752.746114564824.51308175672.1397.925075153395.34667135905880714058071.77293610847.6397.6697168152997.42156982509.79629481558.312913564071.89279552775.8400.4967933152599.75186829512.37530469417.405713082513.58265988703.9408.4822265152199.2556676912.62331457564.7307126130963421.2176115151790.77286524713.36832445988.566212155531.44240293094.2437.87301151369.55516372922.59533434678.296211709542.87228137562.7452.9585721150931.68216221553.0434423628.985911274864.58216428019.8469.0610248150478.72366070621.35835412833.266210851235.59205153155.3486.8745568150009.66255920142.63436402283.261710438402.32194301919.7506.6175319149522.7885770132.97237391971.073710036119.06183863517.4529.249675514901618438381888.0489644147.989173827398.3555.2415319148486.92085471594.01339372025.27699262259.941164183250.3583.8945386147931.67925323107.09240362374.75988890234.664154920990.4613.8469921147347.78475175175.41341352930.03618527859.904146030755.7643.1659709146733.93775027827.62942343686.71248174929.868137502895.8671.4707458146090.77174881093.69143334640.87817831243.156129327965.9698.4198202145419.3014735002.91944325789.05567496602.278121496722.8725.6398155144720.88124589583.61845317126.24517170813.222114000120.5755.8360024143995.24144444862.73746308644.89716853686.977106829307.3791.1057307143239.40544300867.49647300335.57426545042.0899975620.29832.1041913142448.29964157628.0948292188.42246244706.50693430578.21877.9307901141616.19544015179.79149284194.71725952518.08387185871.71926.0541528140738.26463873563.59550276348.44775668323.36681233353.62974.4584205139812.21053732825.33151268645.74025391974.91875565030.261020.798944138837.75213593013.125226108517870173055.341065.566407137816.95313454175.36853253664.71024862244.04665049726.161110.948197136751.38673316358.41554246380.44974608579.33660187482.111162.760999135640.43853179607.02855239222.6774362198.88655578902.781229.402917134477.67753043966.5956232173.65994122976.20951216703.891317.241625133248.27462909488.91257225209.80913890802.54947093727.681429.662974131931.0332776240.63758218304.76653665592.7443202925.131565.445334130501.372644309.60459211434.02343447287.97439537332.391718.541419128935.92472513808.23460204579.64173235853.9536090044.421881.872015127217.38332384872.3161197731.11373031274.30932854190.472048.748119125335.51122257654.92762190884.79442833543.19529822916.162214.809464123286.76312132319.41563184041.43492642658.40126989372.962379.723508121071.95372009032.65264177202.09612458616.96624346714.562545.253611118692.23011887960.69965170366.07142281414.8721888097.62714.958708116146.97651769268.4686616352979819606682.732892.061931113432.01781653121.49267156684.06511947519.64217495633.933077.586959110539.95591539689.47468149822.67881790835.57715548114.293270.896853107462.36891429149.51869142937.70551641012.89813757278.713469.027204104191.4721132168795921498075.19212116265.813669.124674100722.44491217495.6777112908323310618190.623868.327997053.32021116773.23272122113.94221232967.0599256140.3884062.81434993184.99231019719.91273115122.59131110853.1178023173.3284249.45606389122919774108117.4097995730.52566912320.2124424.34141584872.72189837412.741875101110.3468887613.11585916589.6864584.21814580448.38048752540.01997694116.27283786502.7695028976.57472516233672091.63947787153.59687692386.49624242473.8014843.26853471139.0259596227.47717880244.05971605232.89933550087.3054934.45450466295.75736525088.45127973412.98077524988.83962944854.4064993.98592861361.30286458792.69388066689.71256451575.85892419865.5665018.54457256367.31693397431.3918160106.16923848867075000.26452551348.77236341064.0748253701.25581324779.97711583403.5614936.7017446348.50784289715.30178347515.34266271078.72131258623.5844824.64149341411.8061243366.79388441590.99067223563.3786987544.86234662.20216436587.1646201954.98778535971.5812181972.388763981.48374449.12659731924.96244165367.82318630699.69154146000.8068582009.09574186.99900427475.83584133442.86078725815.29574115301.1152436008.28893879.7800823288.83684105967.02498821353.4807789485.8195320707.17373533.72731119409.0567682678.188028917342.2971168132.33873231221.35423157.53214715875.3294563269.131279013800.526350790.04162163089.01542761.96512717.797347393.801829110735.8535236989.51532112298.97382359.416769955.832334676.00452928143.53767226253.661875309.458511963.1161897596.41554247208868418110.1241349055.796711586.1673435633.29935117123.75668944292.69736512104.2372930945.672581240.39365404745733952962.8239097811.53992118841.4353935.31115512806.7383577443.325322961966.7254024848.71601111029.89538677.19953621871.4272024636.586965971249.8664652881.990616181.179367468.59305871194.227666276553868381632.1241453299.188757308.2392189725.63460721570.93209799433.6295214875.58546131667.064612191.5970495417.3953882845.29749100233.8071152441.9559399791.4791505111.7764019225.7983387427.9021018101117.6996423208.1488247349.523210660.73148276114.0219369202.103763110254.8426363690.44918241141.374385830.4605404353.290454188.081826210323.4182337735.6065460550.92520344