Insurance Agency Economics: Commissions, Retention, Book Value & Producer Pay

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Compute the commission of Harbor & Pine Insurance Agency per line of business from new and renewal premium, the effective commission rate per line and for the whole agency, and how much of the revenue is recurring.
• Turn quarterly written premium into gross commission, subtract return commission on cancellations, add fees and express net revenue as a share of premium.
• Measure retention for households with one, two and three or more policies, the agency's overall retention and policies per household, and the commission walking out the door.
• Roll the book of business forward five years with retention, rate growth and new business, then compute the growth rate and the new business needed just to stand still.
• Compute each producer's split pay, check whether they validate against their base salary, work out their pay under the plan and add a data validation rule that keeps the split rates sensible.
• Compute each carrier's loss ratio, look up the bonus tier with XLOOKUP, apply the minimum-volume rule and find how far one carrier is from the next tier.
• Build total revenue, total expenses, EBITDA and its margin for two years, then normalise the owner's salary for an adjusted EBITDA and check the compensation ratio.
• Value the agency with revenue and EBITDA multiples, agree a price, compute an earnout tied to retention and chart the valuation range.