I need help to get this done

💬 App Building
1

I am trying to make interestRate field generate a value based on the two input fields investmentAmount and tenor. There is a Google Sheet that has the data in a table. I just want to modify a form in a way that when a value is input in the investmentAmount field and a certain value is selected in the tenor field, it generates the rate based on the range the investmentAmount falls under and the tenor chosen, in accordance to the table.

image
image1238×382 18.6 KB

image
image768×131 7.32 KB

image
image1862×818 96.6 KB

Lastly, I want to be able to write a formula to calculate the "Total interest to be paid" based on the investmentAmount, Tenor and interestRate. I am a Newbie, please help.

1 Like
2

With the small amount of data you have for the investment range in google sheet, it can be written in a temporary state variable in Retool without needing to connect to google sheet (it takes a bit of work setting it up if you want to make the connection).

I suggest to go with temporary state variable for the time being, it would make things a lot easier.

image
Here I have created a temporary state variable as JSON data similar to what you would have in the google sheet.

Untitled
Untitled1915×920 74.9 KB

I have created a few Inputs and created a query to update the interest rate % on tenor change, the event handler in the tenor component will trigger the calculateRates query.

testApp.json (10.3 KB)
I have attached the app that I created, you can import it and see how it works. Populate the interest_rate data and test it out. Afterwards you can try to calculate the total interest to be paid.

2 Likes
3

Hi @lenti
Thank you so much. It worked. One last thing is: I am trying to make the total_interest field to generate a value based on the multiplication of the amount and rate field. Please help.

4

Using the calculateRates query I created, you can replace it with this.

interest_rates.value.map((rate) => {
  if (amount.value > rate.lowlimit && amount.value < rate.highlimit) {
    let getTenor = rate[tenor.value];
    rates.setValue(getTenor);
    let mapMonth = {
      three_month: 3,
      six_month: 6,
      nine_month: 9,
      twelve_month: 12,
    };
    /*
      A = P(1+RT)
      A = total accrued amount
      P = principal
      R = interest rate
      T = time period
    */
    let P = amount.value;
    let R = parseFloat(rates.value) / 100.0;
    let T = (mapMonth[tenor.value] * 30) / 365;
    let A = parseInt(P * (1 + R * T));
    
    //replace total_interest with the textInput component where you wanted the result.
    total_interest.setValue(A-P);
  }
});

The total interest formula I used maybe a bit off, feel free to change it.

3 Likes