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?
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:
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.
User | Count |
---|---|
36 | |
31 | |
30 | |
20 | |
17 |