How to Calculate Acquisition Cost per Policy in Excel

How to Calculate Acquisition Cost per Policy in Excel

This tutorial shows how to calculate your true Customer Acquisition Cost (CPA), the real cost of gaining each new policy, and how to compare profitability by lead source. By combining Acquisition Cost, Gross Revenue, and Margin, you can see which channels deliver the best return on investment.

1. Create a List of Lead Sources

• Use the UNIQUE() function on the Lead Source column (for example, Column L).
  • Copy the result and paste values using Ctrl + Shift + V to remove formulas.
  • If you want it to update automatically when new data is added, add a period (.) inside the formula.
  • Optionally sort alphabetically for easier reference.

2. Calculate the Average Acquisition Cost (CPA)

• Acquisition cost data is in Column S.
  • Use the AVERAGEIFS() function to calculate the average cost per lead source.
  Example:
  =AVERAGEIFS(AcquisitionCostRange, LeadSourceRange, LeadSource)
  • Paste the results as values.
  • Format as currency using Ctrl + Shift + 4.

Tip: Label this column “Cost per Acquisition (CPA)” for clarity.

3. Calculate the Average Gross Revenue per Policy

• Gross revenue is in Column R.
  • Use the same AVERAGEIFS() formula to find the average revenue for each lead source.
  • Paste values and format as currency.
  • Center-align the numbers with Alt + H + A + C.

If you want to analyze performance by month, extract the month from the policy effective date using text functions and add it as another criterion.

4. Calculate the Margin

• Create a new column dividing Gross Revenue ÷ Acquisition Cost.
  • Format as a percentage using Ctrl + Shift + 5.
  • Copy the formula down for all rows.

This margin shows how many dollars of revenue you earn for every dollar spent acquiring a policy.

5. Build a Combo Chart

1. Highlight your table (Lead Source, CPA, Revenue, Margin).
   
2. Go to Insert → Recommended Charts → Combo Chart.
   
3. Place both dollar values (CPA and Revenue) on the Primary Axis.
   
4. Place Margin (%) on the Secondary Axis.
   

Your chart should display:

• Blue bars for Acquisition Cost
• Orange bars for Gross Revenue
• Green line for Margin

Now you can instantly compare the cost and profitability of each lead source.

6. Format and Label the Chart

• Add a descriptive title such as “Acquisition Cost per Policy by Lead Source.”
  • Adjust colors, fonts, and labels to match your theme.
  • Add data labels if you want exact values visible.

7. Interpret the Results

• SEO leads return about $4.03 for every $1 spent.
  • Email campaigns return about $3 per dollar.
  • Referrals have the highest margin overall, even with smaller total revenue.

This analysis reveals where to focus your budget and which channels need improvement.

8. Optional Enhancements

• Add conditional formatting to highlight top-performing sources.
  • Include monthly or quarterly filters for trend analysis.
  • Add Lifetime Value (LTV) to calculate total customer ROI.

Result

You now have a dynamic Excel model that calculates and visualizes Acquisition Cost, Gross Revenue, and Margin by Lead Source. This helps you identify high-performing marketing channels and make smarter investment decisions.