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.
Hi everone,
I'm quiet advanced in insurance finances/underwriting etc, but completely new in Power BI.
Is there possibility to publish a small example file with PBI model? Of course with anonymized personal data (if exists).
It hasn;t to include many data , just few rows in every table. And of course all scripts mentioned in this topic.
Is it possible?
Br,
Jarek
Hi Jarek,
I really don't think this is the best solution, but here's the file. --> https://drive.google.com/file/d/1rPvkPq14VYiTm3kGK0nbvmFvdD6c36D_/view?usp=sharing
The reason why I say this is becuase I'm creating a record for each policy/risk/coverage/month.
So, for 1 annual policy with 1 risk and 1 coverage, you will have 12 records, all calculated as the last day of the month.
I did it this way because I can't find the correct way of calculating the things I need, like the earned/unearned premium for the loss ratio, and because we don't have a lot of policies.
The good thing about this design, is that I don't have to do complex calculations, I leverage the out of the box functionalities of Power Bi to run simple aggregations (like sum, avg, count, etc). The more complex calculations you add, the slower your dashboard becomes.
But I will reach the day when the data will be too much data.
All data has been scrambled, so if you find James Bond as a client let me tell you, we don't insured James Bond. High risk and all...
If you have any question, please, let me know.
Gus
Hi Gus,
thanks a lot for quick response.
I downloaded the model and will analyze it for my purposes (mainly extended warranty, PA, Travel).
Dashboards looks quiet well.
I was looking any documents/examples in PBI connected with insurance business, but there is not to much in the web 😞
"...we don't insured James Bond. High risk and all..." - but You know, premium could be high. Especially at the end of the year to meet the budget goals 🙂
Br,
Jarek
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 <= @ReportDateFrom 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.
