Need help in calculating Insurance premium on my form

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.

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.

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ó:
...

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.

https://drive.google.com/open?id=1wYZGWeYpI2okRjsn_zX64CU8QOIo5H7V&authuser=alvaro.satrustegui@excess.es&usp=drive_fs

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

Look forward to your news , best regards.

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.

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.

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! 

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.

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. 

Hi Kirian,

Do you have any feedback to share?

Look forward to your news , best regards.

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.

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.

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

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

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

Thank you!

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