- This topic has 0 replies, 1 voice, and was last updated 1 year, 3 months ago by
.
-
Posts
-
-
Loop Up AND Interpolate Using Excel, EASY!
It is EASY to get Excel to lookup AND, where needed, perform linear interpolation, and what better way to show you how than with the following three shortened table examples from a pensions actuarial and a pensions claims department in the 1980s!
Example # 1
You have a table comprising the following ages next birthday: 50, 55 and 60 and corresponding deferred annuity rates: 0.5932, 0.7861 and 0.9723 respectively, where rates for ages next birthday which are in-between the tabulated ages next birthday are calculated using linear interpolation, and you want Excel to do the looking up of this table along with all the relevant calculations.
Firstly, put the calculation of age next birthday onto sheet 1 of the workbook. To do this, let cell A2 accommodate the input of the date of birth, let cell B2 accommodate the input of the relevant later date, and into cell C2, put the formula =DATEDIF(A2,B2,”Y”)+1
Put the headings: dob, relevant later date and age nb into cells: A1, B1 and C1 respectively if you want.
Lastly, put the table, the look up functions and the interpolation calculation onto sheet 2 of the workbook. To do this;
Put the heading, age nb, into cell A1 and the values: 50, 55 and 60 into cells: A2, A3 and A4 respectively. Notice that the values: 50, 55 and 60 go down column A in ascending order.
Next, put the heading, rate, into cell B1 and the values: 0.5932, 0.7861 and 0.9723 into cells: B2, B3 and B4 respectively.
Next, let cell C2 accommodate the input of age next birthday, found from the output of cell C2 in sheet 1.
Next, into cell D2, put 5, the uniform table step size.
Next, into cell E2, put the formula =LOOKUP(C2,A2:A4)
Next, into cell F2, put the formula =LOOKUP(C2,A2:A4,B2:B4)
Next, into cell G2, put the formula =LOOKUP(C2+D2,A2:A4,B2:B4)
Next, into cell H2, put the formula =F2+(C2-E2)*(G2-F2)/D2 The output of this cell, cell H2, gives the rate.
Put the headings age nb and rate into cells C1 and H1 respectively if you want.
EASY!
Example # 2
You have a table comprising the following ages in months: 696, 697, 698, 699, 700, 701, 702, 703, 704, 705, 706, 707, 708, 709, 710, 711, 712, 713, 714, 715, 716, 717, 718, 719 and 720 and corresponding early retirement factors: 0.909, 0.913, 0.916, 0.920, 0.924, 0.928, 0.931, 0.935, 0.939, 0.943, 0.946, 0.950, 0.954, 0.958, 0.962, 0.966, 0.970, 0.974, 0.978, 0.982, 0.986, 0.990, 0.994, 0.998 and 1 respectively, where factors for ages in months with part months, which will be the majority of ages, are calculated using linear interpolation, with days being made equal to 1/30 months, and you want Excel to do the looking up of this table along with all the relevant calculations.
Firstly, put the calculation of age in months onto sheet 1 of the workbook. To do this, let cell A2 accommodate the input of the date of birth, let cell B2 accommodate the input of the early retirement date, and into cell C2, put the formula =DATEDIF(A2,B2,”Y”)*12+DATEDIF(A2,B2,”YM”)+DATEDIF(A2,B2,”MD”)/30
Put the headings: dob, early retirement date and age in months into cells: A1, B1 and C1 respectively if you want.
Lastly, put the table, the look up functions and the interpolation calculation onto sheet 2 of the workbook. To do this;
Put the heading, age in months, into cell A1 and the values: 696, 697, 698, 699, 700, 701, 702, 703, 704, 705, 706, 707, 708, 709, 710, 711, 712, 713, 714, 715, 716, 717, 718, 719 and 720 into cells: A2, A3, A4, A5, A6, A7, A8, A9, A10, A11, A12, A13, A14, A15, A16, A17, A18, A19, A20, A21, A22, A23, A24, A25 and A26 respectively. Again, notice that the values: 696, 697, 698, 699, 700, 701, 702, 703, 704, 705, 706, 707, 708, 709, 710, 711, 712, 713, 714, 715, 716, 717, 718, 719 and 720 go down column A in ascending order.
Next, put the heading, factor, into cell B1 and the values: 0.909, 0.913, 0.916, 0.920, 0.924, 0.928, 0.931, 0.935, 0.939, 0.943, 0.946, 0.950, 0.954, 0.958, 0.962, 0.966, 0.970, 0.974, 0.978, 0.982, 0.986, 0.990, 0.994, 0.998 and 1 into cells: B2, B3, B4, B5, B6, B7, B8, B9, B10, B11, B12, B13, B14, B15, B16, B17, B18, B19, B20, B21, B22, B23, B24, B25 and B26 respectively.
Next, let cell C2 accommodate the input of age in months, found from the output of cell C2 in sheet 1.
Next, into cell D2, put 1, the uniform table step size.
Next, into cell E2, put the formula =LOOKUP(C2,A2:A26)
Next, into cell F2, put the formula =LOOKUP(C2,A2:A26,B2:B26)
Next, into cell G2, put the formula =LOOKUP(C2+D2,A2:A26,B2:B26)
Next, into cell H2, put the formula =F2+(C2-E2)*(G2-F2)/D2 The output of this cell, cell H2, gives the factor.
Put the headings age in months and factor into cells C1 and H1 respectively if you want.
EASY!
Example # 3
You have a table comprising the following times, in years, until normal retirement date: 0, 1, 2 and 3 and corresponding early retirement factors: 1, 0.9562, 0.8914 and 0.8473 respectively, where factors for times, in years, until normal retirement date with part years, which will be the majority of times, are calculated using linear interpolation, with days being made equal to 1/365 years, and you want Excel to do the looking up of this table along with all the relevant calculations.
Firstly, put the calculation of normal retirement date onto sheet 1 of the workbook. To do this, let cell A2 accommodate the input of the date of birth, let cell B2 accommodate the input of the normal retirement age, e.g. 60, and into cell C2, put the formula =DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2))
Put the headings: dob, normal retirement age and normal retirement date into cells: A1, B1 and C1 respectively if you want.
Secondly, put the calculation of time, in years, until normal retirement date onto sheet 2 of the workbook. To do this, let cell A2 accommodate the input of the early retirement date, let cell B2 accommodate the input of the normal retirement date, found from the output of cell C2 in sheet 1, and into cell C2, put the formula =DATEDIF(A2,B2,”Y”)+DATEDIF(A2,B2,”YD”)/365
Put the headings: early retirement date, normal retirement date and time, in years, until normal retirement date into cells: A1, B1 and C1 respectively if you want.
Lastly, put the table, the look up functions and the interpolation calculation onto sheet 3 of the workbook. To do this;
Put the heading, time, in years, until normal retirement date, into cell A1 and the values: 0, 1, 2 and 3 into cells: A2, A3, A4 and A5 respectively. Again, notice that the values: 0, 1, 2 and 3 go down column A in ascending order.
Next, put the heading, factor, into cell B1 and the values: 1, 0.9562, 0.8914 and 0.8473 into cells: B2, B3, B4 and B5 respectively.
Next, let cell C2 accommodate the input of time, in years, until normal retirement date, found from the output of cell C2 in sheet 2.
Next, into cell D2, put 1, the uniform table step size.
Next, into cell E2, put the formula =LOOKUP(C2,A2:A5)
Next, into cell F2, put the formula =LOOKUP(C2,A2:A5,B2:B5)
Next, into cell G2, put the formula =LOOKUP(C2+D2,A2:A5,B2:B5)
Next, into cell H2, put the formula =F2+(C2-E2)*(G2-F2)/D2 The output of this cell, cell H2, gives the factor.
Put the headings time, in years, until normal retirement date and factor into cells C1 and H1 respectively if you want.
EASY!
You see, it IS easy to get Excel to lookup AND, where needed, perform linear interpolation!
July 2023
-
- You must be logged in to reply to this topic.