Pricing Logic

The most friendly premium calculation engine

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.

⭐Pricing Logic File

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.

18KB

KASKO Pricing Example.xlsx

In the KASKO Calculator sheet, you will see the below sections.

Section	Column	Description
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

📆 Duration Strategy

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.

fixed_start

Fix start duration strategy is typically used for policies that have a specific start date (usually selected by the customers) but have an undefined end date (open ending). This means the policy with this duration strategy does not expire (lapse) unless it is cancelled.

💡Must configured together

With fixed_start duration strategy, you must also include policy_start_date in the output field.

fixed_end

Fix end duration strategy is typically used for policies that have a fixed end date (usually set by the customer) but does not request the customers to select a start date. This means the policy with this duration strategy will expire (lapse) on a defined date unless it is cancelled prior to the date.

💡Must configured together

With fixed_end duration strategy, you must also include policy_end_date in the output field.

fixed_start_with_interval

Fix start with interval strategy is typically used for policies that have a specific start date (usually selected by the customers) and the policies last for a defined period of time (X days/months/years) counting from the start date. This means the policy with this duration strategy will expire on a dynamic end date depending on defined the start date and end date unless it is cancelled prior to the end date.

💡Must configured together

With fixed_start_with_interval duration strategy, you must also include policy_start_date and policy_validity_interval in the output fields.

For policy_validity_interval it should always be in the format P_D/P_M/P_Y depending on the duration set. Below are some duration examples and the corresponding values.

• 7 days - P7D

• 14 days - P14D

• 6 months - P6M

• 12 months or 1 year - P12M or P1Y

• 3 years - P3Y

fixed_start_and_end_date

Fix start and end end date duration strategy is typically used for policies that have a specific start date (usuaully set by the customers) and the policies will end on a specific end date (usually set by the customer). This means the policy with this duration strategy will start on the defined date and end on the defined date unless it is cancelled prior to the end date.

💡Must configured together

With fixed_start_with_interval duration strategy, you must also include policy_start_date and policy_end_date in the output fields.

⭐Validation Rules

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.

Pricing Logic Validation Rules Example

{
	"quote": [
		{
			"name": "field_1_number",
			"path": "data",
			"validation": "integer|required"
		},
		{
			"name": "field_2_module",
			"path": "data",
			"validation": "required|string|in:module_1,module_2"
		},
		{
			"name": "field_3_date",
			"path": "data",
			"validation": "iso_date|required|string|after:1 month from yesterday"
		}
	],
	"quote_patch": [],
	"pricing_variables": []
}

In the example, you can see that each of the input fields in your pricing would include 3 parts in the Validation Rules.

Field	Description
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.

Pricing Logic Edit Screen Example

Field	Description
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

Pricing Logic Validation Rule - Create Screen Example

Pricing Logic - Manual Test Screen Example

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).