The spreadsheet serves two main purposes for migrations: i) It is our calculation specification. The system development team code our main administration system, so that it matches the results provided by the spreadsheet. ii) It is used for testing. The testing team will use it to make sure that the results calculated by the administration system are correct. Given those requirements, it is important that: a) The inputs to the spreadsheet are clearly defined and are things that will be data items on the system i.e. they should not need to be calculated. b) The Excel code should be easy to follow. We prefer to use cell formulae rather than Visual Basic. c) The outputs must fulfil the requirements. This test is designed to be reasonably realistic and allow the candidate to demonstrate their skills. The Test The Excel Spreadsheet calculation that we would like you to build is a unit linked charge for the cost of providing life cover. There are two products Flexible Protecting Plan and Universal Cover Plan that have this feature. The system product codes are LU_FPP_TT and LU_UPP_TT. They are both unit-linked products that can invest in a maximum of five funds. The product descriptions for both say that, The death benefit provided by a policy is the greater of the cash value of units and the guaranteed minimum death benefit. In order to pay for this life cover a charge equal to the probability of a claim in a month multiplied by the excess of the guaranteed death benefit over the current fund value.. From examining policy data, we have found that there are a mixture of single life, joint life first death and joint life last survivor policies. The legacy IT team have extracted two mortality tables that they say are used in the calculation (see below). This calculation will be called monthly for each policy in these two products. The outputs required are: 1) The cash value of the charge. 2) The number of units to be deducted from each fund holding attached to the policy.

