How to Calculate Monthly Written Premium in Excel

Learn how to analyze your agency’s written premium month by month using Excel. In this lesson, you’ll see how to organize your data, build a clean visual that highlights performance trends, and create a simple dashboard view you can share with your team.

Download the Excel file used in this tutorial:

Step By Step Instructions

How to Calculate Written Premium by Month in Excel

In this tutorial, you’ll learn how to calculate and visualize Written Premium on a month-by-month basis in Excel. This is one of the first KPIs every insurance agency should track, as it shows how revenue trends evolve throughout the year.

1. Set Up the Month Labels

In a blank column, type January in the first cell.
Drag down the fill handle to automatically generate all months through December.
These will serve as your reference points for calculating each month’s total written premium.

2. Identify the Written Premium Column

Confirm which column in your dataset contains Written Premium values (for example, Column N).
Ensure you also have a Date column (e.g., Column D) that includes the policy effective date or month.

3. Use the SUMIFS Function

In the cell next to January, enter a SUMIFS formula to calculate the total written premium for that month:

=SUMIFS(WrittenPremiumRange, MonthRange, “January”)

Replace “January” with the cell reference containing the month name (e.g., B3).
The formula will sum all policies written in January.
Copy or drag the formula down to calculate totals for all remaining months.

4. Format the Results

Use Ctrl + Shift + 4 to apply currency formatting.
To remove decimals, highlight the column and click the Decrease Decimal button on the Home tab.
You can also double-click the small green fill handle to autofill the entire column.

5. Create a Chart to Visualize Written Premium

Highlight your month and total columns.
Go to Insert → Charts → Line Chart.
Choose a simple line chart for a clean monthly trend view.
Under Chart Design, adjust the colors or layout as desired and resize for better visibility.

6. Review and Customize

Add a chart title like “Written Premium by Month”.
Optionally label the data points for quick reference.
You now have a month-over-month visualization of your written premium trends, perfect for identifying seasonal patterns or growth momentum.

Result

You’ve created a simple but powerful KPI visualization in Excel that shows total written premium per month. This chart helps agencies quickly track production trends and understand where growth or slowdowns occur throughout the year.

FAQ

Tracking Written Premium in Excel Dashboards

Q1. What is written premium in insurance analytics?
Written premium refers to the total value of all insurance policies issued by an agency during a specific period. It’s one of the most important insurance KPIs for understanding production trends, growth, and business performance over time.

Q2. Why should agencies track written premium in Excel?
Excel is one of the easiest and most flexible tools for insurance KPI tracking. It allows you to organize policy data, analyze month-by-month performance, and create visual dashboards that make premium trends clear for your management team.

Q3. How do I track written premium in Excel step by step?
You can organize your data by policy date and premium amount, summarize totals by month, and then visualize those results with a simple Excel line chart. This lets you quickly see which months were your best performers and where production may have slowed down.

Q4. Can this same process work for other insurance KPIs?
Yes. The same Excel dashboard setup can be used for metrics such as claims paid, policies renewed, commissions earned, or customer retention, any KPI that varies by month or quarter.

Q5. What’s the best chart for displaying written premium trends?
A line chart works best for spotting performance trends, but you can also use column or area charts to compare monthly production visually. Excel’s chart tools make it easy to customize colors and styles for reports or presentations.

Q6. Where can I get sample data to practice?
You can download the sample Excel dataset linked below the video tutorial. It includes monthly policy data so you can recreate the same written premium dashboard shown in the lesson.

Get more lessons like this
by joining our newsletter

Receive weekly Excel tutorials, tips, and KPIs straight to your inbox

Databoards

Analysis & Development