How to Analyze Loss Ratios by Product Line in Excel

How to Calculate Loss Ratio % by Product Line in Excel

This walkthrough shows how to calculate Loss Ratio Percentages by product line, helping you see which products have the highest claim costs relative to premiums.
Loss ratio insights are critical for evaluating profitability, adjusting commissions, and managing carrier relationships.

1. Create a List of Product Lines

• Use the UNIQUE() function on your Product Line column (e.g., Column H).
  
• To remove formulas, copy the range and paste values using Ctrl + Shift + V.
  
• To make it dynamic (so new data auto-updates), add a period (.) inside the formula.
  
• Remove any “0” entries and sort alphabetically if desired.
  

2. Prepare Key Columns

Add two new columns to calculate:

• Total Claim Cost (Claims Paid + Loss Adjustment Expense)
  
• Written Premium
  

These will be used to calculate total loss ratio values.

3. Calculate Total Claim Cost with SUMIFS

Use the SUMIFS() function to sum total claim cost by product line.
Example:

=SUMIFS(TotalClaimCostRange, ProductLineRange, ProductLine)

Tips:

• Use F4 to lock ranges properly when dragging formulas.
  
• Format as currency using Ctrl + Shift + 4.
  
• Center-align with Alt + H + A + C.
  

4. Calculate Total Written Premium

Copy the SUMIFS formula from your Claim Cost column and update the range to the Written Premium column.
Then apply the same formatting as before for consistency.

5. Compute Loss Ratio Percentage

Divide the two totals to find the loss ratio for each product line:

=TotalClaimCost ÷ WrittenPremium

Format as a percentage using Ctrl + Shift + 5 and adjust decimals as needed.
This gives a precise, weighted loss ratio (rather than a simple average).

6. Combine into a Single Formula (Optional)

If you want a cleaner sheet, replace cell references with the SUMIFS formulas directly:

=(SUMIFS(TotalClaimCostRange, ProductLineRange, ProductLine)) ÷  (SUMIFS(WrittenPremiumRange, ProductLineRange, ProductLine))

This consolidates your model into one formula per product line.

7. Create a Bar Chart

• Highlight your Product Line and Loss Ratio columns.
  
• Go to Insert → Recommended Charts → Clustered Bar Chart.
  
• If you prefer sorting, convert formulas to values first, then use Sort Smallest to Largest or Largest to Smallest depending on how you want to display ratios.
  
• Add data labels for clarity and format as whole percentages (zero decimals).
  

8. Format for Readability

• Remove extra decimals under Home → Decrease Decimal.
  
• Center text and adjust chart title (e.g., “Loss Ratio % by Product Line”).
  
• Use consistent colors or brand palette for charts.
  

9. Optional: Analyze by Carrier

You can easily adapt this process to show Loss Ratios by Carrier instead of by product line. Simply change the criteria range in your SUMIFS formulas to the Carrier column.

Result

You now have a dynamic Excel model showing Loss Ratios by Product Line, calculated with precision and visualized for quick insights. This metric helps identify high-cost products and maintain balanced profitability across your portfolio.