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

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?

0 16 1,787
16 REPLIES 16

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.

@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.

Hi @Steven_Coile. I am reaching out to you based on the response you gave to another AppSheet member about creating a loan calculator. I am hoping you can provide some guidance.

I am trying to create an expression to calculate mortgage payments. I have tried using a spreadsheet formula for calculating payments. I have also tried the expression posted by Jason_Denniston (above) and have been testing the following expression in the Initial Value field:

([_THISROW].[Interest on Loan]*[Principle Amount Borrowed]/12)*
(POWER(1+[Interest on Loan], 12)-1,[Number of Payments])

I am obviously missing something because while it doesn’t come up with an error message, the field doesn’t show on the detail view. Also, when I do the test data, the answer shows as a % even though it is a price column. I am not a math wizard and have researched the various formulas to see what I am missing. Any suggestions to get me my misery?

First, remove [_THISROW]., as it’s not needed and could cause problems.

The Initial value expression is only applied when the row is first added, not as needed. So if you’re looking for it to calculate for existing rows, Initial value may not be what you want.

Please provide more details.

Thanks. I will get [_THISROW] removed and will move the formula to the App Formula rather than the initial value.

Here are some additional details. The client wants to calculate the monthly payment (interest only) on a mortgage loan. I have the following columns: [Principle Amount Borrowed] = Mortgage amt; [Interest on Loan] = annual interest; [Number of Payments] = actual number of payments (not length of loan) over the life of the loan.

Here is how it is calculated in Excel:

Thanks for any help you can provide.

Have you tried using a virtual column rather than a regular column?

See also:

I did try a VC, but will give it another shot with removing the [_THISROW] piece. Thx.

@Steve I appreciate the support on sorting out the formula for calculating a mortgage payment. I was able to get it to work thanks to the added help of @Landan_Quartemont from QREW Technologies.

For anyone else wanting to add this mortgage payment formula to their apps, here is the final version:

[Mortgage Amount]*[Monthly Mortgage Interest] * POWER((1+[Monthly Mortgage Interest]) , [No of Payments]) / (POWER((1+[Monthly Mortgage Interest]) , ([No of Payments])) - 1)

A couple of things to note:

  1. the monthly mortgage interest is the (interest rate / 12). I created a virtual field for the monthly mortgage interest so that I didn’t have to add one more calculation into the formula.
  2. I used a virtual column for the Mortgage payment calculation

Thanks to you both for helping me get this to work.

@JTWhite6789  many thanks just what I was looking for. This formula is for repayment loans and mortgages. Worth mentioning as it may not be obvious at first glance.🙂

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

Is that a problem?

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

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

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

BINGO! Thanks gentlemen!

Hello. I am looking to calculate a monthly mortgage payment and am curious if you could post the final expression you used? Thanks.

Building a Loan Calculator in AppSheet sounds like a cool project. To calculate the loan amount using data from the Google Sheets column [Loan Amount], you don't necessarily need an SQL formula in AppSheet. Instead, you can directly refer to the column [Loan Amount] in your AppSheet app's calculations or expressions. By the way, if you're looking for professional assistance with mortgages, Nottingham Money Man is a reliable mortgage broker in Mansfield. They can provide expert guidance and support. Good luck with your Loan Calculator and feel free to reach out if you need any further help!

Hi @EugeneConner  we did  exactly that in our the Stress-Test application for landlords worrying about the impact of interest rate hikes and other stress-factors on their portfolio. The repayment formula used looks as follows and is based on the one provided by @JTWhite6789  earlier:

[RepaymentAmount]*([InterestRate]/12) * POWER((1+([InterestRate]/12)) , [LoanTermMonths]) / (POWER((1+([InterestRate]/12)) , ([LoanTermMonths])) - 1)

NB: [RepaymentAmount] is your drawdown amount, i.e. the amount of the loan you are actually using. This caters for offset mortgage calculations too.

Mortgage lenders thoroughly evaluate the numbers for every application, considering potential scenarios. For instance, they assess whether the figures would still be viable if the base rate increased by a x amount. Therefore, it is advisable to anticipate such situations, which is why they use a stress-test application to evaluate the mortgage's feasibility under various conditions. The same is true of most loan lenders.

I would be delighted to provide further explanation to anyone interested in the stress-test application. Feel free to ask any questions you may have about how it works.

Top Labels in this Space