Forum Discussion
Insurance Earned Premium/Loss Ratio Calculation
Hi Dale,
I have been thinking about this, and I finally understood that my data model was wrong.
Now the model has the info "flat" month by month, and I let Power Bi do just aggregations.
With this model now I can see the Earned Premium month by month, and calculate the Loss Ratio change month by month.
Thank you Dale for your help!!
Gus.
Hello Gus,
I have been having issues calculating Earned premium in my model.
Would you be able to share the code and the process how you got to Earnedpremiumchange?
Thank you.
Absolutely!
But quick comment regarding the values in this model: I don’t do ANY complex calculations in this model. Everything is calculated as part of the data feed (before reaching PowerBi, in our backend system database)
I only allow PowerBi to do simple stuff like sum, average, YTD, ITD, Last 12 months, and that sort of thing, not because PowerBi has a lack of functionality, but because I don’t know how to do it in PowerBi. Also, I believe that if you implement complex calculations, you WILL affect in a negative way the user experience.
Now, let’s switch gears and let’s talk about SQL and how I calculate the Earned Premium Change. At a high level: I calculate the Earned Premium as of this period, and the last period at the same time, and I subtract one from the other.
In SQL, as you can imagine, I have a table with the term premium at the coverage level, with the effective, expiration, and accounting/booking date. The table looks something like this (it is much more complicated, but for the sake of the example I’m trying to keep it simple):
Transaction Type | Coverage | Effective Date | Expiration Date | Accounting Date | Term Premium |
New Business | BI | 01/01/2001 | 01/01/2002 | 01/01/2001 | 365.00 |
If I cancel the policy, then I will see 2 records, 1 for the new business and 1 for the cancellation. In this system, the cancellation represents the period you are cancelling.
In this example, this policy was inforce for 32 days.
Transaction Type | Coverage | Effective Date | Expiration Date | Accounting Date | Term Premium |
New Business | BI | 01/01/2001 | 01/01/2002 | 01/01/2001 | 365.00 |
Cancellation | BI | 02/02/2001 | 01/01/2002 | 02/02/2001 | -334.00 |
This is important to explain because my logic is based on this architecture.
Your implementation may vary depending how your system is designed.
I feed PowerBi with the information as of the last day of the month.
So I run this query (soudo code btw) as of the last day:
@ReportDate = 01/31/2001
@ReportDateLastPeriod = 12/31/2000
select Coverage, TermPremium
-- Calculate Unearned for this Period: 01/31/2001
, case when dbo.fun_PolicyIsCancel(as of @ReportDate) = No then
dbo.fun_CalculateUnearned(TermPremium,EffectiveDate,ExpirationDate,as of @ReportDate)
else 0 end as [Unearned Premium]
-- Calculate Unearned for the Last Period: 12/31/2000
, case when AccountingDate <= @ReportDateLastPeriod then
case when dbo.fun_PolicyIsCancel(as of @ReportDateLastPeriod) = No then
dbo.fun_CalculateUnearned(TermPremium,EffectiveDate,ExpirationDate, as of @ReportDateLastPeriod)
else 0 end
else 0 end as [Unearned Premium Last Period]
-- Calculate Earned for this Period: 01/31/2001, using the result from the Unearned Calculation
, TermPremium – [Unearned Premium] as [Earned Premium]
-- Calculate Earned for the Last Period: 12/31/2000, using the result from the Unearned Last Period Calculation
, case when AccountingDate <= @ReportDateLastPeriod then
TermPremium – [Unearned Premium Last Period]
else 0 end as [Earned Premium Last Period]
-- Calculate the change in Unearned
, [Unearned Premium] – [Unearned Premium Last Period] as [Unearned Change]
-- Calculate the change in Earned
, [Earned Premium] – [Earned Premium Last Period] as [Earned Change]
from Coverage
where AccountingDate <= @ReportDate
From this query, I get the Unearned Change, and Earned Change and I send it to PowerBi with the rest of the data (Premium, OS, Incurred, Recoveries, etc, etc)
I hope this helps
Gus.