The “Dxy” tab - Weebly



This Excel workbook solves an applied problem for an institution looking to value their liability on payments to individuals that were essentially life annuities. The payment amounts were level, EXCEPT for a discrete change in amount when the individual attained age 70. For married individuals, payments were to continue to be made until both the individual & spouse were deceased (a 100% joint and survivor annuity).The primary tabs are the “Assumption Inputs”, “Data”, & “Calc” and “Results”. The rest of the tabs are all used to be able to develop commutation function tables that are used to find the single & joint life actuarial present value factors. “Assumption Inputs”: There are 2 easily changeable inputs: the valuation date & the interest rateThe other critical assumption is the mortality table choice. The actual inputting of annual mortality rates is done on the “ax(12)” tab (the qx column).“Data”:Includes the information for the 29 individuals that are to be valued.“Calc”:The “Calc” tab brings in the information for the individuals, calculates their attained ages at the valuation date, and references the commutation function tables in order to be able to calculate the life & joint life annuity factors appropriate for the Pre age 70 and Post age 70 pieces to the payments. The value of each individual’s annuity is in the final column.“Results”Simply summarizes the liability of each individual and the value of the total liability (cell D36).The above provides the general overview and layout of the spreadsheet. Below I provide supplemental information to describe the development of the commutation function tables, followed by a few sections of even more rigorous description of the functions for those interested in the mutation Function tabsThe tables on these tabs are developed to generate the factors that allow the actuarial valuation of single & joint life annuities. The only inputs needed to generate them are an interest rate and mortality table. Tab “ax(12)” develops the single life annuity factor from the given mortality factors qx.The final column, a x (12) provides the single life annuity factor for a monthly annuity that commences at the given age (note the 12 in parentheses is a notation indicating a monthly payment factor, not indicating multiplication). This factor is all that would be needed in our scenario for a single person over 70. For individuals under 70, we need to make use of the columns N x (12) and D x columns to able to come up with different factors to develop the pre 70 and post 70 payments. The “Calc” tab therefore references all three of these columns to value the different pieces of the non-level annuities. See below “Definition of Functions on tab ‘ax(12)’” and “Formula Examples for Single Individuals” for definitions of the commutation functions and specifics on how they may be used to derive present value factors.The remaining tabs are all in place to deal with the joint contingent piece of the annuity for married individuals. These are fundamentally created in a similar fashion as the single life annuity, but each commutation function requires its own sheet to handle the fact that there are two individuals to account for. See “Contingent Commutation Functions tabs” for a detailed explanation of the derivation of these present value factors.Definition of Functions on tab “ax(12)”qx = probability of death occurring in the year from age x to x+1lx+1= lx*1- qxDX = lx(1+i)Nx= t= 0∞Dx+t (infinity in this example is effectively age 110)Nx12 = Nx- 1124*Dxax12 = Nx(12)DxFormula Examples for Single Individuals:Present Value (PV) factor for payments to 72 year old individual:= a7212 = N72(12)D72PV factor for 64 year old individual for the pre age 70 payments:= N64(12) - N70(12)D64PV factor for someone age 64 for the post age 70 payments:= N70(12)D64Contingent Commutation Functions tabsThe “lxy” tab…. define lxy as..lxy= lx*lyThe sheet then just multiplies the lx column by itself to get a big matrix of all possible combinations of two ages between 15 and 110. The products are scaled down by k= 1/1000000 to make them manageable. k is irrelevant because it will just factor out later.The “Dxy” tab…. define Dxy as..Dxy= 11+ix+y2*lxyA Dxy matrix can therefore be created from the lxy values on the “lxy” tab, an interest rate, and ages.The “Dxy_2” tab… just a rearrangement of the “Dxy” tab so that it is easier to sum appropriately (to get Nxy) later. The column headers now represent the difference in age between the two individuals.The first column under 0 will have:Row 1 D15,15 Row 2 D16,16 And so on down to D110,110 (these were along a diagonal on the “Dxy” tab, but now are positioned vertically down the column).The second column, headed with a 1 is Row 1 D16,15 Row 2 D17,16down to D110,109 (again, these entries were diagonally positioned on “Dxy” tab).As stated above, the column headings are now representing the age difference (stopping at the extreme case of 35). Also, half of the matrix can be left empty, since Dxy = Dyx, (as can be seen on the “Dxy” tab which is symmetric diagonally).The “Nxy” tab …define Nxy as:Nxy= t= 0∞Dx+t , y+t For example:N70,67 = D70,67 + D71,68 + D72,69 + D73,70 ….On the “Nxy” tab, N70,67 is found in cell G61. Again, as with the “Dxy_2” matrix, the columns represent difference between ages of two individuals. The highest age of the two would default as the “x” age.Some further examples:Cell D6 is N15,15Cell D7 is N16,16Cell H11 is N20,16The “Nxy(12)” tab….. converts annual factors to monthly factors:Nxy12 = Nxy- 1124*DxyThe “axy(12)” tab……axy(12) is defined as:axy12 = Nxy(12)DxyThis represents the present value factor where payment is conditional on BOTH individuals surviving. But the object under consideration is to obtain the factor for the condition where EITHER survives. To find the factor for this condition (also called a 100% joint and survivor annuity) we must add each individual’s independent annuity factor and then subtract the joint survival factor: ax(12)+ay(12)- axy12 This follows from basic probability that for two independent events A and BPA∪B= PA+ PB- PA*PBEach part of the above expression is broken out for married individuals on the “Calc” tab. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download