Excel’s Goal Seek examples for pensions actuarial/claims calculations

[Richard Purvey]

Four Types of Pension Actuarial/Claims Calculation and How To Do These Using Excel’s Goal Seek Facility

Calculation of the amount of single life pension (SLP) that should be converted into a contingent widow’s or widower’s pension (CWP) to result in this CWP being x times the remaining amount of SLP using excel’s goal seek facility. Put the initial amount of SLP, in £s p.a., into cell A2, the cost, in £s, of £1 p.a. of SLP into cell B2, the initial amount of CWP, in £s p.a., into cell C2 and the cost, in £s, of £1 p.a. of CWP into cell D2. Leave cell E2 blank (this will accommodate the input of the amount of the SLP, in £s p.a., that should be converted). Into cell F2, put the formula =A2-E2, into cell G2, put the formulae =C2+E2*B2/D2 and into cell H2, put the formula =G2/F2. And finally, apply goal seek, putting cell reference H2 into the “set cell” box, the value of x (e.g. 0.5) into the “to value” box and cell reference E2 into the “by changing cell” box.

Calculation of the amount of single life pension (SLP) that should be converted into a cash lump sum to result in this cash lump sum being x times the initial amount of SLP using excel’s goal seek facility. Put the initial amount of SLP, in £s p.a., into cell A2 and leave cell B2 blank (this will accommodate the input of the amount of the SLP, in £s p.a., that should be converted) and put the cost, in £s, of £1 p.a. of SLP into cell C2. Into cell D2, put the formula =B2*C2 and into cell E2, put the formula =D2/A2. And finally, apply goal seek, putting cell reference E2 into the “set cell” box, the value of x (e.g. 2.25) into the “to value” box and cell reference B2 into the “by changing cell” box.

Calculation of the amount of a second type of benefit (benefit type two) bought when another type of benefit (benefit type one) is completely converted to this benefit using excel’s goal seek facility. Put the initial amount of benefit type one, in £s, into cell A2 and the cost, in £s, of £1 of benefit type one into cell B2, and into cell C2, put the formula =A2*B2. Leave cell D2 blank (this will accommodate the input of the amount of benefit type two, in £s, bought) and put the cost, in £s, of £1 of benefit type two into cell E2, and into cell F2, put the formula =D2*E2. Into cell G2, put the formula =C2-F2. And finally, apply goal seek, putting cell reference G2 into the “set cell” box, 0 into the “to value” box and cell reference D2 into the “by changing cell” box.

Calculation of the amount of single life pension (SLP), along with a contingent widow’s or widower’s pension (CWP) equal to x times this SLP, bought by a cash lump sum using excel’s goal seek facility. Put the cash lump sum, in £s, into cell A2 and leave cell B2 blank (this will accommodate the input of the amount of SLP, in £s p.a., bought) and put the cost, in £s, of £1 p.a. of SLP into cell C2, and into cell D2, put the formula =B2*C2. Into cell E2, put the formula =x*B2 (for example =0.5*B2), and put the cost, in £s, of £1 p.a. of CWP into cell F2, and into cell G2, put the formula =E2*F2. Into cell H2, put the formula =D2+G2 and into cell I2, put the formula =A2-H2. And finally, apply goal seek, putting cell reference I2 into the “set cell” box, 0 into the “to value” box and cell reference B2 into the “by changing cell” box.

 

This would have been handy in the ’80s.

[Author]

Posts