Mathematicians Needed

[J.R.]

Alright you mathematical geniuses.......Has anybody figured out the proper formula to calculate as-earned commissions factoring in the following?

1) Commission percentage
2) Month on books (cancellation rate)
3) Renewals
4) Anything else I haven't thought about.

I've been sitting here for an hour playing around with my excel spreadsheets and I cannot figure out how to put everything together. My old finances books are of NO USE, so I'm hope we have a few smart gems out there nice enough to help me.

I want to do some what-if scenarios for 2008. Thanks.

 

[djs]

I'm confused. What is it you're trying to get to? Are you trying to figure out 'if I write X many policies, my as earned commission will be 'y'? And then trace this down for the year, taking into consideration a certain lapse ratio?

I guess my question is what does the as-earned portion and the lapse ratio portion have to do with the spreadsheet? Even if advanced, the lapse ratio should be figured in, since you'll get hit with the charge-back.

Dan

 

[J.R.]

I'm confused.  What is it you're trying to get to?  Are you trying to figure out 'if I write X many policies, my as earned commission will be 'y'?  And then trace this down for the year, taking into consideration a certain lapse ratio?

No, I already have that covered. Very simple formula

X ($10000AV X 4.3 weeks) * 20% (commission) /12 months

Example; $43000 *.20 / 12 = $614 (1st month) $1228 (2nd month same production). etc. etc.

This doesn't factor in people who cancel their polices, so I want to add in a 90% (best case) and 50% (worst case scenario) retention rate, combined with a renewal rate of 5-10% starting month 13, while adding that to the projected income. I can just multiply the commissions from the 2nd month to reflect a .50-.90 retention rate and complete that to reflect a retention rate on a month by month basis, but that tricks me up when factoring in renewals, etc.

I guess my question is what does the as-earned portion and the lapse ratio portion have to do with the spreadsheet? Even if advanced, the lapse ratio should be figured in, since you'll get hit with the charge-back.

Because I do not have software the break down everything so I track my goals and finances with a spreadsheet (see below). I haven't written any as-earned business this year, but since this will be changing in 08 I wanted to figure some things out first.

 

[007]

Interesting pre-class you got there.

 

[Martian]

I may have made what you need on a spreadsheet. It is a general estimation form. If you want a more detailed form you will have to define more variables. I checked the formulas, and I believe them to be solid. If someone checks it out and finds otherwise let me know. I was just trying it out. I can't get it to attach so please pm me with your e-mail address and I'll send it.

 

[Bob_The_Insurance_Guy]

Have you tried contacting the commissions department for each company, and speaking to them? They MIGHT know the answer to your question.

 

[Martian]

Anyone know why when I try to upload a 7KB zip file I get a failed error message, and why we can't upload .xls files?

 

[SME]

I can get my hubby to put this together for you. Can you send me a PM with exactly what you want? I'm sure that he can punch it out in a fraction of the time.