Lump Sum Calculation Sanity Check

[Andy the Actuary]

Being an independent cuss, I developed a generalized spreadsheet (which I'd be happy to share) to calculate lump sums under PPA 2006. Since the use of commutation columns has been outlawed in the state of Missouri under penalty of death, I used first principles. I figure most of you are wiser than I and have purchased software that will accommodate the new law. In any event, if someone else has a mechanism in place, I would appreciate a sanity check on my calculation mechanism.

The calculation example uses the 2008 applicable mortality table and the October 2007 segment rates: 4.85%, 5.02%, and 5.09%.

I am valuing a deferred annuity to participant who is currently age 61 and 10 months of $1 payable monthly at age 65 and 4 months on a 120 months certain and life basis. There is no death benefit if the participant dies during the deferral period.

The factor I developed is 121.16.

Anyone come within a tolerable percentage?

Any help is most certainly appreciated.

[Kimberly S]

I thought Missouri had put the death penalty on hold of late. I guess that just applies to murderers and such, not those of us tryin' to make a living.

[Effen]

Andy, you might not be aware but commutation factors are back on the EA exams! The ban has been lifted!

That said, I haven't thought about lump sum factors yet, but I will soon.

[Mike Preston]

I get 121.2147781, but I'm not sure I've spent enough time on it to ensure that rounding isn't an issue somewhere it shouldn't be.

[Mike Preston]

And, yes, I used commutation functions. This is one (?) FUGLY formula.

[Guest SVP]

I get 121.244028 on our system but interpolation on a deferred benefit with a 10 year certain and segmented rates can produce some differences depending on how you do it. You picked a tough one to start with!

[Andy the Actuary]

Thank you to Messrs. Preston and SVP.

We are all sitting in the same ballpark watching the same game unless the IRS specifies computational details. The difference between 121.16 and 121.24 is .07% if the 121.24 is the golden number. Rounding and method differences seem inconsequential in light of using stale interest rates (lookback month of August 2007 for distribution in December 2008).

I've always used commutation columns (thank you Messrs. Spurgeon and Jordan). The 11/24 approximation will lead to about .05% difference relative to calculating using first principles. But to quote Mr. P, the computation becomes FUGLY (real nasty) when using commutation columns. Effen, haven't written an actuarial exam since 1973 when gasoline contained lead but had understood that commutation columns disappeared. Have they also resurrected columnar yellow paper spreadsheets that had big chuncks of wood floating around?

Thanks for all help.

[flosfur]

..................

The calculation example uses the 2008 applicable mortality table and the October 2007 segment rates: 4.85%, 5.02%, and 5.09%.

I am valuing a deferred annuity to participant who is currently age 61 and 10 months of $1 payable monthly at age 65 and 4 months on a 120 months certain and life basis. There is no death benefit if the participant dies during the deferral period.

The factor I developed is 121.16.

Anyone come within a tolerable percentage?

Any help is most certainly appreciated.

How about doing the "sanity check" for integral ages. For current age of 61 and life annuity payment starting from 65 with 10 year certain, I come up with 121.47949290 from first principles.

For 62 and 65, the factor is 130.98857426 and for 55 and 65, the factor is 77.60338758.

What do you come up with for these ages?

[Andy the Actuary]

Dear Flosfur:

I get 61->65: 118.81

62->65: 125.59

55->65: 86.32

using 2008 mortality table and interest Rates are 4.85%, 5.02%, and 5.09%.

Note, using an old fortran program (11/24 approximation), the new mortality table, and constant interest rate of 4.85%, I get for 55->65 a factor of 89.57 and using a constant interest rate of 5.09%, a factor of 85.78. My result of 86.32 falls between these boundaries as one would expect.

Suggest you use your time-tested lump sum calculation system (whatever it is) to see if your getting an answer that fits within reasonable boundaries. If so, then one of us is off kilter somewhere. I'll be happy to email you my spreadsheet if that will help.

Thanks and regards,

Andy T. A.

[flosfur]

Dear Flosfur:

I get 61->65: 118.81

62->65: 125.59

55->65: 86.32

using 2008 mortality table and interest Rates are 4.85%, 5.02%, and 5.09%.

Note, using an old fortran program (11/24 approximation), the new mortality table, and constant interest rate of 4.85%, I get for 55->65 a factor of 89.57 and using a constant interest rate of 5.09%, a factor of 85.78. My result of 86.32 falls between these boundaries as one would expect.

Suggest you use your time-tested lump sum calculation system (whatever it is) to see if your getting an answer that fits within reasonable boundaries. If so, then one of us is off kilter somewhere. I'll be happy to email you my spreadsheet if that will help.

Thanks and regards,

Andy T. A.

I found the bug in my program - the N year certain value was being discounted back to the valuation age twice. When I developed my program a while back I only tested for life annuity without period certain.

Also, I was assuming no pre-ret mortality.

I am still testing my program (for life with certain period). For now, with pre-ret motality applied, monthly in advance payments (13/24 aprrox. adjustment) and interest rates of 4.85%/5.02%/5.09%, I have the following numbers:

55 => 65: 86.34255

61 => 65: 119.27845

62 => 65: 125.91942

65 => 65: 149.02968

For 55 => 65, at 4.85%, I get 89.5906, at 5% 87.2037 and at 5.09% 85.807765.

I cross checked the 4.85% & 5% numbers using the the commutation functions and got the same numbers as the cash flow method.

[Andy the Actuary]

Dear Flosfur:

Looks like now we are either both sane or both insane! The calculation is, indeed, a mess and as stated before, so totally unnecessary when you could be using stale interest rates. But, the arcane methodology now allows Congress to stand proudly and boast how they have acted to protect workers' pensions, at least for the short term while there are still DB plans. I never thought at my age that I would become a computer programmer!

Andy T. A.

P.S. At 65->65, I compute 148.99. I await with great joy the auditor who argues that his/her 149.03 is correct and my 148.99 is incorrect.

[tymesup]

Uh oh, I'm getting 147.998 at 65/65 for the 10 year certain and life.

Anybody else get 137.862 for the plain life annuity at 65/65?

[Andy the Actuary]

For immediate life annuity at 65 using 4.85%, 5.02%, and 5.09%, I get 143.44. Using 4.85% (and my old Fortran program), I get 145.61; using 5.09%, I get 142.66.

Hope this helps.

Andy T. A.

[flosfur]

For immediate life annuity at 65 using 4.85%, 5.02%, and 5.09%, I get 143.44. Using 4.85% (and my old Fortran program), I get 145.61; using 5.09%, I get 142.66.

Hope this helps.

Andy T. A.

I get 143.50962, 145.60777 and 142.6593.

So Andy, for the segment rates APR, we differ by the 0.07 which is too great to be acceptable to the actuaries.

Just in case anyone is interested in additional checking, here are my APRs for immediate life only annuity at various ages based on 4.85%/5.02%/5.09% and 2008 Applicable Mortality.

Val Age Cash Flow

x APRx

55 176.8135674

56 173.8480394

57 170.8073878

58 167.6760465

59 164.4532921

60 161.1373470

61 157.7359800

62 154.2679516

63 150.7331930

64 147.1541028

65 143.5096129

66 139.8082081

67 136.0748258

68 132.2814661

69 128.4107939

70 124.4764811

[flosfur]

..... now allows Congress to stand proudly and boast how they have acted to protect workers' pensions, at least for the short term while there are still DB plans. I never thought at my age that I would become a computer programmer!

Andy T. A.

And what's with the annually changing Applicable and valuation mortality tables! Good God Congress, what were you thinking!

[Mike Preston]

For immediate life annuity at 65 using 4.85%, 5.02%, and 5.09%, I get 143.44. Using 4.85% (and my old Fortran program), I get 145.61; using 5.09%, I get 142.66.

For immediate life annuity at 65 using 4.85%, 5.02%, and 5.09%, I get 143.51 (5 decimal place rounding: 143.50854). Using 4.85% (and my old Fortran program), I get 145.61 (same); using 5.09%, I get 142.66 (same). Might as well round out the calculations, using 5.02% I get 143.51 (five decimal place rounding: 143.50961).

[Mike Preston]

Just in case anyone is interested in additional checking, here are my APRs for immediate life only annuity at various ages based on 4.85%/5.02%/5.09% and 2008 Applicable Mortality.

I concur to 3 decimal places (it may be consistent with my program to more decimal places than 3, I just stopped checking at 3 decimal places).

[abanky]

For immediate life annuity at 65 using 4.85%, 5.02%, and 5.09%, I get 143.51 (5 decimal place rounding: 143.50854). Using 4.85% (and my old Fortran program), I get 145.61 (same); using 5.09%, I get 142.66 (same). Might as well round out the calculations, using 5.02% I get 143.51 (five decimal place rounding: 143.50961).

how are you calculating the immediate life annuity at 65 of 143.50854?

[Andy the Actuary]

Flosfur wrote, "So Andy, for the segment rates APR, we differ by the 0.07 which is too great to be acceptable to the actuaries."

I ask, "Who says so?" The .07 relative to your 143.51 is .05% which hardly wants to make one cry foul. Going back to an earlier discussion, this means the use of commutation columns instead of first principles is unacceptable.

I suspect you and I will have to agree to disagree on this one.

Thanks,

Andy T. A.

[Mike Preston]

My first principles worksheet and my commutation worksheet come up with the same number.

[Mike Preston]

abanky, I'm not sure what question you are asking. If you are asking what method I'm using to come up with the number, the answer is: "I do it two ways: an excel spreadsheet that has as its underlying formula a series of cells which replicate the formula that would be used based on commutation columns and an excel spreadsheet that caculates the present value based on first principles (discounting each monthly payment)". Does that answer your question?

[Guest mjb]

..... now allows Congress to stand proudly and boast how they have acted to protect workers' pensions, at least for the short term while there are still DB plans. I never thought at my age that I would become a computer programmer!

Andy T. A.

And what's with the annually changing Applicable and valuation mortality tables! Good God Congress, what were you thinking!

Full employment for actuaries?

[Andy the Actuary]

abanky, I'm not sure what question you are asking. If you are asking what method I'm using to come up with the number, the answer is: "I do it two ways: an excel spreadsheet that has as its underlying formula a series of cells which replicate the formula that would be used based on commutation columns and an excel spreadsheet that caculates the present value based on first principles (discounting each monthly payment)". Does that answer your question?

My first principles spreadsheet does the old L(x+t/12)*v^(t/12), where L(x+t/12)=Lx-(t/12)*dx so will yield a different result from the 11/24 approximation. I ask, "Does it really matter?" Given that the law makes no specification, isn't either calculation method appropriate?

Also, how do you apply the 11/24 th approximation when the benefit changes mid year? Answer: With the utmost of difficulty.

[Mike Preston]

Did you mean that dx?

[Andy the Actuary]

Did you mean that dx?

Naah, that would be Dx. This was supposed to mean Lx - L(x+1) but I'm unsure how to show subscript/superscript on the bulletin board. In any event, dx meant the number dying during the year. Also, lx just doesn't look right, so I used Lx. Lx is number living during the year and not that arcane Lx that appeared in one of Jordan's most confusing chapters on demography.

Best,

Andy T. A.

[tuni88]

It's so very exciting to witness all this actuary banter. Once you girls have agreed on what's right/wrong/best, can it be put (if it's usable) on the internet where we can obtain a copy?

[Andy the Actuary]

It's so very exciting to witness all this actuary banter. Once you girls have agreed on what's right/wrong/best, can it be put (if it's usable) on the internet where we can obtain a copy?

We will never agree. Get two actuaries in the room and you get three opinions!

That's why the blog was entitled "sanity check." I never anticipated my numbers would be replicated. I was hoping others would come close which they did and which was sufficient to comfort moi that my methodology was on target.

[Mike Preston]

Let's see:

dx = Lx - L(x+1), which of course I agree with.

But I would write that as:

L_{x -} L_x+1

Third opinions welcome.

[Andy the Actuary]

Let's see:

dx = Lx - L(x+1), which of course I agree with.

But I would write that as:

L_{x -} L_x+1

Third opinions welcome.

[/sub]

Oh, you're the guy that voted for AUH₂O in '64. Thanks for letting me know I could do what I didn't think I could do!!!

[tuni88]

That "I'd be happy to share ..." comment in your original post meant what?

[Andy the Actuary]

That "I'd be happy to share ..." comment in your original post meant what?

It means I'd be happy to email you the beta-stage Lotus 1-2-3 spreadsheet I created. Send me your email address in a personal mesage by clicking on "Andy The Actuary."

The spreadsheet would be sent as a professional courtesy at no charge [i'm not selling software] with the understanding that it is being provided solely for purpose of comparing values with any systems you might use to determine lump sums and would not be intended or warranted for you to copy in whole or part or use for determining lump sum values in behalf of your clients or for any other purpose.

[HBActuary]

Dear Flosfur:

I get 61->65: 118.81

62->65: 125.59

55->65: 86.32

using 2008 mortality table and interest Rates are 4.85%, 5.02%, and 5.09%.

Note, using an old fortran program (11/24 approximation), the new mortality table, and constant interest rate of 4.85%, I get for 55->65 a factor of 89.57 and using a constant interest rate of 5.09%, a factor of 85.78. My result of 86.32 falls between these boundaries as one would expect.

Suggest you use your time-tested lump sum calculation system (whatever it is) to see if your getting an answer that fits within reasonable boundaries. If so, then one of us is off kilter somewhere. I'll be happy to email you my spreadsheet if that will help.

Thanks and regards,

Andy T. A.

Andy - I would like to see your spreadheet. If you want you can email to mrocco@earthlink.net or post here? I'm having trouble matching you guys and need to find where I'm going wrong...

Mary Ann

[tuni88]

It means I'd be happy to email you the beta-stage Lotus 1-2-3 spreadsheet I created. Send me your email address in a personal mesage by clicking on "Andy The Actuary."

The spreadsheet would be sent as a professional courtesy at no charge [i'm not selling software] with the understanding that it is being provided solely for purpose of comparing values with any systems you might use to determine lump sums and would not be intended or warranted for you to copy in whole or part or use for determining lump sum values in behalf of your clients or for any other purpose.

-------

Thanks. I tried that clicking bit and it didn't work. Please email to me, as a professional courtesy under the terms you list, at: munis4u2@yahoo.com. Lotus 1-2-3 is still around, huh?