An example of how Excel’s Goal Seek would have been handy back in the 1980s

[Richard Purvey]

Calculation of how much deferred single life pension, along with a deferred contingent widow/er’s pension equal to a desired fraction of this deferred single life pension, can be bought by a single premium, for a scheme member, using Excel’s Goal Seek.  Create the spreadsheet, making sure that it includes a cell for inputting the amount of the single premium to be used to buy deferred single life pension and that it then calculates the balance (which will buy deferred contingent widow/er’s pension) and that it then calculates the amounts of pensions bought.  Also, make sure that it has a cell with a formula which calculates deferred contingent widow/er’s pension bought divided by deferred single life pension bought.  And, finally, apply Goal Seek in the following way; enter the cell reference of the cell with the formula which calculates deferred contingent widow/er’s pension bought divided by deferred single life pension bought, into the “set cell” box and enter the desired fraction that the deferred contingent widow/er’s pension should be of the deferred single life pension, into the “to value” box and enter the cell reference of the cell for inputting the amount of the single premium to be used to buy deferred single life pension, into the “by changing cell” box.

[Author]

Posts