Agent Commission (New vs Renewal)

Excel Formulas › Insurance

All versionsVLOOKUP

Insurance agents earn a commission percentage of premium — typically higher on new business than renewals. A rate that depends on policy type and status turns a book of premium into commission.

Quick formula: commission on a policy:
=premium * commission_rate
Premium times the commission rate. New business often pays a higher rate than renewals.

Functions used (tap for the full reference guide):

VLOOKUPSUMIF

The example

$2,000 premium, 15% new.

AB
1ItemValue
22000 × 15%—
3Commission→ $300

The formula

The formula:

=premium * commission_rate // premium × rate

How it works

How it works:

  1. Multiply premium by the commission rate for that policy.
  2. Rates differ by new vs renewal — look them up with VLOOKUP or IF.
  3. Total a book with SUMPRODUCT(premiums, rates) across policies.
  4. Split between agency and producer with a further percentage if applicable.

Illustrative math only — not insurance, financial, or legal advice. Policy language, state regulation, and carrier rules govern actual claims, premiums, and coverage. Always read the policy and consult a licensed professional.

Try it: interactive demo

Live demo

Premium and status.

Commission:

Variations

Rate by status

New vs renewal:

=premium * IF(status="New", new_rate, renewal_rate)

Book total

Across policies:

=SUMPRODUCT(premiums, rates)

Producer split

Agency share:

=commission * producer_pct

Pitfalls & errors

New vs renewal. Use the right rate for the policy status.

Percent as decimal. 15% is 0.15.

Net premium. Commission may apply to net of fees — confirm the base.

Practice workbook

📊
Download the free Agent Commission (New vs Renewal) practice workbook
A commission sheet with the by-status, book-total, and split variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate insurance agent commission in Excel?
Multiply premium by the rate: =premium * commission_rate. 15% on $2,000 is $300.
How do I handle new vs renewal rates?
Look up the rate by status: =premium * IF(status="New", new_rate, renewal_rate).
How do I total a book of business?
Use SUMPRODUCT of premiums and their rates: =SUMPRODUCT(premiums, rates).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Tiered commission · Loss ratio · Tax bracket lookup

Function references: VLOOKUPSUMIF