Over 150.000 insurance professionals and consumers come to the Insurance Forum every month. Click here to register for FREE


Go Back   Insurance Forums > Insurance Agents and Brokers Forum > Insurance Technology Forum

Reply to I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.     
Old 11-05-2012, 06:00 PM   #1
Guru
Jerard on I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
 
Join Date: Jun 2012
Posts:2,764
State: Jerard is an Insurance Agent from Florida
Jerard is a member of 1000 posts VIP club
I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing. Commission Tracker. A Sales/Deposit/Tax tracking Spreadsheet
Insurance Forums Sponsored Advertorial





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.
---------------------------------------------------------------------------------
Attached Images
File Type: jpg CT_Screenshot_1.jpg (44.8 KB, 22 views)
File Type: jpg CT_Screenshot_2.jpg (46.8 KB, 14 views)
------------------------------------

Last edited by Jerard; 04-11-2017 at 12:35 PM. Reason: Because...
  Reply With Quote to I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
Old 11-05-2012, 08:00 PM   #2
Guru
PaperHanger on I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
 
Join Date: Mar 2010
Posts:681
State: PaperHanger is an Insurance Agent from Georgia
I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing. 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?
------------------------------------
  Reply With Quote to I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
Old 11-06-2012, 08:45 AM   #3
Guru
Jerard on I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
 
Join Date: Jun 2012
Posts:2,764
State: Jerard is an Insurance Agent from Florida
Jerard is a member of 1000 posts VIP club
I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing. Re: I Made a Commission/Deposit/Tax Tracker that I'm Sharing. Jerard is the thread starter for: I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.

Originally Posted by PaperHanger View Post
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.
------------------------------------
  Reply With Quote to I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
Old 11-08-2012, 11:55 AM   #4
Guru
PaperHanger on I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
 
Join Date: Mar 2010
Posts:681
State: PaperHanger is an Insurance Agent from Georgia
I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing. 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.
------------------------------------
  Reply With Quote to I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
Old 11-08-2012, 06:06 PM   #5
Guru
Jerard on I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
 
Join Date: Jun 2012
Posts:2,764
State: Jerard is an Insurance Agent from Florida
Jerard is a member of 1000 posts VIP club
I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing. Re: I Made a Commission/Deposit/Tax Tracker that I'm Sharing. Jerard is the thread starter for: I Made a Commission/Deposit/Tax Tracker/Spreadsheet 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.
------------------------------------
  Reply With Quote to I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
Old 01-26-2013, 12:16 AM   #6
Expert
 
Join Date: Jan 2013
Posts:42
State: obiswill is an Insurance Agent from Undisclosed Location
I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing. 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
------------------------------------
  Reply With Quote to I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
Old 02-23-2013, 12:04 AM   #7
Guru
Jerard on I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
 
Join Date: Jun 2012
Posts:2,764
State: Jerard is an Insurance Agent from Florida
Jerard is a member of 1000 posts VIP club
I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing. Re: I Made a Commission/Deposit/Tax Tracker Spredsheet that I'm sharing. Jerard is the thread starter for: I Made a Commission/Deposit/Tax Tracker/Spreadsheet 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)
------------------------------------

Last edited by Jerard; 02-23-2013 at 12:08 AM.
  Reply With Quote to I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
Old 02-23-2013, 08:56 AM   #8
Super Genius
 
Join Date: Sep 2011
Posts:153
State: Underwriter2Agent is an Insurance Agent from Florida
I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
Thank you very much for sharing...this is very helpful
------------------------------------
  Reply With Quote to I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
Old 02-23-2013, 02:24 PM   #9
Guru
 
Join Date: Jan 2013
Posts:674
State: blue_wynd is an Insurance Agent from Undisclosed Location
I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing. Re: I Made a Commission/Deposit/Tax Tracker Spredsheet that I'm sharing.
Originally Posted by Jerard View Post
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.
------------------------------------
  Reply With Quote to I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
Old 02-23-2013, 05:36 PM   #10
Guru
Jerard on I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
 
Join Date: Jun 2012
Posts:2,764
State: Jerard is an Insurance Agent from Florida
Jerard is a member of 1000 posts VIP club
I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing. Re: I Made a Commission/Deposit/Tax Tracker Spredsheet that I'm sharing. Jerard is the thread starter for: I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.

Originally Posted by blue_wynd View Post
How to you add additional carriers? The dropdown only has rna, mon. etc.
See the original post, I added some instructions for that.
------------------------------------
  Reply With Quote to I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
Reply to I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing. 

  Insurance Agent Forum > Insurance Forums > Insurance Agents and Brokers Forum > Insurance Technology Forum




Similar Threads with I Made a Commission/Deposit/Tax Tracker/Spreadsheet that I'm Sharing.
Thread Thread Starter Forum Replies Last Post
New Agents (and Old): Do You Know how Much You Made Last Week? Josh General Insurance Agent Discussions 22 07-11-2014 02:12 PM
Cost Sharing Info Sent to Carrier? yankee466 Health Care Reform Forum 9 01-28-2014 08:43 AM
Claims Made E&o Coverage Milkman1265 P&C Insurance Forum 7 08-19-2013 11:04 AM



All times are GMT -5. The time now is 04:56 AM.

Forum enabled with vBulletin®
Copyright ©2000 - 2017, vBulletin Solutions, Inc.


Join the Forum - It's FREE

Share This Page



Weekly Poll

Where do we go from here? POLL
Nothing - No ACA fixes - status quo
29.82 %
HHS Rulings and band aid bills
17.54 %
Hail Mary repeal and/or replace bill
17.54 %
A bipartisan bill to fix ACA
35.09 %

Voters: 57. This poll is closed