Formulae for Repayment loans and mortgages

[AntoineM]

Hi everyone

I am having trouble calculating my loan payments and need help with the formula. Here are the loan terms:

Amount 1,000,000

Period 10 years

Interest  11.75

If I use google sheets to calculate the monthly payments

=PMT((Interest /12 ),( Period*12),1000000,1)

I get a value of -14,202.95  per month

I am using a formula I got from this thread

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/I-ve-got-a-Loan-Calculator-I-m-creating-in-Ap/m...

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

My formulae
(1000000) * ([InterestRate]/12) * POWER((1 + ([InterestRate]/12)), ([Bond years]*12)) / (POWER((1 + ([InterestRate]/12)), ([Bond years]*12)))

The results I get are different and Im not sure why, I get a value of - 9,792 per month

Please can someone assist?

[Suvrutt_Gurjar]

---

@AntoineM wrote:

My formulae
(1000000) * ([InterestRate]/12) * POWER((1 + ([InterestRate]/12)), ([Bond years]*12)) / (POWER((1 + ([InterestRate]/12)), ([Bond years]*12)))

---

In Appsheet a number division returns an integer. So you may want to try

(1000000) * ([InterestRate]/12.00) * POWER((1 + ([InterestRate]/12.00)), ([Bond years]*12.00)) / (POWER((1 + ([InterestRate]/12.00)), ([Bond years]*12.00)))

Please test well extensively.

Please see the associated tip by @Steve 

Fix for expression giving 0% or missing decimal pa... - Google Cloud Community

[AntoineM]

Hi Suvrutt_Gurjar

Thank you for your input. Ive tried all the suggestions by @Steve  and still get the same answere 9,791.67 

(1000000) * ([InterestRate]/12) * POWER((1 + ([InterestRate]/12)), ([Bond years]*12)) / (POWER((1 + ([InterestRate]/12)), ([Bond years]*12)))
9,791.67

(1000000) * ([InterestRate]/12.0) * POWER((1 + ([InterestRate]/12)), ([Bond years]*12)) / (POWER((1 + ([InterestRate]/12)), ([Bond years]*12)))
9,791.67

(1000000) * ( DECIMAL([InterestRate]/12.00)) * POWER((1 + DECIMAL([InterestRate]/12.00)), (DECIMAL([Bond years]*12.00))) / (POWER((1 + (DECIMAL([InterestRate]/12.00)), (DECIMAL([Bond years]*12.00))))

9,791.67

[Suvrutt_Gurjar]

Okay, thank you for the update. I will also test in a test app and update you.