I've got a Loan Calculator I'm creating in Ap...

(Jason Denniston) #1

I’ve got a Loan Calculator I’m creating in AppSheet that’s pulling Rate, Loan Amount, and Term of a loan data from Google Sheets.

Google Sheet name is AppData and the columns are [Loan Amount], [Rate], and [Term].

How would I calculate the loan amount in AppSheet, what’s the SQL formula to get this information to populate?

(Jason Denniston) #2

Not really sure, but I’m assuming the formula might look something like this?

[Payment]=([Rate]/(POWER([Rate],[Total_NumPayments])-1)))*[Loan Amount]

But, I can’t get it to populate the Payment Amount field.

Wouldn’t I use _This row with Any SELECT to find the row from data that the user selected and then perform the calculation? What would this equation look like?

Any help is appreciated.

(Steven Coile) #3

@Jason_Denniston Your formula looks good, except for the [Payment] at the beginning. Unlike other programming languages, the = operator is not an assignment operator; instead, it is a comparative equality operator.

To populate a column (presumably [Payment]) with the result of this expression, set the Initial value for that column to the formula. When a new row is saved for the first time, the formula will be applied and the result saved in that column.

(Jason Denniston) #4

I’m not saving or updating any data in the form…it’s basically just read only.

Is that a problem?

(Jason Denniston) #5

plus.google.com - i = Column Name - Rate n= Column Name - Total_Periods I’m trying to perform … i = Column Name - Rate n= Column Name - Total_Periods I’m trying to perform … plus.google.com

(Jason Denniston) #6

Here’s the formula: {[(1 + i) ^n] - 1} / [i(1 + i)^n]

if n=360, i=.005, then D should = 166.7916

(Jason Denniston) #7

BINGO! Thanks gentlemen!