Hi. Could you help me with this please? I tried but I couldn't figure it out! Thank you very much!
A A P C ... 3 Apll 1 Home Insert Draw Page Layout Formulas Data Review Tell me Share Comments Arial v 12 AA =E ap Wrap Text v General LIX V V Paste BI UV V DVAv E Merge & Center v $ ~ % " Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data J20 X V fx =120 A C D E F G H I J K L M N 0 P Q R S APLII INPUT: 9 DEATH BENEFIT [APLII B3] = $100,000 10 ISSUE AGE [APLII A7] = 24 GUARANTEED INTEREST RATE [APLII G3] = 2.5% 12 CURRENT INTEREST RATE [APLII H3] = 5.0% Questions 1, 2 and 3 may be performed simultaneously. 14 1) Review the CSO tables on the APLII Base Information [AF to AX] 15 a. What is the Cost of Insurance Per $1,000 of Death Benefit based on the 2001 CSO Table by Risk Rate Class [D3]? 16 MALE FEMALE AGE PREFERRED_STANDARD_ PREFERRED_STANDARD_SMOKER 17 SMOKER 18 24 = 19 44 = 20 64 = 11 11 11 21 84 22 23 b. What is the Cost of Insurance Per $1,000 of Death Benefit based on the 2017 CSO Table by Risk Rate Class [D3]? 24 MALE FEMALE 25 AGE PREFERRED_STANDARD_SPREFERRED_STANDARD_SMOKER 26 27 24 $ 0.69 $ 0.82 $ 1.00 $ 0.27 $ 0.32 $ 0.40 28 44 = 29 64 = 11 11 11 30 84 : 31 c. List observations with respect to the change in mortality rates over time or by risk rate class. 32 33 2) Review the CSO tables on the APLII Base Information [AF to AX] 34 a. What is the Percentage Probability of Premature Death based on the 2017 CSO Table by Risk Rate Class [D3]? 35 MALE FEMALE 36 AGE PREFERRED_STANDARD_S PREFERRED_STANDARD_SMOKER 37 38 24 39 44 = 11 1 1 1 11 11 11 11 40 64 = 41 84 = 42 43 44 45 a. List observations with respect to the change in mortality rates over time or by risk rate class. 46 47 D Sheet1 Sheet2 + Ready Circular References + 100%3 Apll 1 Q A P Y C ... Share Comments View ? Tell me Home Insert Draw Page Layout Formulas Data Review v 12 AA ab Wrap Text v General LIX V V Calibri (Body) Insert Delete Format Sort & Find & Analyze = $ ~ % 9 00 20 Conditional Format Cell Filter Select Data Formatting as Table Styles Paste BIUV E Merge & Center v D70 fx R S E F G H I J K L M N P Q A B C D 42 43 44 45 a. List observations with respect to the change in mortality rates over time or by risk rate class. 46 47 3. Estimate the Life Expectancyes, Number of Years.Number of Months [Traditional Whole Life B] by changing Gender [C3], Risk Rate Class [D3] and CSO Mortality Table [F3]. Issue Age is 24. 48 49 Hint: Life Expectancy is when Proportion Living is 50% Stan Smoker 50 Female Preferred a. 198 b. 200 = 54 55 C. 201 Preferred Stan Smoker 56 Male 57 d. 198 58 59 e. 200 60 61 f. 201 62 g. What observations do you have about the above? 63 64 65 66 67 68 69 70 71 73 75 76 77 78 79 80 81 82 D Sheet1 Sheet2 + + 100% Readyn A F ? C ... APPLIED LIFE INSURANCE ILLUSTRATOR - LEARNING BY DOING BU378 FALL 21-2 Home Insert Draw Page Layout Formulas Data Review View ? Tell me Share Comments Arial v 12 A A ab Wrap Text v Percentage ZY AP - O. v Z Paste BIUV DVAv E Merge & Center v $ ~ % " Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles v Filter Select Data AB20 X V fx =MAX(AB19+$AB$5,$G$3) B 3 H M N P Q S U W X Z AA AB AC Insured Information & Policy Premiums Basic Assumed Policy Elements Prospective Reserve Method Outpu APLII INPUT DEATH GENDER RISK RATE CSC GUARANTEED CURRENT PREMIUM EXPENSE MORTALITY INTEREST INTEREST PERCENTAGE CHARGE - NET SINGLE NET LEVEL ANNUALIZED PV LIFE BENEFIT CLASS POLICY ANNUITY TERM INSURANCE INPUT BASE INFORMATION TABLE RATE RATE LOADING ADMIN. FEE PREMIUM PREMIUM EXPENSE DUE $1 $250,000 Male Standard 2001 2.50% 5.00% 4.00% $75.00 $70, 137.13 $2,377.94 $185.54 $29.49 PREMIUMS Annual Projected Change Participating Dividend Factors Vanishing Premiums Universal Life Inputs Adjustable Policy Elements 0.10% SSUI PREMIUMS PREMIUM ADJUSTEL PART. WL EXPENSE MORTALITY # PREMIUMS # PREMIUMS UNIVERSAL LIFE DEATH PROJECTED Annual PAYABLE PAYMENT ANNUAL DIVIDEND DIVIDEND CURRENT IFE PLANNED BENEFIT Interest Rate TERM LEVEL YEARLY AGE DIVIDEND ARTICIPATING INTEREST RATE ANNUAL FEE TERM POLICY INTEREST POLICY S # YEARS MODE PREMIUM OPTION FACTOR % FACTOR % WHOLE LIFE ASSUMPTION PREMIUM OPTION Change # YEARS YEAR RATE YEAR 76 Annual $2,563.48 PUA 10% 15.0% 76 76 $1,438.58 Level current 0.10% 40.00 5 1 5.00% 5.10% ILLUSTRATIONS PROSPECTIVE METHOD RETROSPECTIVE METHOD ANNUAL 5/10/20 YR DUI AWN 3 5.20 % 5.30% TRADITIONAL WHOLE LIFE - GUARANTEED PARTICIPATING WHOLE LIFE CURRENT ASSUMPTION WL UNIVERSAL LIFE-GUARANTEED|UNIVERSAL LIFE-PROJECTED RENEWABLE LEVEL 5 5.40% 12 Paid-Up Death Benefit Cash Value Dividend Death Benefit Cash Value Cash Value Death Benefit Cash Value Death Benefit TERM TERM Cash Value Death Benefit Age 13 250,000 114 114 250, 397 250,000 250,000 250,000 $302.76 7 5.50% $ 312.01 5.60% 250,000 286 170 250,976 229 250,000 109 250,000 250,000 $307.75 $ 312.01 8 5.70% 1,354 4.518 250.0 1,876 229 251,740 1,354 250,000 329 250,000 250 0 $320.25 $ 312.01 5.80% 3,727 12, 163 250,000 4,551 252, 68 3,727 250,000 663 en enen 250,000 708 250, 0 $332.75 $ 312.01 5.90% 6 .163 19,669 250,000 7,359 354 253,819 6.163 250,000 1,116 250,000 1,207 250 , 0 $332.73 312.01 11 6.00% 8,666 27,052 250,000 10,310 420 255, 130 8,666 250,000 1,694 250,000 1,856 250,000 $327.72 318.24 6.10% 11,238 34,305 250,000 13,408 489 256, 624 11,238 250,000 2,402 2,666 250 ,000 30 $325.20 $3 318.24 13,880 41,427 250,000 16,662 258,303 13,880 250,000 3,24 250,000 3.649 250 ,000 $322.68 318.24 6.30% 16,591 48,415 250,000 20, 076 640 260, 171 16,59 250,000 4.228 250,000 4,817 250 ,000 $322.67 318.24 6.40% 19,367 55.258 250,000 23, 654 722 262,231 19,367 250,000 Paste 250,000 6,870 250,000 33 $327.66 $ 318.24 6 50/ 22,210 61,953 250,000 27,403 808 264,48 22,210 250,000 250,000 9,309 250,000 $335.15 353 .94 6.60% 25, 121 68,506 250,000 31,331 899 266,936 25, 121 250,000 0,24 250,000 12, 162 250,000 $342.64 4 $ 353.94 6.70% 28,092 74,900 250,000 35,439 995 269,589 31,664 250,000 2,82 en en en 50,000 15,450 250,000 $360.13 $ 3 353.94 6.80% 31,129 81, 146 250,000 39,73 1,095 272,442 39,402 250,000 5,715 250,000 19,203 250,000 $375.12 53.94 6.90% 34,224 87,233 250,000 44,229 1,201 $ 275,502 48, 176 250,000 18,901 250,000 23,441 $ 250,000 $400. 11 $ 353.94 7.00% $425. 10 $ 81.51 7.10% $ 131,439 197,338 $ 250,000 257,215 6,616 $ 438,836 274, 122 328,947 45,261 $ 250,000 110,978 $ 250,000 $452. 481.51 7.20% $487.57 $ 481.51 7.30% 151,308 209,568 $ 250,000 327,825 8,643 $ 494,484 357,413 411,025 6,053 250,000 $ 134,893 $ 250,00 $530.05 $ 481.51 7.40% 170,732 219,825 250,000 412,406 1,095 61, 166 462,485 485,610 10,30 250,000 161,901 $ 250.00 $577.53 481.51 7.50% en en 188,644 228,088 250,000 512,333 $ 14,351 641,370 596,790 626,630 Lapse Laps 193,487 $ 250.00 $637.50 741.17 7.60% $702.50 en en en 741.17 7.70% $ 203,715 $ 234,320 250,000 $ 629,354 8,505 $ 39,586 759,685 797,669 apse Lapse 237,575 $ 250,000 $765.00 $ 741.17 7.80% 215,288 en en e 238,788 250,000 765,565 $ 3,626 $ 860,342 950,534 998,060 Lapse Lapse 300,474 $ 315,498 $832.50 $ 741.17 7.90% 223,826 242,309 250,000 921,705 s 8,459 1,005,509 , 192,416 1,204,341 apse Lapse 380, 156 $ 383,957 $872.50 $ 7 741.17 8.00% $920.00 $ 1,046.78 8.10% 100 $ 234,864 250,000 $ 250,000 $ 1,108,427 $ 6,387 $ 1,179,861 1,517,673 $ 1,532,849 Lapse Lapse 487,077 491,948 $980.00 $ 1,046.78 8.20% 11 240, 129 250,000 250,000 $ 1,585,543 57,806 1,650,720 2,383,643 2,407,479 Lapse Lapse 764,331 771,974 $1,055.00 $ 1,046.78 8.30% 120 243,902 250,000 $ 250,000 $ 2,335,760 90,423 $ 2,394, 154 3,655,608 $ 3,692, 164 Lapse Lapse 1, 171,519 $ 1,183,234 $1, 157.50 $ 1,046.78 8.40% 8.50% 8.60% 3.70% 8.80% 8.90% 9.00% 9.10% 9.20% 9.30% 9.40% 9.50% 9.60% 9.70% 9.80% 9.90% An nnol APLII TRADITIONAL WHOLE LIFE PARTICIPATING WHOLE LIFE CURRENT ASSUMPTION WHOLE LIFE UNIVERSAL LIFE TERM + Ready Average: 6.35% Count: 2 Sum: 12.70% 71%n A F ? C ... APPLIED LIFE INSURANCE ILLUSTRATOR - LEARNING BY DOING BU378 FALL 21-2 Home Insert Draw Page Layout Formulas Data Review View ? Tell me Share Comments Arial v 12 AA ab Wrap Text v Percentage LIX V V LY AP- O. Sort & Find & Analyze Paste BIUV DVAv E Merge & Center v $ ~ % " Conditional Format Cell Insert Delete Format Formatting as Table Styles Filter Select Data AB20 X V fx =MAX(AB19+$AB$5,$G$3) AW AX AZ AA AB AC AD AE AF AG AH Al AJ AY BA AK AL AM AN AO AP AQ AR AS AT AU AV 20 Years IRS 26 3 Annual Projected Change CSO MORTALITY TABLES - GUARANTEED COST OF INSURANCE ANNUAL RATES 0% COI INDEX SECTION 7702 5 0.10% 1958 CSO 1980 CSO 1980 CSO 2001 CSO 2001 CSO 2001 CSO 2001 CSO 2001 CSO 2001 CSO 2017 CSO 2017 CSO 2017 CSO 2017 CSO 2017 CSO 2017 60% 140% FEMALE MALE FEMALE PREFERRED RATED SELECT (-) COl as % of DEATH YEARLY E FEMALE MALE RATED (+) 2001 CSO BENEFIT POLICY INTEREST POLICY SURRENDER MINUS 3 MALE FEMALE PREFERRED MALE MALE REFERRED FEMALE FEMALE PREFERRED MALE MALE PREFERRED FEMALE FEMALE MORTALITY MORTALITY YEAR RATE YEAR CHARGE AGE YRS) STANDARD STANDARD NONSMOKER STANDARD SMOKER NONSMOKER STANDARD SMOKER NONSMOKER STANDARD SMOKER NONSMOKER STANDARD SMOKER EXPERIENCE EXPERIENCE CO Standard AGE FACTOR 6 60.1% 140% 250% 5.00% 100% 7.08 4.18 2.89 0.97 0.97 0.97 0.48 1.48 0.48 0.24 0.24 .24 0.26 0.26 1.26 100% 100% 0.87 .56 0.56 0.56 0.35 0.35 0.35 0.15 0.15 0.15 0.14 0.14 0.14 60.19 140% 100% 100% 250% 5.10 95% 1.76 1.07 0.39 0.26 0.26 0.13 0.09 0.09 140% 100 % 100% 250% 90% 1.52 0.99 0.81 0.39 0.39 AWN -20 00 5.209 0.98 0.79 0.27 0.27 0.27 0.20 0.20 0.20 0.12 0.12 0 .12 0.08 0.08 50.1% 140% 100% 100% 250% 5.30% 85% 5.40% 80% 1.40 0.95 0.77 0.21 0.21 0.21 0.19 0.19 0.19 0.11 0 .11 0.08 0.08 0 .08 60. 1% 140% 100% 100% 250% YOUAWN 0.11 5.50% 1.35 0.90 0.7 0.21 .21 0.21 0.18 0.18 0.18 0.10 0.10 0.10 0.09 0.09 0.09 75% 60.19 140% 100% 00% 250% NOUP 5.60% 70% 1.30 0.86 0.73 0.22 ).22 0.22 0.18 0 . 18 0. 18 0.10 0.10 0.10 0.09 0.09 0.09 60.19 140% 100% 100% 250% 7 1.26 0 .80 0.72 0.22 0.22 0.22 0.21 0.21 0.21 0.09 0.09 0.09 0.08 0.08 0.08 60.1% 140% 100% 100% 250% 5.709 65% GAWNO VO VIAWN 1.23 0.76 0.70 0.22 0.22 0.22 0.21 0.21 0.09 0.09 0.09 0.08 0.08 0.08 60.1% 140% 100% 100% 250% 5.80 60% 0.74 0.69 0.23 0.23 0.23 0.21 0.21 0.21 0.09 0.09 0.08 0.08 60.1% 140% 100% 100% 9 5.90 50% 6.00% 40% 10 1.21 0.73 0.68 0.23 0.23 0.23 0.22 0.22 0.22 0.10 0 .10 0 .10 0.09 0.09 0.09 60.1% 140% 100% 100% 10 250% 140% 100% 250% 6.10% 30% 11 1.23 11 0.77 0 .69 0.27 0.27 0 .27 0.23 0.23 0.23 0.11 0.11 0.11 0.09 0.09 0 .09 60.19 100% 1.26 0.27 0.27 0.14 0.14 0.14 0.08 0.08 0.08 60.19 140% 100% 100% 12 20% 12 0.85 0.72 1.33 0.33 250% 6.20% 0.33 0.09 10% 13 1.32 0.99 0.75 0.39 .39 0.39 0.30 0.30 0.30 0.20 0.20 0.20 0.09 0.09 60.19 140% 100% 100% 13 250% 6.30% 6.40% 0% 14 1.39 1.15 0.80 0.47 0.47 0.47 250% 0.33 0.33 0.33 0.29 0.29 0 .29 0.15 0.15 0 .15 60 .1% 140% 100% 100% 14 1.46 1.33 140% 100% 15 6.50% 15 0.85 0.61 0.61 0.61 0.35 0.35 0.35 0.42 0.42 0.42 0.23 0.23 0.23 60.19 100% 1.51 0.90 0.74 0.74 0.79 0.39 0.39 0.41 0.59 0.59 0.59 0.30 0 .30 60. 19 140% 100% 100% 16 250% 6.60% 1.62 1.67 0.97 0.41 0.41 0.46 0.78 0.78 0 .78 0.33 0.33 0.33 60.19 140% 100% 100% 17 250% 6.70 17 0.9 0.85 ).87 6.809 1 .69 1.78 0.98 0.92 ).94 1.11 0.42 0.43 0.50 0.67 0.80 0.80 0.28 0.33 0.33 60.19 140% 100% 100% 18 250 70 6.90 1.74 1.86 1.02 0.94 0.98 1.21 0.45 Past 0.54 0.70 0.83 0.84 0.28 0.33 0.33 60.1% 140% 100% 19 250% 7.00% 1.79 1.05 0.95 1.00 1.27 0.45 U.41 0.58 0.72 0.85 0 .87 0.27 0.32 0.32 60.19 140% 100% 100% 20 250% 7.10% 1.83 1.91 1.07 0.95 1.00 1.33 0.46 0.48 0 .61 0.72 0.85 0.90 0.27 0.32 0.32 60.19 140% 100% 100% 21 250% 1.89 0.95 1.02 0.48 0.50 0.65 0.70 0.83 0.93 0.27 0.32 0.33 60.19 140% 100% 100% 22 250% 7.20% 1.86 1.09 0 .36 100% 100% 23 250% 7.30% 1.89 1.86 1.11 J.96 1.03 0.48 0.50 0.67 0.69 0.82 0.96 0.27 0.32 60.10 140% 7.40% 1.91 1.82 1.14 0.97 1.05 0 .50 0.52 0.72 0.69 0.82 1.00 0.27 0.32 0.40 60.10 140% 100% 00% 24 250% 7.50% 1.93 1.77 1.16 0.98 1.07 1.63 0.50 0.54 0.77 0.66 0.79 1.01 0.27 0.32 0.40 60.1% 140%% 100% 100% 250% 7.60% 1.73 1.19 1.02 1.12 1.71 0.53 0.56 0.81 0.57 0.68 1.01 0.27 0.32 0.41 60.19 140% 100% 100% 26 250% 1.99 1.22 1.07 1.17 1.81 0.57 0.60 0.87 0.52 0.62 1.00 0.29 0.34 0.41 60.19 140% 100% 100% 250% 7.70% 7.80% 2.03 1.70 1.26 1.05 1.17 1.82 0.58 0.63 0.92 0.50 0.60 0.98 0 .31 0.37 0.43 60.1% 140% 100% 100% 28 250% 7.90% 2.08 1.71 1.30 1.03 1.15 1.81 0.62 0.66 0.99 0.47 0.56 0.98 0.33 0.39 0.46 60.19 140% 100% 100% 29 250% 8.00% 1.35 1.02 1.14 1.80 0.64 1.03 0.46 0.54 1.00 0.34 0.41 0.49 60.1% 140% 100% 00% 250% 2.19 1.78 1.40 1.01 1.13 1.80 0 .68 0.73 1.12 0.49 0.58 1.05 0.37 0.43 0.53 60.1% 140% 100% 100% 250% 8.10 0.72 0.77 1.19 0.52 0.62 1.13 0.41 0.48 0.57 60.19 140% 100% 100% 32 250% 8.20% 1.01 2.32 1.13 1.82 8.30% 1.04 1.15 1.87 0.76 0.57 0.68 0.47 0.56 0 .68 60.19 140% 100% 100% 33 250% 2.40 1.06 1.18 1.94 0.82 0.88 1.39 0.6 0.79 1.30 0.55 0.65 0 .82 60.19 140% 100% 100% 34 250% 8.409 2.51 1.65 1.09 1.21 2.00 0.89 0.97 1.53 0.77 0.92 1.41 0.74 0.95 60. 19 140% 100% 100% 35 250% 8.50% 0.63 2.64 1.76 1.15 1.28 2. 11 0.95 1.03 0.89 1.06 1.54 0.71 0.84 60.10 140% 100% 00% 36 250% 8.60% 2.80 1.89 1.20 1.34 2.23 1.03 1.11 1.79 1.01 1.20 1.69 0.78 0.92 1.30 60.1% 140% 100% 100% 37 250% 8.70% 8.80% 3.01 2.04 1.29 1.44 2.40 1.07 1.17 1.88 1.10 1.31 1.87 0.84 0.99 1.47 60.19 140% 100% 100% 38 8.90% 1.37 1.54 2.57 1.13 1.23 2.00 1.17 1.39 2. 12 0.86 1.02 1 .60 60.19 140% 100% 100% 39 250% 2 . 12 1.23 1.46 2 .38 0 .87 1.03 1.72 60.19 140% 100% 100% 40 250% 9.00 3.53 2.42 1.46 1.65 2.77 1.20 1.30 9. 10 2.64 1.58 1.79 3.03 1.27 1.38 2.26 1.30 1.55 2 .65 0.88 1.04 1.82 60.19 140% 100% 00% 41 243% 9 .209 4:17 1.73 1.96 3.33 1.48 2.43 1.65 2.90 1.04 1.93 60.19 140% 100% 00% 42 236% 9.30% 3.87 3.09 1.90 2.15 3.69 1.45 1.59 2.63 1.47 1.75 3 . 12 0 .8 8 1.04 2.04 60.1% 140% 100% 100% 43 229% 4.92 4.19 3.32 2.10 2.39 4.12 1.57 1.72 2.86 1.52 1.81 3 . 28 0.89 1.05 60.19 140% 100% 100% 44 222% 9.40 5.35 3 .56 2.33 2 .65 4.57 1.71 1.87 3 .13 1.57 1.87 0.91 1.07 60.10 140% 100% 100% 45 215% 9.50% 46 5.83 4.92 3.80 2.55 2.90 4.99 1.87 2.05 3.43 1.63 1.94 3 . 68 0.94 1.11 2.54 60.19 140% 100% 100% 46 209% 9.60 9.70% 47 6 .36 5.32 4.05 2.79 3.17 5.46 2.07 2.27 3.81 1.69 2.01 3.92 0.98 1.17 2.79 60.19 140% 100% 100% 47 203% 100% 197% 9.80 6.95 5.74 4.33 2.93 3.33 5.72 2.29 2.50 4.28 1.77 2.10 4.19 1.04 1.24 3.06 60.1% 140% 100% 48 191% 9.90% 7.60 6.21 4.63 3.09 3.52 6.02 2.53 2.78 4.81 1.86 2.20 4.49 1.12 1.33 3 . 36 60.19 140% 100% 100% 49 10.00% 8 . 32 6.71 4.96 3.32 3.76 6.45 2.81 3.08 5.39 1.97 2.33 4.84 1.21 1.45 3 .69 60.19 140% 100% 100% 50 185% 178% 10. 10 9.11 7.30 5.31 3.59 4.06 6.96 6.02 2.52 1.60 4.07 60.19 100% 3.12 2.12 5.23 1.32 140% 100% 51 3.96 7.66 3.47 3.79 6.71 2.30 2.76 5 .68 1.48 1.78 4.47 60.19 140% 100% 100% 52 171% 10.20% 9.96 7.96 5.7 4.47 1 36 3.85 1 20 2.48 6.17 2.00 60.1% 140 %% 100% 100% 53 8.45 1.65 4.93 164% 10.309 10.89 8.71 6.1 4.93 APLII TRADITIONAL WHOLE LIFE PARTICIPATING WHOLE LIFE CURRENT ASSUMPTION WHOLE LIFE UNIVERSAL LIFE TERM + Sum: 12.70% Ready Average: 6.35% Count: 2 71%
