Need help in calculating Insurance premium on my form

  • Profile Image
    Alsatrus65
    Asked on December 15, 2019 at 04:58 PM

    We have quite complex formulas that we can manage in a spreadsheet but we do not know how to insert these calculation within the forms , so our client can get a premium as a result of the data the fill in the form.

    Can you give us some advice of how to get this done?


    Best,


    Alvaro.

  • Profile Image
    AshtonP
    Answered on December 15, 2019 at 05:07 PM

    Thank you for writing to us!

    To have a calculation function in your form, you can add a calculation widget in your form. Here are some guides that will provide you more details about how form calculation works: 

    How to perform calculation in the form

    How-to-Assign-Calculation-Value

    How-to-Pass-a-Calculation-to-a-Payment-Field

    Conditional-Logic-Inserts-Text-Calculation-Into-A-Field

    Also, let us know your requirements and how you want your calculation to work, we can help you out in creating your form.

    We will be waiting for your response.

  • Profile Image
    Alsatrus65
    Answered on December 17, 2019 at 03:50 AM
    Good morning,
    Please find attached an excel file with the rating tool we use that
    includes the premiums matrix and the formulas for calculating the price.
    I also attach the proposal form that would be the form we want to build ,
    that contains the questions to be answered that drive to the final price.
    Let me know your thoughts. Best regards.
    Álvaro Satrústegui SilvelaCEO
    Calle Hermosilla 77, 2º Piso, Oficina 3
    Madrid, España. 28001
    Teléfono: +34 91 154 22 69
    Móvil: +34 629 047 800
    Web: www.exseluwa.com
    En cumplimiento de la legislación vigente en protección de datos
    personales, se advierte que los datos de carácter personal se incluirán en
    los ficheros creados por EXSEL UNDERWRITING AGENCY, S.L. La finalidad de
    los ficheros es la correcta gestión de los contratos de seguro y las
    prestaciones derivadas del mismo. El afectado podrá ejercer sus derechos
    ante EXSEL UNDERWRITING AGENCY, S.L., C/ Hermosilla 77- Piso 2, Oficina 3–
    28001 – MADRID; Tlf.: 911542269, e-mail: administracion@exsel.net como
    responsable del tratamiento.
    “Este mensaje y cualquier anexo se dirige exclusivamente a su destinatario
    y puede contener información CONFIDENCIAL sometida al secreto profesional.
    Si no es el destinatario final de este mensaje, le rogamos que nos lo
    comunique inmediatamente por esta misma vía y proceda a su destrucción.
    Queda notificado que su uso, distribución, impresión, copia y difusión por
    cualquier medio esta tipificada como delito en el artículo 197 y siguientes
    y 278 del vigente Código Penal”
    El dom., 15 dic. 2019 a las 23:07, Jotform () escribió:
    ...
  • Profile Image
    AndrewHag
    Answered on December 17, 2019 at 04:09 AM

    Unfortunately, we have not received the attachment. Please open this link in the browser and attach the files so we can check.

    Meanwhile, you may try using the Form Calculation and Spreadsheet widgets and see if that works for you.

  • Profile Image
    Vanessa_T
    Answered on December 17, 2019 at 04:38 AM

    It seems that you haven't created a form for this need of yours.

    Please create the form and add the necessary fields, you can then follow this guide to perform calculations:

    https://www.jotform.com/help/259-How-to-Perform-Form-Calculation-Using-a-Widget

    Once you already have the form, we can then help you with any difficulties you encounter.

    You may share to us which calculation on your spreadsheet corresponds to which field in your form so we can assist you. You may also share a screenshot if that can help you explain better.

  • Profile Image
    Alsatrus65
    Answered on December 28, 2019 at 02:51 PM

    Hi Vanessa,

    I have finished the form which is the following: https://form.jotform.com/193506322663053

    I also attach the spread sheet that include the rating matrix in the sheet called Calculations D&O : https://drive.google.com/file/d/1qUox3W8lP4n5YODBIpUw_ueS_r4PMigz/view?usp=sharing

    My problem is how to include the calculations based on this rating matrix. I know how to do it in an excel file but not with the widgets.

    There are only two questions that are related to the rating matrix:

    cc.Volumen de facturación consolidado (últimos 12 meses): *

    and

    LIMITE SOLICITADO:

    The first question refers to the turnover of the insured company. The second question is a drop down list where they can choose the limit of the insurance.

    So depending on both answers you can find in the matrix a premium that corresponds with the turnover and the limit you choose.

    Let me know if you can help me integrate the formulas I need for getting the premium from the rating matrix.

    Look forward to your news , best regards.


     
  • Profile Image
    Kiran
    Answered on December 28, 2019 at 04:52 PM

    I have checked the spreadsheet provided by you and understood that changing the Option from Dropdown field is changing the value in 'PRIMA DE RECIBO'. However, I wasn't sure how the calculation is performed on the Spreadsheet and which of the field is the turnover.

    It would be helpful if you can provide us with an example of a calculation and the fields associated so that we can assist you further. 

    We will wait for your response. Thank you! 

  • Profile Image
    Alsatrus65
    Answered on December 31, 2019 at 12:39 PM

    Hi Kiran,

    I have translated the headers and titles of the main fields so you can follow the explanation of the spread sheet.

    The first sheet "RATOR D&O" is the data introduction sheet.

    In row 9 you can find "INSURED LIMITS" ,  different options ,all 3 are a drop down list.

    This list is the same for the 3 options and represents the insured limits options of the product.

    In row 46 you can find "TURNOVER" where you introduce this data (D46)

    In row 49 you can find the Premium calculations for each of the 3 options. D49, F49 and H49

    All this 3 cells show a formula that is in the second sheet " Calculations D&O"

    In "Calculations D&O" you can find the following:

    Premium Schedule : Is a matrix of two entries , Turnover and Limits

    The figures within the matrix are the premiums. 

    Calculations: Contains the data of the RATOR D&O corresponding fields . the formulas are in C26, C27 and C28.

    The spreadsheet: 

    https://drive.google.com/file/d/1tG0aERhVLOQpbhg9FRppStuIfIYWXGy0/view?usp=sharing


    Look forward to your news , happy new year and best regards.


  • Profile Image
    Kiran
    Answered on December 31, 2019 at 01:47 PM

    Thank you for providing detailed information with the spreadsheet. Please allow us sometime to check this up and get back to you with relevant information.

    Thank you for your patience. 

  • Profile Image
    Alsatrus65
    Answered on January 13, 2020 at 03:40 PM

    Hi Kirian,

    Do you have any feedback to share?

    Look forward to your news , best regards.

  • Profile Image
    Vanessa_T
    Answered on January 13, 2020 at 05:57 PM

    I tried to use an Input Table and assigned calculation values based on your spreadsheet. Unfortunately, since the input table cannot have a default value, then the calculation of the premium cannot be done automatically.

    1578956224A.gif

    The only possible way I could think of to calculate the premium, is to use calculation conditions, one for each possible limit and turnover pair.

    1578956163The Easiest Online Form Builde

    1578956172The Easiest Online Form Builde

    Please see this guide to help you with creating these conditions:

    https://www.jotform.com/help/268-How-to-Insert-Text-or-Calculation-into-a-Field-Using-Conditional-Logic

  • Profile Image
    Alsatrus65
    Answered on January 17, 2020 at 05:21 PM

    Thanks Vanessa. Can you give me a link to the demo form please?

  • Profile Image
    jherwin
    Answered on January 17, 2020 at 10:17 PM

    I will ask my colleague for the demo form link, once she gets online, she will update you through this thread.

    Thank you!

  • Profile Image
    Vanessa_T
    Answered on January 20, 2020 at 07:39 AM

    Hello Alvaro,

    My apologies for the delayed response.

    Here's the link for the demo form: https://form.jotform.com/200126771941047

    Feel free to clone it to see how it is setup.

    How-to-Clone-an-Existing-Form-from-a-URL