New Comparibility Calculation

[Guest AJM 34]

I want to learn how to manually calculate a New Comparbility P/S formula.

Can anyone share with me a sample Excel Spreadsheet, or point me in the right direction as to how to go about a New Comparbility P/S formula.

Sincerely, AJM

[Tom Poje]

I will assume what you mean is "How do you calculate an E-Bar"

suppose a 60 year old receives a $40,000 and deposits that money in a bank at 8.5% interest.

The money would grow as follows

Age 61 = 40,000 * 1.085 = 43,400

Age 62 = 43,400 * 1.085 = 47,089

Age 63 = 47,089 * 1.085 = 51,091

Age 64 = 51,092 * 1.085 = 55,434

Age 65 = 55,434 * 1.085 = 60,146

So at retirement she will have a little over $60,000

Mathematically speaking, this could be written as

40,000 * (1.085^5) or contribution * 1.oI ^ (yrs to retirement) where I = interest between 7.5 and 8.5

in this example she had 5 years to retirement.

instead of taking a lump sum at age 65, she gets an annuity. the APR fo 1983 IAF at 8.5% is 115.39

so 60,146 / 115.39 = 521.24 this would be her monthly benefit for the rest of her life.

or put another way 521.24 * 12 = 6254.88 a year.

if she made 200,000 then what % of pay is that? simply 6254.88 / 200,000 or 3.127

that is her E-BAR.

[Guest AJM 34]

Thank you Tom for the very detailed response on how to calculate an E-Bar.

How did you come up with the APR for 1983 IAF at 8.5% as 115.39?

Can you tell me the table that you referenced?

Thank you, Anthony

[Tom Poje]

the Actuaries decided what APR values are. I think they make them up to suit their fancy.

the figure I gave was for a monthly annuity, I suppose sometimes it might be shown as 9.615 (1/12 of 115.39) if determining things on an annual basis.

maybe Mr. Preston or one of the other acuary gurus from the dark side of DB can describe.

[Mike Preston]

Very funny, Tom.

The calculation of an "annuity purchase rate" is not very complicated, but it does involve a fair amount of number crunching. Many people have spreadsheets that allow one to enter the mortality factors (the probability of death at each age) into a column on a spreadsheet and then, given a specific age and a specific interest rate, determine the appropriate annuity purchase rate.

Their spreadsheets will all require updating once the new rules which require multiple interest rates come into effect.

I have an Excel add-in that allows one to enter a simple formula into a cell and have that cell populated with an APR. Something like:

=slaapr(65,"83IAM-F",8.5%) and the cell is then populated with 115.38701 (you can round to two decimals if you prefer).

I talk about the add-in only to describe how I have implemented the generation of APR's for my firm and to note that there are other alternatives than the typical approach of including a mortality table in each workbook.