I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.

[Jerard]

Commission Tracker. A Sales/Deposit/Tax tracking Spreadsheet

​

Click Here if you're looking for LeadTracker​

In November of 2012 I was looking for a simple Excel spreadsheet to track my sales, commissions, deposits, and taxes and I couldn't find one I liked, so I made one. I was thinking that maybe some of you may find it useful so I uploaded it. Since then it has taken on "a life of it's own" whereas I keep it updated for the benefit of all of us.

Commission Tracker - Description
An Excel Spreadsheet that makes it easy to track your Sales, Persistency, Deposits, Taxes, Client Communications, and much more. Sales stats are available as Totals and Averages for the Month, Quarter, and Year To Date. All these stats are also sorted by Company. You can also tract 'after the sale' information in the Debt Management section. Set Up takes just a few minutes.

If you don't have Excel you can try using the free MS Office clone called OpenOffice.
Keep in mind some things may not work or look right by using OpenOffice.

If you are using a previous version of Commission Tracker you can copy over your data to any new version in just a few minutes.

Most of the posts below are based on previous versions and are no longer relevant.​

​

​

​

------------------------


Version 9.7 < < < Download Here!

What's new:
- Added a column that calculates the monthly commission you will receive on an "as earned" sale.




RECENT CHANGE LOG:
Version 9.6
-Added a YTD total for the Unpaid Advances column.
-Minor enhancements to the Notes sections

Version 9.5
-Updated to 2016
-Added an Issue Date column (Under the Correspondence Section).

Version 9.1
-Added an enhancement to the chargeback management column implemented in Version 9.0 (Column "V"), now if you have an issued sale but are never paid on that sale (no take, canceled, etc), then the chargeback savings amount changes to Zero (blank).

View previous changes here.
---------------------------------------------------------------------------------

 

[PaperHanger]

Re: I Made a Commission/Deposit/Tax Tracker that I'm Sharing.

pretty cool...do you reverse the Ins Co's modal formula to get the commission/advance?

 

[Jerard]

Re: I Made a Commission/Deposit/Tax Tracker that I'm Sharing.

pretty cool...do you reverse the Ins Co's modal formula to get the commission/advance?

No. The modal formula is for determining a clients premium amount. Your advance is what the insurance company has allowed you to receive for issued business. For example, Settlers has a maximum advance of 60% and that is a constant figure for each commission deposit.

 

[PaperHanger]

Re: I Made a Commission/Deposit/Tax Tracker that I'm Sharing.

Sorry I should have asked 'do you reverse the modal formula to get the annual premium' which is what the commission > advance are based on.

Looks like you have set the calculation for Annual Prem as "Monthly Premium x 12 = Annual Prem"
100 mo x 12 = 1200 annual premium

But when you see your actual report from the carrier, it won't say 1200.
I figure it this way (for example RNA)
(MonthlyPrem / modal factor) - 30pol fee = Annual Prem
(100mo / 0.87) - 30 = 1119.43 annual prem > this is what you're paid on, not 1200. Trying for convention? Only 1119 points go from this deal, not 1200.

You'd need to change the calculation based on which carrier you're using.

My commission is based on annual premium, regardles of payment method, and RNA policy fee is non-commisionable, no carrier pays commission on increases due to modal factor.

Anyways, I just wish the carriers would show me how much advance I stand to be charged back, as the service item comes in, instead of after the fact.

great spreadsheet, just don't let it throw you off, expecting more money, or more credits for convention or something, but the way it's written, at least you'll be oversaving for taxes, that's better than undersaving.

 

[Jerard]

Re: I Made a Commission/Deposit/Tax Tracker that I'm Sharing.

Thanks for pointing that out, I see the screw up now. I suppose there would be a way for it to auto calculate all the different companies and their policy fees, APs, and if they pay comm on them or not...but I think that would be more of a PIA then it's worth. Probably the best way is to leave it like it is and then change it once it hits the ins company's websites to the actual correct figures. I'll mess around with it some more during the next few days and see if I can make it better.

I see you're from GA....I hate your Falcons this year.

 

[obiswill]

Re: I Made a Commission/Deposit/Tax Tracker that I'm Sharing.

Jerad, thanks for the file. I will play with it and use it. Actually thinking about turning it into an app so I can run it off of my Tablet and Phone.

PaperHanger...good catch on the numbers

 

[Jerard]

Re: I Made a Commission/Deposit/Tax Tracker Spredsheet that I'm sharing.

Version 2 of Commission Tracker is has been uploaded to post 1. Instructions to the far right.

The changes mostly reflect input from PaperHanger (many thanks)

 

[Underwriter2Agent]

Thank you very much for sharing...this is very helpful

 

[blue_wynd]

Re: I Made a Commission/Deposit/Tax Tracker Spredsheet that I'm sharing.

Version 2 of Commission Tracker is has been uploaded to post 1. Instructions to the far right.

The changes mostly reflect input from PaperHanger (many thanks)

How to you add additional carriers? The dropdown only has rna, mon. etc.

 

[Jerard]

Re: I Made a Commission/Deposit/Tax Tracker Spredsheet that I'm sharing.

How to you add additional carriers? The dropdown only has rna, mon. etc.

See the original post, I added some instructions for that.