UPP life cover rate in excel are: SEX ANB RATE SEX ANB RATE F 1 0.00043096 F 2 0.00043096 F 3 0.00043096 F 4 0.00043096 F 5 0.00043096 F 6 0.00043096 F 7 0.00043096 F 8 0.00043096 F 9 0.00043096 F 10 0.00043096 F 11 0.00043096 F 12 0.00043096 F 13 0.00043096 F 14 0.00043096 F 15 0.00043096 F 16 0.00043096 F 17 0.00043096 F 18 0.00043096 F 19 0.00043422 F 20 0.00043786 F 21 0.00044255 F 22 0.00044694 F 23 0.00045307 F 24 0.00045914 F 25 0.0004669 F 26 0.00047548 F 27 0.00048575 F 28 0.00049714 F 29 0.00051078 F 30 0.00052611 F 31 0.00054394 F 32 0.00056402 F 33 0.00058773 F 34 0.00061487 F 35 0.00064463 F 36 0.00067849 F 37 0.00071742 F 38 0.00076202 F 39 0.00081287 F 40 0.00087222 F 41 0.00093955 F 42 0.00101737 F 43 0.00110599 F 44 0.00120828 F 45 0.00132419 F 46 0.00145744 F 47 0.00160954 F 48 0.00178268 F 49 0.00197922 F 50 0.00220396 F 51 0.00245835 F 52 0.00274756 F 53 0.00307553 F 54 0.00344661 F 55 0.00386674 F 56 0.00434121 F 57 0.00487671 F 58 0.0054799 F 59 0.00615941 F 60 0.0069232 F 61 0.00778121 F 62 0.00874328 F 63 0.00982165 F 64 0.01102835 F 65 0.01237624 F 66 0.01388135 F 67 0.0155594 F 68 0.01742749 F 69 0.01950514 F 70 0.02181288 F 71 0.02437324 F 72 0.02721004 F 73 0.03034975 F 74 0.03381987 F 75 0.03765086 F 76 0.04187596 F 77 0.04652902 F 78 0.05164782 F 79 0.05727201 F 80 0.06344525 F 81 0.07021091 F 82 0.0776198 F 83 0.08572101 F 84 0.09456984 F 85 0.1042128 F 86 0.11430205 F 87 0.12502235 F 88 0.13655787 F 89 0.14894902 F 90 0.16223437 F 91 0.17645263 F 92 0.19163993 F 93 0.20783104 F 94 0.22505668 F 95 0.24334527 F 96 0.2627199 F 97 0.2831978 F 98 0.30478995 F 99 0.32749836 F 100 0.35131584 F 101 0.37787968 F 102 0.40548755 F 103 0.43172061 F 104 0.45664239 F 105 0.48031009 F 106 0.50277828 F 107 0.52409593 F 108 0.54430912 F 109 0.5634598 F 110 0.58158428 F 111 0.59871508 F 112 0.6148791 F 113 0.63009655 F 114 0.64438004 F 115 0.65773114 F 116 0.67013759 F 117 0.68156232 F 118 0.69192785 F 119 0.7010638 F 120 1 M 1 0.00043096 M 2 0.00043096 M 3 0.00043096 M 4 0.00043096 M 5 0.00043096 M 6 0.00043096 M 7 0.00043096 M 8 0.00043096 M 9 0.00043096 M 10 0.00043096 M 11 0.00043096 M 12 0.00043096 M 13 0.00043096 M 14 0.00043096 M 15 0.00043096 M 16 0.00043096 M 17 0.00043096 M 18 0.00043096 M 19 0.00043422 M 20 0.00043786 M 21 0.00044255 M 22 0.00044694 M 23 0.00045307 M 24 0.00045914 M 25 0.0004669 M 26 0.00047548 M 27 0.00048575 M 28 0.00049714 M 29 0.00051078 M 30 0.00052611 M 31 0.00054394 M 32 0.00056402 M 33 0.00058773 M 34 0.00061487 M 35 0.00064463 M 36 0.00067849 M 37 0.00071742 M 38 0.00076202 M 39 0.00081287 M 40 0.00087222 M 41 0.00093955 M 42 0.00101737 M 43 0.00110599 M 44 0.00120828 M 45 0.00132419 M 46 0.00145744 M 47 0.00160954 M 48 0.00178268 M 49 0.00197922 M 50 0.00220396 M 51 0.00245835 M 52 0.00274756 M 53 0.00307553 M 54 0.00344661 M 55 0.00386674 M 56 0.00434121 M 57 0.00487671 M 58 0.0054799 M 59 0.00615941 M 60 0.0069232 M 61 0.00778121 M 62 0.00874328 M 63 0.00982165 M 64 0.01102835 M 65 0.01237624 M 66 0.01388135 M 67 0.0155594 M 68 0.01742749 M 69 0.01950514 M 70 0.02181288 M 71 0.02437324 M 72 0.02721004 M 73 0.03034975 M 74 0.03381987 M 75 0.03765086 M 76 0.04187596 M 77 0.04652902 M 78 0.05164782 M 79 0.05727201 M 80 0.06344525 M 81 0.07021091 M 82 0.0776198 M 83 0.08572101 M 84 0.09456984 M 85 0.1042128 M 86 0.11430205 M 87 0.12502235 M 88 0.13655787 M 89 0.14894902 M 90 0.16223437 M 91 0.17645263 M 92 0.19163993 M 93 0.20783104 M 94 0.22505668 M 95 0.24334527 M 96 0.2627199 M 97 0.2831978 M 98 0.30478995 M 99 0.32749836 M 100 0.35131584 M 101 0.37787968 M 102 0.40548755 M 103 0.43172061 M 104 0.45664239 M 105 0.48031009 M 106 0.50277828 M 107 0.52409593 M 108 0.54430912 M 109 0.5634598 M 110 0.58158428 M 111 0.59871508 M 112 0.6148791 M 113 0.63009655 M 114 0.64438004 M 115 0.65773114 M 116 0.67013759 M 117 0.68156232 M 118 0.69192785 M 119 0.7010638 M 120 1 FPP Mort rate in excel: GENDER SMOKER ANB CHARGES F N 1 0.00020138 F N 2 0.00020138 F N 3 0.00020138 F N 4 0.00020138 F N 5 0.00020138 F N 6 0.00020138 F N 7 0.00020138 F N 8 0.00020138 F N 9 0.00020138 F N 10 0.00020138 F N 11 0.00020138 F N 12 0.00020138 F N 13 0.00020138 F N 14 0.00020138 F N 15 0.00020138 F N 16 0.00020138 F N 17 0.00020138 F N 18 0.00020138 F N 19 0.00020700 F N 20 0.00021150 F N 21 0.00021825 F N 22 0.00022388 F N 23 0.00023175 F N 24 0.00023963 F N 25 0.00024863 F N 26 0.00025875 F N 27 0.00027113 F N 28 0.00028350 F N 29 0.00029813 F N 30 0.00031388 F N 31 0.00033188 F N 32 0.00035213 F N 33 0.00037463 F N 34 0.00039938 F N 35 0.00042188 F N 36 0.00044325 F N 37 0.00046800 F N 38 0.00049500 F N 39 0.00052538 F N 40 0.00056025 F N 41 0.00059850 F N 42 0.00064238 F N 43 0.00069075 F N 44 0.00074588 F N 45 0.00080775 F N 46 0.00087750 F N 47 0.00095625 F N 48 0.00104400 F N 49 0.00114188 F N 50 0.00125325 F N 51 0.00137813 F N 52 0.00151763 F N 53 0.00167513 F N 54 0.00185288 F N 55 0.00205200 F N 56 0.00227475 F N 57 0.00252675 F N 58 0.00280913 F N 59 0.00312638 F N 60 0.00348300 F N 61 0.00388463 F N 62 0.00433463 F N 63 0.00484088 F N 64 0.00541013 F N 65 0.00604913 F N 66 0.00676688 F N 67 0.00757350 F N 68 0.00848025 F N 69 0.00949725 F N 70 0.01064025 F N 71 0.01192388 F N 72 0.01336500 F N 73 0.01498275 F N 74 0.01679738 F N 75 0.01883475 F N 76 0.02112075 F N 77 0.02368463 F N 78 0.02656013 F N 79 0.02978213 F N 80 0.03339450 F N 81 0.03744000 F N 82 0.04197038 F N 83 0.04703963 F N 84 0.05271075 F N 85 0.05905013 F N 86 0.06613088 F N 87 0.07403625 F N 88 0.08285063 F N 89 0.09267188 F N 90 0.10360125 F N 91 0.11575013 F N 92 0.12923325 F N 93 0.14417663 F N 94 0.16070625 F N 95 0.17895600 F N 96 0.19905975 F N 97 0.22114800 F N 98 0.24535013 F N 99 0.27178425 F N 100 0.30055163 F N 101 0.33240038 F N 102 0.36452925 F N 103 0.39494925 F N 104 0.42374588 F N 105 0.45099900 F N 106 0.47678175 F N 107 0.50116275 F N 108 0.52420275 F N 109 0.54596025 F N 110 0.56648588 F N 111 0.58582575 F N 112 0.60401813 F N 113 0.62109450 F N 114 0.63707625 F N 115 0.65197350 F N 116 0.66577950 F N 117 0.67846050 F N 118 0.68993775 F N 119 0.70003238 F N 120 1.00000000 F Y 1 0.00028125 F Y 2 0.00028125 F Y 3 0.00028125 F Y 4 0.00028125 F Y 5 0.00028125 F Y 6 0.00028125 F Y 7 0.00028125 F Y 8 0.00028125 F Y 9 0.00028125 F Y 10 0.00028125 F Y 11 0.00028125 F Y 12 0.00028125 F Y 13 0.00028125 F Y 14 0.00028125 F Y 15 0.00028125 F Y 16 0.00028125 F Y 17 0.00028125 F Y 18 0.00028125 F Y 19 0.00028463 F Y 20 0.00028800 F Y 21 0.00029250 F Y 22 0.00029813 F Y 23 0.00030375 F Y 24 0.00031050 F Y 25 0.00031950 F Y 26 0.00032963 F Y 27 0.00034088 F Y 28 0.00035438 F Y 29 0.00037013 F Y 30 0.00038813 F Y 31 0.00040950 F Y 32 0.00043313 F Y 33 0.00046238 F Y 34 0.00049500 F Y 35 0.00053325 F Y 36 0.00057713 F Y 37 0.00062775 F Y 38 0.00068625 F Y 39 0.00075263 F Y 40 0.00083025 F Y 41 0.00091800 F Y 42 0.00101925 F Y 43 0.00113513 F Y 44 0.00126788 F Y 45 0.00141863 F Y 46 0.00159188 F Y 47 0.00178763 F Y 48 0.00201150 F Y 49 0.00226463 F Y 50 0.00255263 F Y 51 0.00287775 F Y 52 0.00324675 F Y 53 0.00366413 F Y 54 0.00413325 F Y 55 0.00466425 F Y 56 0.00526050 F Y 57 0.00593100 F Y 58 0.00668363 F Y 59 0.00752738 F Y 60 0.00847125 F Y 61 0.00952763 F Y 62 0.01070663 F Y 63 0.01202175 F Y 64 0.01348538 F Y 65 0.01511213 F Y 66 0.01691888 F Y 67 0.01892138 F Y 68 0.02113763 F Y 69 0.02358788 F Y 70 0.02629125 F Y 71 0.02927138 F Y 72 0.03254850 F Y 73 0.03614963 F Y 74 0.04009838 F Y 75 0.04442175 F Y 76 0.04914675 F Y 77 0.05430375 F Y 78 0.05991975 F Y 79 0.06602738 F Y 80 0.07265588 F Y 81 0.07983563 F Y 82 0.08760038 F Y 83 0.09597825 F Y 84 0.10500075 F Y 85 0.11469825 F Y 86 0.12509888 F Y 87 0.13622963 F Y 88 0.14811525 F Y 89 0.16077825 F Y 90 0.17423663 F Y 91 0.18850838 F Y 92 0.20360363 F Y 93 0.21953025 F Y 94 0.23629050 F Y 95 0.25388100 F Y 96 0.27229388 F Y 97 0.29151225 F Y 98 0.31151475 F Y 99 0.33227325 F Y 100 0.35374950 F Y 101 0.37894388 F Y 102 0.40642200 F Y 103 0.43253888 F Y 104 0.45735525 F Y 105 0.48092850 F Y 106 0.50331263 F Y 107 0.52455488 F Y 108 0.54470025 F Y 109 0.56379150 F Y 110 0.58186238 F Y 111 0.59894663 F Y 112 0.61506788 F Y 113 0.63024863 F Y 114 0.64450013 F Y 115 0.65782350 F Y 116 0.67020525 F Y 117 0.68160938 F Y 118 0.69195713 F Y 119 0.70107863 F Y 120 1.00000000 M N 1 0.00040838 M N 2 0.00040838 M N 3 0.00040838 M N 4 0.00040838 M N 5 0.00040838 M N 6 0.00040838 M N 7 0.00040838 M N 8 0.00040838 M N 9 0.00040838 M N 10 0.00040838 M N 11 0.00040838 M N 12 0.00040838 M N 13 0.00040838 M N 14 0.00040838 M N 15 0.00040838 M N 16 0.00040838 M N 17 0.00040838 M N 18 0.00040838 M N 19 0.00041063 M N 20 0.00041400 M N 21 0.00041850 M N 22 0.00042188 M N 23 0.00042750 M N 24 0.00043200 M N 25 0.00043875 M N 26 0.00044550 M N 27 0.00045338 M N 28 0.00046238 M N 29 0.00047363 M N 30 0.00048488 M N 31 0.00049838 M N 32 0.00051300 M N 33 0.00052988 M N 34 0.00055013 M N 35 0.00057150 M N 36 0.00059738 M N 37 0.00062550 M N 38 0.00065813 M N 39 0.00069413 M N 40 0.00073575 M N 41 0.00078300 M N 42 0.00083700 M N 43 0.00089775 M N 44 0.00096750 M N 45 0.00104513 M N 46 0.00113400 M N 47 0.00123525 M N 48 0.00135000 M N 49 0.00147938 M N 50 0.00162788 M N 51 0.00179438 M N 52 0.00198450 M N 53 0.00219938 M N 54 0.00244350 M N 55 0.00272025 M N 56 0.00303413 M N 57 0.00338963 M N 58 0.00379238 M N 59 0.00425025 M N 60 0.00476775 M N 61 0.00535500 M N 62 0.00601988 M N 63 0.00677363 M N 64 0.00762863 M N 65 0.00859500 M N 66 0.00969075 M N 67 0.01093163 M N 68 0.01233563 M N 69 0.01392525 M N 70 0.01572413 M N 71 0.01775925 M N 72 0.02006100 M N 73 0.02266313 M N 74 0.02560388 M N 75 0.02892600 M N 76 0.03267900 M N 77 0.03691463 M N 78 0.04169250 M N 79 0.04708013 M N 80 0.05315063 M N 81 0.05998388 M N 82 0.06767213 M N 83 0.07631213 M N 84 0.08601075 M N 85 0.09684900 M N 86 0.10747013 M N 87 0.11845125 M N 88 0.13037400 M N 89 0.14329125 M N 90 0.15725475 M N 91 0.17231400 M N 92 0.18851738 M N 93 0.20590763 M N 94 0.22451963 M N 95 0.24438600 M N 96 0.26552700 M N 97 0.28795838 M N 98 0.31167900 M N 99 0.33667763 M N 100 0.36293175 M N 101 0.39010388 M N 102 0.41649075 M N 103 0.44159400 M N 104 0.46547100 M N 105 0.48817350 M N 106 0.50974875 M N 107 0.53024175 M N 108 0.54969525 M N 109 0.56814525 M N 110 0.58562550 M N 111 0.60216300 M N 112 0.61778363 M N 113 0.63250313 M N 114 0.64633163 M N 115 0.65926913 M N 116 0.67130100 M N 117 0.68239013 M N 118 0.69245888 M N 119 0.70133850 M N 120 1.00000000 M Y 1 0.00076388 M Y 2 0.00076388 M Y 3 0.00076388 M Y 4 0.00076388 M Y 5 0.00076388 M Y 6 0.00076388 M Y 7 0.00076388 M Y 8 0.00076388 M Y 9 0.00076388 M Y 10 0.00076388 M Y 11 0.00076388 M Y 12 0.00076388 M Y 13 0.00076388 M Y 14 0.00076388 M Y 15 0.00076388 M Y 16 0.00076388 M Y 17 0.00076388 M Y 18 0.00076388 M Y 19 0.00076613 M Y 20 0.00076950 M Y 21 0.00077288 M Y 22 0.00077625 M Y 23 0.00078188 M Y 24 0.00078750 M Y 25 0.00079425 M Y 26 0.00080213 M Y 27 0.00081225 M Y 28 0.00082350 M Y 29 0.00083700 M Y 30 0.00085388 M Y 31 0.00087300 M Y 32 0.00089550 M Y 33 0.00092250 M Y 34 0.00095400 M Y 35 0.00099113 M Y 36 0.00103500 M Y 37 0.00108675 M Y 38 0.00114638 M Y 39 0.00121613 M Y 40 0.00129825 M Y 41 0.00139275 M Y 42 0.00150300 M Y 43 0.00163013 M Y 44 0.00177863 M Y 45 0.00194850 M Y 46 0.00214538 M Y 47 0.00237263 M Y 48 0.00263250 M Y 49 0.00293063 M Y 50 0.00327263 M Y 51 0.00366300 M Y 52 0.00410850 M Y 53 0.00461588 M Y 54 0.00519188 M Y 55 0.00584550 M Y 56 0.00658688 M Y 57 0.00742388 M Y 58 0.00836775 M Y 59 0.00943088 M Y 60 0.01062675 M Y 61 0.01196663 M Y 62 0.01346738 M Y 63 0.01514475 M Y 64 0.01701450 M Y 65 0.01909575 M Y 66 0.02140763 M Y 67 0.02397038 M Y 68 0.02680425 M Y 69 0.02993288 M Y 70 0.03337875 M Y 71 0.03716550 M Y 72 0.04131900 M Y 73 0.04586400 M Y 74 0.05082638 M Y 75 0.05623088 M Y 76 0.06210563 M Y 77 0.06847313 M Y 78 0.07536150 M Y 79 0.08279213 M Y 80 0.09079088 M Y 81 0.09937575 M Y 82 0.10856925 M Y 83 0.11838600 M Y 84 0.12884288 M Y 85 0.13994888 M Y 86 0.15171300 M Y 87 0.16413975 M Y 88 0.17722800 M Y 89 0.19097325 M Y 90 0.20536650 M Y 91 0.22039313 M Y 92 0.23603400 M Y 93 0.25226325 M Y 94 0.26905275 M Y 95 0.28636650 M Y 96 0.30416513 M Y 97 0.32240250 M Y 98 0.34103138 M Y 99 0.35999663 M Y 100 0.37924313 M Y 101 0.40199513 M Y 102 0.42723113 M Y 103 0.45126338 M Y 104 0.47414588 M Y 105 0.49592250 M Y 106 0.51664050 M Y 107 0.53633813 M Y 108 0.55505363 M Y 109 0.57282075 M Y 110 0.58966875 M Y 111 0.60562350 M Y 112 0.62070638 M Y 113 0.63493088 M Y 114 0.64830600 M Y 115 0.66082838 M Y 116 0.67248450 M Y 117 0.68323388 M Y 118 0.69300113 M Y 119 0.70162088 M Y 120 1.00000000

