Insurance MGA & Program Business Financial Model – Premium Underwriting, Reinsurance, IBNR and Captive Economics Suite

Description
🛡️ Understand how an MGA program turns submissions into premium—and how every participant in the insurance value chain earns its share.The Insurance MGA & Program Business Model is a premium, fully editable Excel suite created for insurance professionals, MGA founders, program administrators, fronting carriers, reinsurers, captive-cell participants, consultants, investors and finance teams that need a transparent view of specialty-program economics.

This is not a generic revenue-growth template. It is a connected operating and financial model that follows the program from submissions → quotes → binds → gross written premium → reinsurance → commissions → losses → reserves → underwriting result → stakeholder returns.

The workbook combines monthly operating drivers, five-year financial projections, loss-reserving mechanics, commission waterfalls, fronting economics, captive participation, scenario analysis and decision-ready dashboards in one professionally structured model.

The model is delivered as a native .xlsx workbook with 19 linked sheets, 27 charts and no macros or VBA. Inputs are visually separated from formulas, every major output traces back to the assumptions, and the built-in Audit & QC sheet continuously checks model integrity.

📌 What is included in the model?
The suite contains all of the following interconnected modules:

• Global assumptions and operating controls
  
• 60-month submissions-to-premium funnel
  
• Five-year GWP forecast by program
  
• Gross, ceded and net premium bridge
  
• Quota-share reinsurance and ceding commission
  
• Paid and incurred loss-development triangles
  
• Chain-ladder ultimate loss and IBNR calculations
  
• Retail, MGA base, override and contingent commissions
  
• Fronting-carrier fee, capital charge, margin and ROE
  
• With-captive versus without-captive economics
  
• Consolidated program P&L
  
• Full stakeholder economics waterfall
  
• Base, Upside and Downside scenarios
  
• Loss-ratio sensitivity analysis
  
• Executive, underwriting, loss/reserve and stakeholder dashboards
  
• Live formula, blank-cell, reconciliation and structural checks
  
  

📈 1. Bottom-up premium funnel and GWP forecasting
The model starts with the operating engine of an MGA: its ability to source, quote and bind insurance business.

The Premium Funnel models 60 monthly periods from 2026 through 2030 across three illustrative specialty programs:

• Commercial Property
  
• Specialty Casualty
  
• Cyber E&O
  

For each program, users can enter and test:

• Opening monthly submission volume
  
• Quote rate
  
• Bind rate
  
• Monthly submission growth
  
• Average premium per bound policy
  
• Annual premium growth
  
• Program-specific gross loss ratio
  
  

The model calculates submissions, quotes, binds, average bound premium and GWP every month. It then converts the detailed monthly output into a calendar-year summary for 2026–2030, including blended quote rate, blended bind rate and submission-to-bound conversion.

This allows buyers to see exactly what drives premium growth. GWP is not entered as a simple top-down figure—it is produced from the underlying pipeline and conversion assumptions.

🔁 2. Ceding and fronting structure
The Ceding & Fronting module converts gross written premium into ceded premium and net written premium using an adjustable quota-share assumption.
The schedule calculates:

• Gross written premium
  
• Ceded premium under the quota-share treaty
  
• Net written premium retained by the program
  
• Ceding commission received
  
• Retention ratio
  

This provides a clear gross-to-net premium bridge and makes the effect of reinsurance visible across the full forecast period.
The separate Fronting Economics module evaluates the fronting carrier’s position by modelling:

• Fronting fee income as a percentage of GWP
  
• Captive-cell rental income where applicable
  
• Regulatory capital held as a percentage of GWP
  
• Capital charge based on the fronting carrier’s cost of capital
  
• Economic margin after capital charge
  
• Fronting ROE
  
• Fee income as a percentage of GWP
  

This is especially useful when reviewing whether the fronting fee adequately compensates the carrier for the capital committed to the program.

📉 3. Loss triangles, reserve development and IBNR
The model includes both cumulative paid and incurred loss-development triangles using six accident years and six annual development periods.
Unlike templates that require the user to type development factors manually, this workbook calculates volume-weighted age-to-age paid-loss development factors from the triangle data and applies a user-controlled tail factor.

The Reserves & IBNR schedule then calculates, by accident year:

• Paid losses to date
  
• Case incurred losses to date
  
• Current maturity
  
• Cumulative development factor to ultimate
  
• Chain-ladder indicated ultimate loss
  
• Case reserves
  
• IBNR
  
• User-entered booked ultimate loss
  
• Reserve adequacy ratio
  

It also calculates total carried reserves and provides clear loss-development and reserve-composition views.

This module helps users understand how emerging claims experience can affect indicated ultimate losses, reserve needs and overall program economics.

💼 4. Multi-layer commission waterfall
The Commission Waterfall separates the cost of distribution into its different economic layers rather than combining everything into one commission rate.
It calculates:

• Retail agent commission
  
• MGA base commission
  
• MGA override commission
  
• MGA contingent or profit commission
  
• Total MGA remuneration
  
• Total distribution cost
  
• Distribution cost as a percentage of GWP
  

Contingent commission is determined through a loss-ratio band table. The applicable rate is selected by lookup from the program’s blended gross loss ratio, allowing better underwriting performance to produce a different contingent commission outcome.

