Input Table: Loan calculation

  • Profile Image
    afckredieten
    Asked on January 01, 2018 at 06:45 AM

    Hello,

    Still trying to perfect my loan simulation tool.

    I've got it working, cost car, already payed, gives the loan amount.

    Depending on de hight of the loan amount, de AKP changes accordingly, as the possibility to choose in numbre of months to pay. In the end visitors get the monthly amount to pay, and the total at the end of the choosen period.

    Now i want to present not only the monthly payement and the totall. But also some sort of table/matrix (whatever its called) where the rows are the number of months from the dropdown where they choose initialy theire number of months. an example

    If loan amount is 2501€ the AKP¨(of in my case the JKP (same in dutch)) is 3.24%

    and visitor can choose to repay this or in 24 months or in 30 months

    loan amount between 2501 and 3700 its 24 and 30 months

    when amount is 3701-5600€ it is 24months, 30,36

    for 5601-7500 it is 24 to 42.

    And so one, the steps are here steps of 60months but from 60 months the steps are 12months.

    This works, i get the result if people go for loan amount 6000€  the visitor can choose from a dropdown with 24:30,36,42 months . for 7501€ it goes to 48months.

    Now, the get monthly payement for 6000€ for the choosen period of for instance 36months amount X.

    But i want also a table/sheet where the monthly payements are shown for all the other periods, number of months. In or example for 6000€ rows with the loan perod 24,30,36,42.

    I hope i make my question clear. My calcultator you'll find here https://form.jotformeu.com/60561555261352

    thanks for the assisance

  • Profile Image
    Mike_G
    Answered on January 01, 2018 at 11:25 AM

    I believe what you are trying to do is possible.

    To present your calculation in a matrix table, you can follow the instructions in this guide — Calculate-Values-in-the-Input-Table-Through-Condition

    We can help you create the calculation that will happen in the table, but first, you need to tell us what exactly do you need to show on the table.

    Do you need multiple matrix tables? One for each number of months that can be selected.

    Or just one matrix table where the row headers are the number of months (1st, 2nd, 3rd, etc...) and the column headers are the available monthly payments (24 months, 30 months, 36 months, etc...).

    Please clarify what would you want to be as row headers and column headers.

    We will wait for your response.


  • Profile Image
    aubreybourke
    Answered on January 01, 2018 at 04:11 PM

    Sorry your response didn't come through there. 

    I took a look at your form and can see you need to create update/calculation conditions to work with your input table.

    For example:

    1514840823The Easiest Online Form Builde

    This condition fills the APR for 24 months.

    For example:

    1514841016The Easiest Online Form Builde

    You would need to create similar conditions for each value in your input table.

    This guide is useful:

    Calculate-Values-in-the-Input-Table-Through-Condition

  • Profile Image
    afckredieten
    Answered on January 07, 2018 at 10:28 AM

    Hello,

    Finaly found some time to continue working on my tool.

    I'm still stuck, here is my problem

    This is more or less the table i want to use.

    The rows are as many as the total of months that are possible for the loan amount.

    Like i said earlier, you've got 8 dropdowns higher up the form, with each a set of months

    24-30 / 24-30-36 .... /24-30-36-42-48-60-84-120 as last dropdown.

    These dropdowns are shown if loan amount is greater or less then certain amounts

    2501-3700 / 3700-5601€.... AND for for the 3 highest dropdowns, theire is another factor that decides which dropdown is shown, new cars can lend in more months than an old car ...

    Now back to the table.

    Example, visitor puts cost car 6000€ and 2500€ payed in advance. Loan amount will be 4500€

    because it's a new car, the AKP¨/JKP is 3.24%, and for loan period only possible for 24,30 months. The customer chooses 30 months, this gives for monthly payement (which is the goal of the calculator)= 121.61€ and in total to pay after 30 months is 3648.3€

    So far no problem. But now i want a table, where these numbers are filled in the appropriate row, but also for the other months from the dropdown earlier on.

    How can i achieve this, not only the calculated values, but for instance also the AKP, must be filled

    1515337545table.PNG

    I've cloned and translated my calculator so you can see how it works, the loan formula is quite simple, you'll find it behind monthly payement


    Thanks in advance

  • Profile Image
    BDAVID
    Answered on January 07, 2018 at 01:47 PM

    What is the translated clone version? If I understood correctly, you would like to populate a new Input Table with data from other fields? Could you indicate which field if that is the case?

  • Profile Image
    afckredieten
    Answered on January 07, 2018 at 01:59 PM

    sorry forgot to put the link https://form.jotformeu.com/80063708984363

    The fields or values are the in the table in the form present 

    but finanlly it's the same as the calculator

    1. Loanamount

    2. number of months

    3. AKP (annual interest rate)

    4. Monthly payement

    5. total to pay at the end of the period

    This are the collumms, there is a row for each choice in the period dropdown, so for 24months is one row, 30months and so one.

    The table must present for each choice of months that's shown higher up the calculated monthly payement ....

    The dropdown with the number of months depends on the loan amount, on the car's age, and for one dropdown (120months) on the type of car


  • Profile Image
    jonathan
    Answered on January 07, 2018 at 04:31 PM

    If I understand correctly, what you wanted to achieve is to populate the Input table with the values from the input fields (Textboxes) 

    Ex:

    1515360483zzz 2018-01-08 05.26.20.png

     

    You can do this using the Update/Calculate condition tool of the form.

    Example:

    1515360626zzz 2018-01-08 05.28.59.png

    1515360800zzz 2018-01-08 05.28.32.png

     

    you can test my demo form in action https://form.jotform.com/80065935641963 with the calculate conditions applied.





  • Profile Image
    afckredieten
    Answered on January 07, 2018 at 04:59 PM

    Not quite what i need. I'll try to explain myself.

    When a visitor use the calculator, he gets one monthly payement, for the loan amount.

    He fills out cost car, advance, he gets loan amount. because value of loan amount he gets a certain AKP, AND one of the 8 dropdowns with a list of months. Which list/dropdown depends of the loan amount.  From this dropdown the visitor chooses ONE valu, for instance 30months.

    The calculator, will now give the monthly value for this 30 months, and the total to pay.

    Ok, are you with me?

    Now, if you go to the beginning of my question, i want to realize that after this a table is presented, with the other months from the dropdown with each time a corresponding monthly payement, total to pay, and the values from

    For instance, when the dropdown gives 24/30/36/42 months to choose out of. The visitor chooses 36 months, the calculation wil give the monthly payement for 36 months and the total as well.

    Now the table will give the monthly and total payements for the other choices, so for 24/30 and 42 months.

    Thats my question, it goes futher than taking over values from the calculator like you show me.

  • Profile Image
    jonathan
    Answered on January 07, 2018 at 05:32 PM

    Thank you for providing us with detailed explanations of the calculation process.

    For instance, when the dropdown gives 24/30/36/42 months to choose out of. The visitor chooses 36 months, the calculation wil give the monthly payement for 36 months and the total as well.

    Now the table will give the monthly and total payements for the other choices, so for 24/30 and 42 months.

    In my assessment, you can do achieve this using the same method I have described previously. You will need to use the update/calculate condition tool to present the needed table with all the other months with calculations populated with values as well.

    1. Take note that you will have to do all the calculations for the months table (i.e. 24/30/36/42) outside the tables.

    2. You can use the Calculation widget to calculate the results base on the selected month and for each of the other month.

    3. Then you will populate the tables with the values from the Calculation widget.

    With your current design, the calculations will not be too complex, but it will require multiple condition calculations for each of the months table. So it might be overwhelming at a glance.

    Alternatively, I suggest you also check Spreadsheet widget which you may want to use as the viewer for the monthly calculation tables.

    With the spreadsheet widget, you will have to create less conditions/calculations and tables since you can have the user use the spreadsheet as the interactive calculator table.


  • Profile Image
    afckredieten
    Answered on January 08, 2018 at 08:49 AM

    Hello

    Ok spreadsheet seems pretty good. But i'm wondering

    How do i insert data in the spreadsheet. I need to use the data/submission from the calculator

    for instance loan amount, this isnt a fix amount, so i can prefill it in advance, 

    I think the spreadsheet is only good if working with fix numbers and amounts. I hope i make myself clear.

    The sheet that i needs, the table that i needs is filled with data from the calculator, the AKP can be 4 different %, the loan amount depends on the calcul from cost car and payed in advance...

    I dont seem to find some explinantion how i get these numbers in the spreadsheet

  • Profile Image
    aubreybourke
    Answered on January 08, 2018 at 10:15 AM

    You are right. There is no way of getting the variables into the spreadsheet widget.

    I think your previous approach using the input table would work better.

    You can use update/calculate conditions to populate the table.