Mapping an insurance book of business in Google Sheets

A book of business in insurance is an agent or broker's active client list, representing the policies in force and the associated renewal commissions. Carriers and independent brokers value this asset by applying a multiple to the annualised commission revenue, buying and selling it when producers retire or agencies merge.

This is for independent brokers and agency managers who want to operationalise their book of business in Google Sheets. By turning your client address list into a live map, you can calculate exposure accumulation, divide producer territories, and route post-CAT field crews without leaving your spreadsheet.

What is an insurance book of business?

An insurance book of business is the total portfolio of active policies an agent or broker holds at any given time. It comprises the named insureds, physical mailing addresses, policy types, written premiums, commission rates, and renewal dates. When an agency owner decides to sell the agency, they are not selling a mere list of names; they are selling the rights to the future renewal commissions attached to those specific addresses.

Carriers and producers evaluate this asset differently. A producer views the book as a geographic pipeline for cross-selling additional lines, such as offering commercial auto to existing general liability clients. They look at neighbourhood density to plan their daily routing.

Carriers, however, view the book of business as a concentration of financial risk. If an agent writes 2,000 property policies across a single coastal county, the carrier sees a massive exposure accumulation. A single hurricane hitting those exact coordinates could trigger catastrophic payouts.

For a book to hold its value, the underlying data must be pristine. A standard book of 2,500 active policies requires 2,500 verified physical locations. If the spreadsheet contains post office boxes or rural route designations instead of standardised street addresses, the book loses operational value. The geographic data must be exact to run accurate accumulation reports or divide the accounts among new producers.

How a book of business is valued and bought

The valuation of an insurance book of business is driven by standard multiples of commission and strict retention metrics. Agencies generally trade for 1.5 to 2.5 times their annual recurring commission. A book generating $500,000 in annual commission with a 95% retention rate might command a $1,000,000 purchase price. Personal lines books often secure lower multiples due to higher cancellation rates, while commercial books with multi-year contracts secure higher multiples.

When a book changes hands, the acquiring agency executes a Broker of Record (BOR) process. The buyer sends a BOR letter to the respective carriers, instructing them to redirect the commission streams and move the active policies under the new agency's master code.

During this transfer, the immediate operational hurdle is the physical address audit. The acquired book often contains dirty data. A carrier's download might list "123 Main St" for one policy and "PO Box 45, Main St" for another, making it impossible to measure geographic density or plot the locations.

Before calculating the exact risk of the newly acquired book, the agency must standardise the location data. A spreadsheet containing 1,200 rows of acquired policies requires a formula like =CLEAN_ADDRESS(A2:A1200) to repair formatting errors. The agency then runs =GEOCODE(B2:B1200) to generate the exact coordinates for the physical risks. Only after the BOR transfer is complete and the coordinates are established can the buyer accurately measure the true geographic exposure of the purchased asset.

Map exposure accumulation in a storm path

Carriers enforce strict accumulation limits to prevent a single catastrophic event from devastating their capital. A carrier might stipulate that an agency cannot write more than $10,000,000 in total insured value (TIV) within a 50-mile radius. When a hurricane tracks toward the coast, an agency must immediately quantify its exact exposure within the storm's projected path to prevent breaching those concentration thresholds.

To measure this in Google Sheets, the agency must first convert its list of physical addresses into geographic coordinates. If column A holds the street addresses of a 2,500-policy book, the agency runs =GEOCODE(A2:A2501). The free tier provides 100 lookups per day (1,000/day with a free email unlock), which handles smaller farms or daily incoming renewals. If an address is a post office box, the formula returns an error, instantly flagging the row for manual correction.

Once the latitude and longitude for the active policies are established, the agency plots the hurricane's projected landfall coordinates. If the storm is expected to strike coordinates (28.083, -80.608), the agency uses =WITHIN_RADIUS() to isolate the risk. The formula =WITHIN_RADIUS(B2:C2501, 28.083, -80.608, 50) evaluates the coordinate ranges and returns only the rows that fall inside the 50-mile line.

This isolates every policy in the storm's path. The agency can then sum the TIV column for that filtered list. If the total exceeds the carrier's $10,000,000 concentration limit, the agency knows it must cease binding new policies in that specific area until the storm passes.

Assigning producer territory splits

Dividing accounts alphabetically among producers creates inefficient routing, forcing agents to drive across entire counties for simple renewal reviews. Splitting a book of business by geographic territory reduces windshield time and allows producers to build local expertise.