Because the commission components are displayed individually, the model makes it easier to negotiate, analyse and explain how the economics are divided between the retail agent and MGA.

🏦 5. Captive-cell economics with a genuine structure toggle
The model includes an adjustable With Captive / Without Captive switch.
This is not merely a label change. The toggle changes how the underwriting result is allocated and updates captive profit, collateral requirements, rental fees and the wider stakeholder P&L.
The Captive Cell schedule calculates:

• Net written premium
  
• Program underwriting result before the captive split
  
• Captive participation percentage
  
• Captive share of underwriting profit
  
• Annual cell rental fee
  
• Net captive-cell profit
  
• Required collateral or cell capital
  
• Captive-cell ROE
  

A separate cumulative comparison shows the financial effect of operating with or without the captive, including captured underwriting profit, average collateral posted and implied return on collateral.
This feature is valuable for MGAs, program sponsors and capital partners evaluating whether participation in underwriting profit justifies the collateral commitment and facility costs.

💰 6. Consolidated program P&L and stakeholder economics
The Program P&L consolidates all operating, reinsurance, loss and fee mechanics into one five-year financial statement.
The P&L includes:

• Gross written premium
  
• Gross ultimate losses
  
• Gross underwriting economics pool
  
• Ceded premium
  
• Ceded losses recovered
  
• Ceding commission received
  
• Retail agent commission
  
• MGA base, override and contingent commission
  
• Fronting fee
  
• Program operating expenses
  
• Net earned premium
  
• Net incurred losses
  
• Net expense load
  
• Net underwriting result
  
• Net loss ratio
  
• Net expense ratio
  
• Net combined ratio
  

The stakeholder section then reconciles the full gross economics pool across:

• Retail agent
  
• MGA
  
• Fronting carrier
  
• Reinsurer
  
• Program operating expenses
  
• Captive cell
  
• Captive facility sponsor
  
• Risk capital provider
  

This makes the model particularly useful for answering the central commercial question in program business: who earns what, why, and how does the allocation change when assumptions or structure change?

🧪 7. Scenario and sensitivity analysis
The model includes three operating scenarios:

• Base
  
• Upside
  
• Downside
  

The scenario switch changes submission growth, bind rates, loss ratios and average premium growth. The selected assumptions flow through the funnel, commissions, underwriting economics, captive returns, P&L and dashboards.
The dedicated sensitivity grid tests the effect of gross loss-ratio shocks on:

• 2026 net underwriting result
  
• Captive-cell profit
  
• Captive-cell ROE
  
• Change in underwriting result versus the base case
  

This allows users to see how quickly program profitability and captive returns can deteriorate or improve as loss experience changes.

📊 8. Four professional dashboards
The workbook includes four purpose-built dashboards:

• Executive Dashboard: GWP, combined ratio, loss ratio, MGA commission and net underwriting result.
  
• Underwriting Dashboard: submissions, quotes, binds, conversion, bind-rate trend and premium mix.
  
• Loss & Reserve Dashboard: loss-development factors, indicated ultimate losses, IBNR, case reserves and reserve adequacy.
  
• Stakeholder Dashboard: agent, MGA, fronting carrier, reinsurer, captive and risk-capital-provider economics.
  

These dashboards convert complex calculations into clear outputs suitable for management review, program discussions, investor analysis and internal decision-making.

✅ 9. Automated Audit & QC framework
The built-in Audit & QC sheet performs 31 live checks across the workbook.
The control framework covers:

• Formula-error sweeps
  
• Blank-cell checks in active model tables
  
• GWP reconciliation
  
• Gross-to-net premium tie-out
  
• Stakeholder waterfall reconciliation
  
• P&L-to-chart-data tie-out
  
• Triangle-to-reserve-schedule reconciliation
  
• Captive-toggle integrity
  
• Contingent-commission band validation
  
• CDF monotonicity
  
• Circular-reference control
  
• Hardcoding control
  
• Chart-series naming
  
• Implicit-intersection control
  
• External-link control
  

The checks feed a visible Model Health Score, giving users a fast indication of whether the model remains internally consistent after assumptions are changed.

🎯 Who should use this model?
The model is especially relevant for:

• MGA founders and management teams
  
• Program administrators
  
• Specialty-insurance underwriting teams
  
• Fronting carriers
  
• Reinsurance professionals and brokers
  
• Captive managers and cell-facility sponsors
  
• Insurance consultants and transaction advisers
  
• Private-equity and strategic investors evaluating MGA economics
  
• FP&A, finance and corporate-development professionals
  
• Analysts and advanced students learning program-business economics
  
  

🧭 How to use the model

1. Read the Instructions & Legend sheet to understand the colour convention and control logic.
   
2. Select the active scenario and captive structure on the Assumptions sheet.
   
3. Replace the synthetic program assumptions with credible, program-specific submission, conversion, premium, loss, ceding, commission, fronting and capital inputs.
   
4. Review the monthly premium funnel and annual GWP output for each line of business.
   
5. Review the ceding structure, commission waterfall, fronting economics and captive-cell results.
   
6. Replace the illustrative loss-triangle data and booked ultimates with appropriate program information.
   
7. Review the consolidated P&L, scenarios, sensitivity and dashboards.
   
8. Confirm that the Audit & QC Model Health Score remains at 100% before relying on any output.