1. How is the guaranteed minimum death benefit calculated. Is it like a 2% increase on the original sum assured yearly or is there another way to calculate it? The guaranteed minimum death benefit is set at the commencement of the policy by the policyholder. It is a fixed amount for each policy for charge purposes and is held as a policy data item. 2. Is there any information about the five funds mentioned? Do you have any unit prices of the funds as without these I cannot calculated the number of units to be deducted from each fund holding. Having the unit prices will also allow me to understand how the fund value grows in comparison to the guarantee therefore giving me an idea if the guarantee with bite or not. The funds are labelled Managed, Equity, Property, Gilt and Cash. The admin system will hold a history of unit prices. You can request the unit price applicable at the charge date for each fund as an input into the calculation. 3. If the guarantee death benefit is greater than the current fund value then there's an extra charge the customer will need to pay. How is this calculated? E.g. if the guarantee was 5,000 greater than the fund value would a proportion of that 5,000 (say 2%) be added to the charge? Or is there another way to work it out? From the policy conditions quoted in the task, . a charge equal to the probability of a claim in a month multiplied by the excess of the guaranteed death benefit over the current fund value. This quote describes the calculation its the expected cost to the company of a claim value each month. NB. The company will keep the value of units in the event of a claim if the guaranteed minimum is paid out. 4. Can I assume UDD (Uniform Distribution Death) for the mortality rates during each month? Yes.