Transcription
EXAMPLE 1 . 1A. Endowment . 1B. Pure endowment and Term insurance . 4C. Reserves . 7D. Brutto premium and reserves . 10EXAMPLE 2 . 14A. Whole life . 14B. Reserves of Whole life . 16C. Brutto Whole life . 17EXAMPLE 3 . 18A .Pure endowment . 18B. Deferred annuity . 18C. Fixed premium annuity . 20D. Reserves . 22EXAMPLE 4 – UNIVERSAL TRADITIONAL APPROACH . 25EXAMPLE 5 – FLEXIBLE PRODUCT . 27A. Capital value at the end of policy . 27B. Minimizing premium . 27C. Endowment . 28EXAMPLE 6 – CASH-FLOW MODEL . 29A. Profitability test . 29B. Liability Adequacy Test . 31ACTUARIAL FORMULAS AND MS EXCEL FUNCTIONS . 32Nomenclature . 32Mortality tables and Commutation tables . 32
Actuarial functions. 34Reserves . 36Regular netto premium . 37TUTORIAL OF MS EXCEL APPLICATION . 39
Example 1A client, man 25 years old, wants to make an insurance contract for 25 years.A. In case of death he wants to secure his family with 200 000. In case he survives all 25 yearshe also wants to receive 200 000. How much would this contract cost him?Derive results of single and regular netto premium.B. The client wants to see how much of the premium he pays for insurance to secure his familyin case of death and how much for 200 000 in case he survives.Derive results of single and regular premium.C. You, as an insurance company, should be able to cover most of your contracts. For thatpurpose you should calculate reserves for all considered contracts. Calculate reserve in year7.D. Apply charges and calculate brutto premium and reserves of contracts mentioned in sectionA and B.*note: assume traditional approach with interest rate 0.025 p.a.A. EndowmentGenderAgePolicy periodDeath benefit (K)Survival benefit (D)Interest rate (i)Type of insurance contractMale2525200 000200 0000.025EndowmentPremium:Endowment108 888,995 829,87491,46SingleRegular annualRegular monthlySingle premiumUsing Excel functionUsing Mortality tables xn K*108 888,99 200 000 *dx * v dx 1 * v2 . dx n-1 * vnlx2626,8398982,34 D* 200 000 *lx n*vnlx95039,86 * 0,5393998982,341
Using probability xn 108 888,99K*qx*v 1 qx*v2 n-1 qx * vn 200 000 * 0,02654D*npx 200 000 * 0,96017*vn*0,539391Using Commutation numbers xn K*108 888,99 200 000* xn K*108 888,99 200 000*Cx Cx 1 Cx 2 . Cx n-1Dx1416,8953390,15Mx - Mx nDx1416,8953390,15 D* 200 000* D* 200 000*Dx nDx27651,1153390,15Dx nDx27651,1153390,15Regular premium – annualUsing Excel functionUsing Actuarial formulasK * nP xK * A1xnD*n Ex200 000*0,51791ӓxn 5 829,87200 000 * 0,0265418,68Using probabilityK * nP x 5 829,87 K* qx * v 1 qx * v2 n-1 qx * vn D*nnpx * v*0,517911 1px * v n-1px * vn-1200 000 *0,02654 200 00018,682
Using Commutation numbersK * nP xK * Mx - Mx nD*Dx n200 000*27651,11Nx - Nx n5 829,87K * nP x 200 000 K* 1416,89997208,20* dx * v dx 1 * v2 dx n-1 * vn D*lx n * vn*51263,61lx lx 1 * v lx 2 * v2 lx n-1*vn-15 829,87 200 000 *2626,83 200 0001848768,26Regular premium – monthlyFrequency of premium (m)K*(m)nPx491,46 m*(1 -monthlyRegular netto premium(m-1)* (Dx - Dx n)2m * (Nx - Nx n)m 12)5 829,8711,863
B. Pure endowment and Term insuranceGenderAgePolicy periodDeath benefit (K)Survival benefit (D)Interest rate (i)Type of insurance contractMale2525200 000200 0000.025Endowment / Term insurancePremium:Endowment108 888,995 828,87491,46SingleRegular annualRegular monthlyPure endowment103 581,315 545,70Term insurance5 307,67284,17467,5123,96Pure endowment – single premiumUsing Excel functionUsing Mortality tables*lx nvn xn D*103 581,31 200 000* xn D*npx*vn103 581,31 200 000*0,96017*0,53939lx*95039,860,5393998982,34Using probabilityUsing Commutation numbers xn D*103 581,31 200 000*Dx nDx27651,1153390,154
Pure endowment – Regular premium – annuallyUsing Excel functionUsing Actuarial formulasK * nP x D*n Exӓxn 5 545,70 200 000*0,5179118,68Using Commutation numbersK *nPx D*Dx nNx - Nx n5 545,70 200 000*27651,11997208,20Using Mortality tablesK *nPx D*lx n * vnlx lx 1 * v lx 2 * v2 lx n-1 * vn-15 545,70 200 000*D*51263,611848768,26Using probabilitiesK *nPx nnpx * v1 1px * v n-1px * vn-15 545,70 200 000*0,5179118,685
Pure endowment – Regular premium –monthlyFrequency of premium (m)K*(m)nPx monthlyRegular netto premium(m-1)* (Dx - Dx n)2m * (Nx - Nx n)m*(1 -)5 545,70 467,51m 1211,86Term insurance – Single premiumUsing Excel function:Using Mortality tablesdx * v dx 1 * v2 dx n-1 * vn xn K*5 307,67 200 000*2626,8398982,34lxUsing probabilities xn K*qx*v 1 qx*v2 n-1 qx * vn5 307,67 200 000*0,02654Using Commutation numbers xn K*5 307,67 200 000*Cx Cx 1 Cx 2 Cx n-1Dx1416,8953390,15 Mx- Mx nDx1416,8953390,156
Term insurance – Regular premium – annuallyUsing Excel functionUsing Actuarial formulasnP x K*A1xnӓxn 284,17 200 000*0,0265418,68Using Commutation numbersnP x K*Mx - Mx n Cx Cx 1 Cx 2 Cx n-1Nx - Nx n284,17 200 000*1416,89997208,20Nx - Nx n1416,89 997208,20Using Mortality tablesnP x Kdx * v dx 1 * v2 dx n-1 * vn*lx lx 1 * v lx 2 * v2 lx n-1*vn-1284,17 200 000*2626,831848768,26Using probabilitiesnP x K*284,17 200 000*qx*v 1 qx*v2 n-1 qx * vn1 1px * v n-1px * vn-10,0265418,68Term insurance – Regular premium - monthlyFrequency of premium (m)K*(m)nPx23,96 m*(1 -Anuallym 1Regular netto premium(m-1)* (Dx - Dx n)2m * (Nx - Nx n))284,1711,867
C. ReservesGenderAgePolicy periodDeath benefit (K)Survival benefit (D)Interest rate (i)Reserve year (t)Type of insurance contractMale2525200 000200 0000.0257Endowment / Term insurance / Pure endowmentPremium:Reserve of regular premium0,22055*200 000 44 1100,21517*200 000 43 0340,00540*200 000 1 080EndowmentPure endowmentTerm insuranceEndowmentUsing Excel functionUsing Actuarial formulastVx Ax t,n-t -nP x*ӓx t,n-t 0,22055 0,64492-0,02915*14,56Using Commutation numberstVx 1-0,22055 1-DxDx t53390,1544680,94**Nx t - Nx nNx - Nx n650482,57997208,20Netto reserves250 000 Kč200 000 Kč150 000 Kč100 000 Kč50 000 Kč0 Kč0123456789 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 258
Pure endowmentUsing Excel functionUsing Actuarial formulastVx n-tEx t-nP x*ӓx t,n-t 0,21517 0,61886-0,02773*14,56Using Commutation numbersDx n tVxDx t0,2151727651,11 44680,94**Nx - Nx tNx - Nx n346725,63997208,20Netto reserves250 000 Kč200 000 Kč150 000 Kč100 000 Kč50 000 Kč0 Kč0123456789 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 259
Term insuranceUsing Excel function:Using Actuarial formulastVx A1x t,n- t -nPx*ӓx t,n-t 0,0054 0,0261-0,0014*14,5584Using Commutation numberstVx 0,0054 Mx t - Mx n-Dx t1164,403944680,9390-Mx - Mx nDx t1416,887544680,9390**Nx t - Nx nNx - Nx n650482,5696997208,2031Netto reserves1 200 000 Kč1 000 000 Kč800 000 Kč600 000 Kč400 000 Kč200 000 Kč0 Kč0 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 42 44 46 48 50 52 54D. Brutto premium and reservesGenderAgePolicy periodDeath benefit (K)Survival benefit (D)Interest rate (i)Reserve year (t)Type of insurance contractMale2525200 000200 0000.0257Endowment / Term insurance / Pure endowment10
Premium:Regular bruttoEndowment5 965,93Pure endowment5 680,87Term insurance402,92*Note: There are no Excel functions to calculate brutto premiumBrutto reserve42 546,9241 471,95-487,07EndowmentEndowment brutto premiumBxnK * Axn K fix ӓxn*(1 - Bxn - Bxn)- Bxn2004,00 18,68 *0,99690- 108888,99 5 965,93* fix K fix K)ӓxn10,20 18,68*0,00020Endowment brutto reserveK*Vxbrutto K*tVxnetto-42 546,92 44109,87-( K fix Bxn)*ӓx t,n-tӓx,n2005,19Brutto reserves*14,5618,68K*tVxNettoK*tVxBrutto250 000 Kč200 000 Kč150 000 Kč100 000 Kč50 000 Kč0 Kč-50 000 Kč0123456789 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 2511
Pure endowmentPure endowment brutto premiumD * nExK * Bxnbrutto ӓxn*103581,315 680,87 K fix ӓxn*(1 - Bxn - Bxn)- Bxn2004,00 18,680,99690- 18,68 * fix K fix K)10,20*0,00020Pure endowment netto reserveK*tVxbrutto K*tVxnetto-41 471,95 43 034,85-( K fix Bxn)Brutto reserves*ӓx 50 000 Kč200 000 Kč150 000 Kč100 000 Kč50 000 Kč0 Kč-50 000 Kč0123456789 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 2512
Term insuranceTerm insurance bruttopremiumBxn K fixK * A1xn ӓxn*5307,67 2004,0018,68 *0,99690 ӓxn- Bxn 402,92(1 - Bxn - Bxn)18,68 fix K fix K)*-10,20*0,00020Term insurance brutto reservebruttotVx-487,07 nettotVx- 1075,01-( K fix Bxn)*ӓx t,n-tӓx,n2004,08Brutto reserves*14,5618,68K*tVxNettoK*tVxBrutto3 000 Kč2 000 Kč1 000 Kč0 Kč-1 000 Kč-2 000 Kč-3 000 Kč0123456789 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 2513
Example 250 years old female wants to be insured for one million in case of death.A. Compare single and regular premium for whole life.B. Calculate also reserve at age 75.C. Calculate whole life premium and reserve including charges.A. Whole lifeGenderAgePolicy periodDeath benefit (K)Type of insurance contractFemale50 1 000 000Whole lifePremium:Single premium453 687,44Whole lifeRegular premium20 254,98Whole life – single premiumUsing Excel function:Using Mortality tablesdx * v dx 1 * v2 x K*453 687,44 1 000 000*44291,1597624,80lxUsing probability x K*qx*v 1 qx*v2 2 qx*v3 453 687,44 1 000 000*0,45369Using commutation numbers x K*453 687,44 1 000 000*Cx Cx 1 Cx 2 .Dx12886,1628403,17 MxDx12886,1628403,1714
Whole life –Regular premiumUsing Excel function:Using Actuarial formulasK * nPx K*Axӓxn 20 254,98 1 000 000*0,4536922,39881Using Mortality tablesK * nPx Kdx * v dx 1 * v2 *lx lx 1 * v lx 2 * v2 lx n-1*vn-120 254,98 1 000 00044291,15*2186679,82Using probabilitiesK * nPx Kqx * v 1 qx * v2 *1 1px * v n-1px * vn-120 254,98 1 000 0000,45*22,40Using Commutation numbersK * nPx K*Mx Nx20 254,98 1 000 000*12886,16636197,45Cx Cx 1 Cx 2 Nx 12886,16636197,4515
B. Reserves of Whole lifeGenderAgePolicy periodDeath benefit (K)Survival benefit (D)Reserve (t)Type of insurance contractFemale50 1 000 000025Whole lifePremium:Reserve0,53529*1M 535 287,41Whole lifeWhole life – ReserveUsing Excel function:Using Actuarial formulastVx A1x t-Px*ӓx t0,53529 0,74612-0,02025*10,40901Using Commutation numberstVx 1-0,53529 1-DxDx t28403,1712285,73**Nx tNx127882,26636197,45Netto reserves1 200 000 Kč1 000 000 Kč800 000 Kč600 000 Kč400 000 Kč200 000 Kč0 Kč0 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 42 44 46 48 50 52 5416
C. Brutto Whole lifeGenderAgePolicy periodDeath benefit (K)Reserve (t)Type of insurance contractFemale50 1 000 00025Whole lifePremium:Regular brutto premium20 443,72Whole lifeBrutto reserve534 346,79Whole life – Regular brutto premiumWhole life brutto premiumBxnK * A1x K fix ӓx*(1 - Bxn - Bxn)-453687,44 2020,00 22,40 *0,99690- 20 443,72ӓx* fix K fix K)*35,00 Bxn22,40 0,00020Whole life – Brutto reserveWhole life brutto reserve* tVxbrutto K*534 346,79 535287,41K1 200 000 KčnettotVx--( K fix Bxn)*ӓx tӓx2024,09Brutto reserves*10,4122,40K*tVxNetto1 000 000 Kč800 000 Kč600 000 Kč400 000 Kč200 000 Kč0 Kč-200 000 Kč0 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 42 44 46 48 50 52 5417
Example 3Young man of age 30 wants to be secured when he reaches his 60 by certain amount of money.A. How much he has to pay every year to get two million when he turns 60.B. How much he would have to pay to get 5 000 every year from 60 until the rest of his life.Compare this premium with premium for 5 000 every year from his 60 until his 80.C. He is sure that now he can pay max 2000 per year. What annuity he can expect when heturns 60 until his death and until his 80?D. Make the reserves of these two contracts at year 12 and 45.A .Pure endowmentUsing Actuarial formulasnP x D*n Exӓxn 40 680,47 2 000 000*0,4262120,95B. Deferred annuityGenderAge (x)Policy period (N)Deferred (k)Annuity (D)Interest rate (i)Type of insurance contractMale30 5 0000,025Deferred / Whole life / Temporary annuityPremium:Single premium30 576,3426 639,88Whole life annuityTemporary annuityRegular premium1 459,221 271,36Whole life annuity – Single premiumUsing Excel functionUsing Mortality tables x D*30 576,34 5 000*lx k 1 * vk 1 lx k 2 * vk 2 .lx603057,5398615,0718
Using probabilities x D*30 576,34 5 000*k 1px * vk 1 k 2px *vk 2 .6,12Using Commutation numbers x D*30 576,34 5 000*Dx k 1 Dx k 2 .Nx k 1 Dx2766364,0647014,01 Dx287503,2747014,01Whole life – Regular premiumUsing Excel functionRegular premiumnPx 1 459,22 D * k 1 ӓxӓxk 30576,3420,95Temporary annuity – Single premiumUsing Excel functionUsing Mortality tables xn D*26 639,88 5 000*lx k 1 * vk 1 lx k 2 * vk 2 lx n* vnlx525418,8498615,0719
Using probabilities xn D*26 639,88 5 000*k 1px *vk 1 k 2px * vk 2 npx * vn5,33Using Commutation numbers xn D*26 639,88 5 000*Dx k 1 Dx k 2 Dx nDx250489,5947014,01 Nx k 1 - Nx n 1Dx250489,5947014,01Temporary annuity – Regular premiumUsing Excel functionRegular premiumnPx 1 271,36 D * k axn ӓxk 26639,8820,95C. Fixed premium annuityWhole lifeP a xk D k a xD D P a xkk a x2000 20,95 6852,956,11Temporary annuityP a xk D k a xn20
D D P a xkk a xn2000 20,95 7865,585,32 xkExcel function for aExcel function fork axExcel function for k a xn21
D. ReservesGenderAge (x)Policy period (N)Deferred (k)Annuity (D)Interest rate (i)Type of insurance contractMale30 5 0000,025Deferred / Whole life / Temporary annuityPremium:Reserve t 124,16*5 000 20 8003,63* 5 000 18 150Whole life annuityTemporary annuityReserve t 457,93*5 000 39 6503,98*5 000 19 900Whole life annuityNetto reserves80 000 Kč70 000 Kč60 000 Kč50 000 Kč40 000 Kč30 000 Kč20 000 Kč10 000 Kč0 Kč0369 12 15 18 21 24 27 30 33 36 39 42 45 48 51 54 57 60 63 66 69 72 75Using Excel function:Using actuarial symbols:K 12, tVx for k ttVx k-t ax t-kPx*ax t,k-t4,16 8,34-0,29*14,3022
K 45, tVx k ttVx ax t7,93 7,93Temporary annuityNetto reserves70 000 Kč60 000 Kč50 000 Kč40 000 Kč30 000 Kč20 000 Kč10 000 Kč0 Kč02468 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 42 44 46 48 50Using Excel function:Using actuarial symbols:K 12, tVx for k ttVx k-t ax t,n-t-k nPx*ax t,k-t3,63 7,26-0,25*14,3023
K 45, tVx for k ttVx ax t,n-t3,98 3,9824
Example 4 – Universal Traditional approach30 years old man has very special demands about his insurance contract. When he turns 40, he wantsto be insured for 10 000 in case of death. When he turns 50, he wants to increase death benefit up to20 000 and until his 55 he wants to receive 5 000 every year. In his 60 he wants to get annuity 20 000and then until his 70 to be insured for 10 000 in case of death but doesn’t want to pay premium.The charges for this contract assume following:20002,0000%2,0000%2,0000%alfa fixalfa from premium (%)alfa z K(%)alfa z D(%)BetaGammaDeltafixfrom K or Dfrom 00000%Calculate netto and brutto premium and reserve in policy year 5 and 25 of this contract.GenderAge (x)Policy period (N)Deferred (k)Reserve (t)Death benefit (K)Annuity (D)Interest rate (i)Type of insurance contractPremium:PremiumReserve year 5Reserve year 25Male30 From age 40 to 50: 10 000From age 50 to 60: 20 000From age 60 to 70: 10 000without paying premiumFrom age 50 to 55: 5 000In age 60:20 0000,025Deferred / Term insurance / Temporary annuityNetto1 222,466 593,4516 921,97Brutto2 035,555 079,8016 538,5325
ReservestVxNettotVxBrutto35 000 Kč30 000 Kč25 000 Kč20 000 Kč15 000 Kč10 000 Kč5 000 Kč0 Kč-5 000 Kč02468101214161820222426283032343638The input information of this policy can be seen on picture below.26
Example 5 – Flexible productDeal flexible contract for 35 years old male for 30 years.A. What is the capital value at the end of policy if the premium is 2 000 per year, death benefitis 100 000 and initial deposit of 1 000?B. What is the minimal premium to keep zero reserve at the end of policy?C. What is the premium for similar contract to endowment where the death benefit is 100 000and survival benefit is 50 000?A. Capital value at the end of policyPolicy characteristicsModel point:Claims:YearDeathTech. Int. rateSA CVAge at valuation date2,5%201535SexMaleSA100 000Policy charges:1st year80,0%Premium (annual)2 0002 years20,0%CV at val. date1 00085,0%Policy year at val. dateProfit shareSurrender fee5,0%Policy periodYearAgePolicy YearPshareCV EoY 2044 64 30 67 821130B. Minimizing premium1Policy characteristicsModelpoint:Claims:DeathTech. Int. ratePolicy charges:Profit shareSurrender fee1SA CV2,5%YearAge at valuation dateSexSA201535Male100 0001st year80,0%Premium (annual)5382 years20,0%CV at val. date100085,0%Policy year at val. date5,0%Policy period130*Note: This Solution requires to install solver.27
C. EndowmentPolicy charcteristicsModelpoint:Claims:DeathTech. Int. ratePolicy charges:YearAge at valuation dateSA2,5%SexSA201535Male100 0001st year80,0%Premium (annual)1 6162 years20,0%85,0%CV at val. datePolicy year at val. date1 0001Profit shareSurrender fee5,0%YearAge 2044 64Policy periodPolicy Year 30PshareCV EoY 50 0003028
Example 6 – Cash-Flow modelOne big company wants to insure 1 000 its employees. In case of death, 100 000 will be paid and ifinsured person survives 10 years the contract will be canceled and the person will obtain at least20 000. All employees are all men in age 35.A. Compare traditional and flexible approach and decide which of the two types of contract ismore profitable.B. Make calculation of Liability Adequacy Test (LAT) for both approaches.A. Profitability testFlexible approachCalculation of flexible product premium with SA 100 000 and at the end CV 20 000. By usingsolver the minimal premium is 2 495.Policy characteristicsModelpoint:Claims:YearDeathTech. Int. RateSAAge at valuation date2,5%Policy charges:Profit shareSurrender fee201535SexMSA100 0002 4951st year80,0%Premium (annual)2 years20,0%CV at val. date085,0%Policy year at val. date15,0%Policy period10From Cash-Flow model the profitable criteria is Pcrit 1.Pcrit1 PVPLPV Pr emiumLiability modelPV CF-29 388 850PV PL-29 636 250PV Premium115 266 345Net PL-24 830 982Total Earnings-24 830 982Pcrit 1-25,7%Pcrit 2-118,8%29
Traditional approachThe premium of traditional approach of endowment contract below needs to be firstly calculated.Time (t)Age (x)12345678910In force35363738394041424344Premium transfer Death benefit Survival benefit11111111111111111111100 000100 000100 000100 000100 000100 000100 000100 000100 000100 000Regular brutto premium20 0002 146,58The profit criteria can be seen in output of Cash-flow model.Liability modelPV CF-14 196 104PV PL5 468 347PV Premium99 171 010Net PL4 106 974Total Earnings4 907 146Pcrit 15,5%Pcrit 225,5%Traditional approach seems to be more profitable for insurance company based on Pric1 and also forinsured person because of lower premium. The reason why traditional approach gives better resultsis based on assumptions of no surrenders payoffs. If client cancels flexible policy, he receives hiscapital value adjusted by surrender fee. In traditional approach we assume no payout when thepolicy is canceled.30
B. Liability Adequacy TestFlexible approachTraditional approachBE29 388 850BE14 196 104RM8 976 977RM10 882 61425 078 718FV38 365 827FVLAT38 365 827LAT5 074 425After pressing “Calculate LAT” Liability adequacy test will be automatically calculated.31
Actuarial formulas and MS Excel functionsNomenclatureXAgeNPolicy periodiInterest rateDDeath benefitKSurvival benefittTime of reservekDeferred timeMortality tables and Commutation tablesProbability of death qxExcel function: qx (x)qx l x l x 1 d x lxlxProbability of survive pxExcel function: px (x)px 1 qxpx l x 1lxNumber of living lxExcel function: lx (x)l x p x 1 l x 1 1 q x 1 l x 1Number of death dxExcel function: dx (x)d x l x l x 1Probability of surviving n years npxExcel function: npx (x,n)npx l x nlx32
p x i 0 p x innProbability of death in n years nqxExcel function: nqx (x,n)nl x l x nlxqx nq x 1 n p xProbability of death in certain age x n n qxExcel function: n qx (x,n)n qx d x nlxDiscounted number of living at age xExcel function: DDx (x,i)Dx l x v xDiscounted number of death at age xExcel function: Cx (x,i)C x d x v x 1Commutation numbers of first orderExcel function: Mx (x,i)N x Dx i 0 Dx i x[ 2]Excel function: Nx (x,i)M x Cx[ 2] i 0 C x i xCommutation numbers of second orderExcel function: Sx (x,i)S x Dx[ 3] i 0 N x i xExcel function: Rx (x,i)Rx C x[ 3] i 0 M x i x33
Actuarial functionsPure endowmentExcel function: nEx (x,n,i)l x n v nlxn Ex Dx nDxnEx nEx n px v nWhole lifeExcel function: A1x (x,i) 1Axi 0 lx 1Ai 0xd x i v i 1C x iDx MxDxA1 x i 0 q x i v i 1 Temp insuranceExcel function: A1xn (x,n,i)1A1Axn n 1i 0xn n 1i 0C x iDxd x i v i 1lx M x M x nDxA1 xn i 0 i q x i v i 1n 1EndowmentExcel function: Axn (x,n,I,D,K)Axn A1 xn D / K n E xAxn n 1i 0d x i v i 1lx D/K l x n v nlx34
Axn n 1i 0C x iDxDx n M x M x nD D / K x nDxDxDx D/K Axn i 0 i q x i v i 1 D / K n p x v nn 1Whole life annuityExcel function: Dax (x,i,in arearrs,frequency,deferred) k a x v k ili 1 x k ilxa x i 1 k i p x v k i k k axi 1D x k iN x k 1Dx Dxa x a x 1 v k ili 0 x k ik a xk a x i 0 k i p x v k ilx k a xD x k ii 0Dx N x kDxTemp annuityExcel function: Daxn (x,N,i,in arearrs,frequency,deferred) nli 1 x k i v k ik a xnk a xn i 1 k i p x v k ilxnk a xn ni 1D x k iDx N x k n 1Dxa xn a xn 1k xn k 1 a xna35
k a xn n 1i 0 x k il v k ilxa xn i 0 k i p x v k in 1k k n 1i 0a xnD x k iDx N x k nDxReservesEndowment reserveExcel function: tVx Endowment (x,n,t) x t ,n tVx Ax t ,n t Pzn atVx 1 tDx N x t N x n Dx t N x N x nWhole life reserveExcel function: tVx Whole live (x,t) x tVx A1 x t Pz atVx 1 tDx N x t Dx t N xTemp insurance reserveExcel function: tVx Temp insurance (x,n,t)Vx A1 x t ,n t Pzn a x t ,n ttVx tM x t M x n M x M x n N x t N x n Dx tDx tN x N x nPure endowment reservesExcel function: tVx Pure endowment (x,n,t) x t ,n tVx n t E x t Pzn atVx tDx n N x N x t Dx t N x N x nDeferred life anuity reservesExcel function:36
For t k x t k Px a x t ,k tV x k t atVx tN x k N x N x tD x t N x N x kFor t kV x a x ttN x tDx tVx tRegular netto premiumPure endowment regularExcel function: regular Pure endowment (x,n,i)Pxn Pxn Pxn Pxn Exa xnnDx nN x N x nl x n v n n 1li 0 x i vip vnnxn 1 i 0 ipx viWhole life regularExcel function: regular Whole life (x,n,i)Px MPx x NxAxa x i 0Nx d l Pxi 0C x ix ii 0 x i v i 1 vi37
q x v i 1i 0 i Pxi 0 ipx viTemp insurance regularExcel function: regular netto Temp insurance (x,n,i)Pxn A1 xna xnM M x n i 0 C x iPxn N x N x n N x N x nn 1 n 1d v i 1l vii 0 x in 1Pxni 0 x i n 1i 0 i xn 1i 0 i xPxq v i 1p viEndowment regularExcel function: regular netto Endowment (x,n,i,K,D)Axn A1 xn D / K n E x a xna xnPxn Pxn PxnM x M x n D / K Dx nN x N x n n 1i 0d x i v i 1 D / K l x n v n n 1li 0 x iPxn n 1i 0 i viq x v i 1 D / K n p x v n n 1i 0 ipx vi38
Tutorial of MS Excel application1243Input information:Fill input information to yellow cells. See in part 1.Output results:Results are automatically calculated in green part 2.Different ways of calculation:See part 3, different approaches to obtain same result based on input information.Detailed application of formulas:Different approaches from part 3 are described in part 4 in detail.39
Type of insurance contract Endowment / Term insurance Premium: Endowment Pure endowment Term insurance Single 108 888,99 103 581,31 5 307,67 Regular annual 5 828,87 5 545,70 284,17 Regular monthly 491,46 467,51 23,96 Pure endowment - single premium Using Excel function Using Mortality tables xn D * l x n * vn l x
