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

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.

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

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.

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.