Instead of manually assigning postcodes to five different producers, an agency can automate the division. If the agency defines five territories by their central coordinates (e.g., North, South, East, West, and Central zones), they can use =TERRITORY() to categorise the book. The formula =TERRITORY(A2:A1500, "Territory_Key") assigns each address in the 1,500-row book to its designated producer based on the closest territory boundary. You can insert these formulas without typing by using the InstaMaps sidebar (Extensions > InstaMaps > Enable formulas) and clicking the Build-the-workflow button to write the entire chain.

Once the rows are assigned, the agency must visualise the split to ensure the workload is balanced. By running =INSTAMAP(), the add-on generates a live, hosted shareable map URL that displays every account, colour-coded by producer. If the map reveals that the South territory contains 800 policies while the North contains only 200, the agency can redraw the territory boundaries and re-run the formula.

Because the map updates automatically when the sheet changes, any adjustments to the territory keys instantly reflect on the live map without generating a new link.

Cross-sell by neighbourhood and status

Your book of business is fundamentally a list of physical addresses. To cross-sell commercial auto to existing property owners, you must see where your policy density drops off. First, geocode your entire book using =GEOCODE(A2:A500) to generate precise latitude and longitude coordinates. Next, add a column for policy status (e.g., column D) using strict values like Active, Quoted, Bound, or Lapsed.

To visualise cross-sell opportunities by neighbourhood, select an empty cell-such as F2-and input =INSTAMAP(B2:B500, C2:C500, D2:D500). This references your latitude, longitude, and status columns. It immediately returns a live, hosted, shareable map URL.

Because the map reads directly from the sheet, it updates dynamically when a policy status changes. When a producer secures a commercial auto policy on an existing property account, they change the text in column D from Quoted to Bound. The corresponding marker on the hosted map URL instantly updates to reflect the new status.

If you are campaigning for a BOR (Broker of Record) in a specific postcode, filter your sheet to display only Lapsed policies. Generating a new map from this filtered view highlights exact streets where your carrier density is weak, giving you hard data to present to underwriters for regional appetite reviews. To group these markers visually, ensure your status column is passed directly into the =INSTAMAP() function so the add-on can segment the points by text value.

Worked example: Post-CAT routing for a 200-home farm

Consider a 200-home farm book in the direct path of a hailstorm. You receive 47 active claims in the aftermath. Here is the step-by-step process to route five field crews efficiently without enterprise dispatch software:

1. Filter the sheet: From your 200 rows of active policies, filter the claim status column to show only Open Claim. This leaves exactly 47 rows.

2. Assign crews: In a new column (F), assign the values Crew A through Crew E to the 47 addresses, allocating 9 or 10 stops to each team.

3. Calculate drive sequence: Focus on the 9 rows assigned to Crew A. If their coordinates sit in B2:C10, enter =VISIT_ORDER(B2:C10). This function evaluates the latitude and longitude pairs and returns the exact numerical stop sequence (e.g., 1, 4, 2, 9) required for the shortest driving time.

4. Generate navigation: Once you reorder the rows for Crew A based on that sequence, use the address column (A2:A10) to generate a turn-by-turn link with =ROUTE_LINK(A2:A10). This uses Google Maps' official URL scheme, which accepts a strict maximum of 11 stops.

Limits and honest alternatives

InstaMaps operates within strict boundaries. The free tier allows 100 lookups per day. If your book of business contains 5,000 rows, geocoding the entire list will fail unless you use the free email unlock, which raises the limit to 1,000 lookups per day.

Routing is also restricted by external systems. =ROUTE_LINK() relies on Google Maps' official URL scheme, which accepts a strict maximum of 11 stops. If you attempt to link 15 addresses, the URL will truncate or break. For routes larger than 11 stops, you must split the data into separate batches or use a dedicated fleet management system.

You do not have to memorise function syntax. Click Extensions > InstaMaps > Enable formulas to open the sidebar. You can select cells and click to insert formulas, or use the Build-the-workflow button to write entire formula chains across multiple columns automatically.

Who this workflow is for

This workflow is designed for independent agencies and brokers who manage their book of business entirely within Google Sheets. If you track policies, producers, and BOR opportunities in a spreadsheet, and you need to map accumulation or route field adjusters without buying enterprise GIS software, this add-on fits your requirements.

You should stick to manual mapping if your agency relies on a proprietary agency management system (AMS) that prevents exporting clean CSV data. If you require real-time traffic integration, complex multi-day fleet routing optimisation, or secure hosting for Personally Identifiable Information (PII) inside a closed network, do not use this tool. InstaMaps is strictly for spatial analysis of spreadsheet text.

It is a tool for agents who need immediate, shareable visualisations of their existing rows, not a replacement for heavy-duty commercial underwriting platforms.

Common Questions