Pricing Logic
The most friendly premium calculation engine
Last updated
The most friendly premium calculation engine
Last updated
Pricing Logic is the core of an insurance product. We understand that most companies use Excel to lay out the pricing structure of an insurance product, hence, at KASKO, we developed our pricing engine around Excel.
The configuration on Product Builder has essentially 2 parts, the Pricing Logic file and the Validation Rules.
You can keep your formula and sheets in your excel and simply add one KASKO formatted sheet Calculator to the first sheet of your excel. You can get the example Excel file here.
In the KASKO Calculator sheet, you will see the below sections.
Input Fields
Below B7 - E7
This is the section that will collect customer inputs and use these inputs in the premium calculation process. In these columns, you will define quote-relevant inputs and the underwriting rules.
Quote Relevant Inputs
You should list out all the quote relevant input fields in column B
. It only accepts letters, numbers and underscore _
, for example input_field_1
is correct and £ Prämie
is incorrect.
Field Value This column will be left empty and the user inputs will be injected accordingly. While working on the excel, you may put in test values in this column and see if the calculation works as expected.
Required
In column D
, you will input TRUE
or FALSE
to indicate whether the specific input is always required for calculating a quote. TRUE
for always required; FALSE
for not required. You can also put in formula to make the TRUE or FALSE
dependant on the other input. For example, IF certain input is X, then TRUE
otherwise FALSE
IsValid
This allows you to define the more comprehensive underwriting rules for each input field. You will construct formula to check column C
input and return TRUE
for passing underwriting rules; FALSE
for not passing.
Example:
Row 8 - B7 - module
Row 8 - C7 - (left empty)
Row 8 - D7 - TRUE
Row 8 - E7 - =IF(ISNA(MATCH(C7,'Allowed values'!C3:C4,0)),FALSE,TRUE)
> where by in Allowed values
sheet C3, C4 are the only input value accepted, for example module 1
& module 2
Predefined Fields
Below G7 - H7
These are the fixed fields required for all pricing excel. You only need to fill in the values accordingly in column H
.
G8
- tax
; H8
- the tax % of your product
G9
- billing_frequency
;
G10
- billing_frequency_unit
;
The values for billing_frequency
& billing_frequency_unit
work together to define the payment frequency of this product. At KASKO, we currently support these frequencies:
Single Payment (not recurring) - billing_frequency = 1
& billing_frequency_unit = N
Monthly Payment (recurring) - billing_frequency = 1
& billing_frequency_unit = M
Quarterly Payment (recurring) - billing_frequency = 3
& billing_frequency_unit = M
Half-Yearly Payment (recurring) - billing_frequency = 6
& billing_frequency_unit = M
Yearly Payment (recurring) - billing_frequency = 1
& billing_frequency_unit = Y
Output Fields
Below J7 - K7
In this section, you will list out all the calculated values you want to either show throughout the customer or store in the policy data.
There are required output fields that need to be in place for all excel pricing (J8:K23
from the KASKO example excel) and you can add additional output fields below row 23
In the Calculator
sheet, you should avoid heavy formula as it will have impact on the performance of quote generating. Instead, you should have the calculation/formula in separate sheets and you can reference the output value from that sheet in Calculator
sheet.
There some output field naming rules that you should know. First of all, it only accepts letters, numbers, and underscore _
; secondly, add one of the 2 prefixes to all the output names. data.
prefix is for outputs that you want to store in the policy data; extra_data.
prefix is for outputs that you only want to use on the FE during the customer journey. The .
is used to cluster data into different levels. For example, these data data.module
, data.person_1.price
, data.person_1.tax
, data.person_2.price
, data.person_2.tax
, data.duration
will be stored like this:
1 data:{ 2 module: xxx, 3 duration: xxx, 4 person_1:{ 5 price: xxx, 6 tax: xxx 7 }, 8 person_2:{ 9 price: xxx, 10 tax: xxx 11 } 12 }
In a functioning excel, you should be able to see the calculated premiums and output fields in column K
once you put in the test values in column C
. You should be able to test various scenarios and cases to see if the premiums are returned correctly.
Important Notes for Pricing Excel
There is a list of output fields that should always be in the pricing excel. These are the default outputs that are already listed in the pricing template file. The fields are:
billing_cycles
gross_payment_amount
gross_premium
premium_tax
net_premium
net_net_premium
net_commission_total
tax_rate
duration_strategy
Apart from the above fields, there are some additional
In the output field, there is a required field called Duration Strategy. This field defines how the policy start and end date should be defined. There are 4 duration strategies that can be used.
Apart from the Pricing Excel file, you will also need the Validation Rules for all the quote data of your product. The Validation Rules are written in JSON and here is an example.
In the example, you can see that each of the input fields in your pricing would include 3 parts in the Validation Rules.
Name
This should correspond to the input field names in the Pricing Logic Excel.
Path
This is usually data
unless you have other data structures for your Pricing Logic input fields.
Validation
This usually include a few things:
data format - for example, string
, integer
, iso_date
required/not required - if the data is always required, you should put required
, otherwise, left empty
other validation rules - this can be (1) limiting the input to a list of allowed value; (2) checking the input value to be within certain range; any other validations
Once you have a functioning Pricing Logic Excel file and the Validation Rules, you can go to Product Builder → Pricing Logic and start configuring for your product.
You will need to remove all the values (column C) for all the input fields so that the pricing is clean and without any prefilled values.
Description
This is an optional field for you to describe this Pricing Logic Excel version.
Type
This is preselected as Excel type
File
This is the Pricing Logic Excel file
Payment Frequency
This defines the payment frequencies your product supports. This can be a multi-select if your product should support multiple frequencies. These is the supported frequency list
Single
Recurring - Yearly
Recurring - Half-Yearly
Recurring - Quarterly
Recurring - Monthly
When you finish the Pricing Logic Configuration, you can go to the Manual Test
. Here on the left hand side, you can see a dropdown of the payment frequencies and the input fields of the product; on the right hand side, you can see a blank output screen.
This area is for you to run manual test of your Pricing Logic, by inputting different values on the left hand side and click Run Test
on the top right. Once clicked, you will see the return of the pricing calculation on the right hand side in the output area.
Once the Pricing Logic is configured and you want to update the pricing calculation for your product, simply go back to Pricing Logic Configuration screen. There you can download the latest Pricing Logic Excel and make the necessary update to your Pricing Logic Excel file. Once the changes are made, you can upload it to the Product Builder (and update the Validation Rules if there are any changes there as well).
Duration Strategy
Must configured together
Must configured together
Must configured together
Must configured together