# Pricing Logic 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**. #### :star:**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. {% file src="" %} In the KASKO Calculator sheet, you will see the below sections.
SectionColumnDescription
Input FieldsBelow 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 FieldsBelow 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:

  1. Single Payment (not recurring) - billing_frequency = 1 & billing_frequency_unit = N
  2. Monthly Payment (recurring) - billing_frequency = 1 & billing_frequency_unit = M
  3. Quarterly Payment (recurring) - billing_frequency = 3 & billing_frequency_unit = M
  4. Half-Yearly Payment (recurring) - billing_frequency = 6 & billing_frequency_unit = M
  5. Yearly Payment (recurring) - billing_frequency = 1 & billing_frequency_unit = Y
Output FieldsBelow 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. :exclamation::exclamation:**Important Notes for Pricing Excel**:exclamation::exclamation: 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 fields. :calendar: **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. :bulb:**Must be 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. :bulb:**Must be 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. :bulb:**Must be 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. :bulb:**Must be configured together** With fixed\_start\_with\_interval duration strategy, you must also include `policy_start_date` and `policy_end_date` in the output fields.
#### :star:**Validation Rules** Apart from the Pricing Excel file, you will also need the Validation Rules for all the quote data of your product. Validation Rules are written in JSON, below 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.
FieldDescription
NameThis should correspond to the input field names in the Pricing Logic Excel.
PathThis is usually data unless you have other data structures for your Pricing Logic input fields.
Validation

This usually includes a few things:

  1. data format - for example, string , integer , iso_date
  2. required/not required - if the data is always required, you should put required , otherwise, left empty
  3. 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. {% hint style="warning" %} 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. {% endhint %}

Pricing Logic Edit Screen Example

FieldDescription
DescriptionThis is an optional field for you to describe this Pricing Logic Excel version.
TypeThis is preselected as Excel type
FileThis 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 - Four-Weekly
  • 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).