Skip to main content

How do I write the equivalent of Excel’s PMT formula in AirTable field that produces the equivalent result in Excel? In Excel it’s =PMT(rate,nper,pv). In Excel I adapted it as =PMT(rate/12,nper12,pv)-1** which also produced the monthly payment as a positive value.


I found this in the community: ({Monthly Interest Rate}*{Loan Amount})/(1-POWER((1+{Monthly Interest Rate}),-{Number of Monthly Payments}))


I adapted it to this: ({Monthly Interest Rate/12*{Loan Amount}})/(1-POWER((1+{Monthly Interest Rate}/12),-{Number of Monthly Payments))/10


It was close but not exact and the small difference becomes a much larger difference in another calculation.


Is there a way to do this?

What you found from the community is correct. I changed it slightly to be more explicit about the sign change on number of monthly payments in the exponent. Why isn’t this formula what you want? Why did you “adapt” it to something that looks unrelated to the excel PMT formula?


({Monthly Interest Rate}*{Loan Amount})/(1-POWER((1+{Monthly Interest Rate}),-1*{Number of Monthly Payments}))

What you found from the community is correct. I changed it slightly to be more explicit about the sign change on number of monthly payments in the exponent. Why isn’t this formula what you want? Why did you “adapt” it to something that looks unrelated to the excel PMT formula?


({Monthly Interest Rate}*{Loan Amount})/(1-POWER((1+{Monthly Interest Rate}),-1*{Number of Monthly Payments}))


Here’s why.

My loan amount was $720K. My interest rate is 4%. My Loan Term is 10 years.


When I used the formula unaltered it returned a value for monthly payment of $88,768.48.


When I adapted it I got a payment value of $7,3332.66 , which is much closer to the PMT formula result in Excel at $7,289.65. Still this $43.01 difference affects another calculation with a much greater discrepancy in comparison.


Below are the results using both formulas. SMP a duplicate field of Monthly Seller Payment.


Here’s why.

My loan amount was $720K. My interest rate is 4%. My Loan Term is 10 years.


When I used the formula unaltered it returned a value for monthly payment of $88,768.48.


When I adapted it I got a payment value of $7,3332.66 , which is much closer to the PMT formula result in Excel at $7,289.65. Still this $43.01 difference affects another calculation with a much greater discrepancy in comparison.


Below are the results using both formulas. SMP a duplicate field of Monthly Seller Payment.



Hey James. What monthly interest rate did you use? Does your formula go something like…


( (0.04/12) * 720000) / ( 1 - POWER( 1+(0.04/12), -1 * (10*12) ) )

Hey James. What monthly interest rate did you use? Does your formula go something like…


( (0.04/12) * 720000) / ( 1 - POWER( 1+(0.04/12), -1 * (10*12) ) )


Thanks augmented that did the trick.

Reply


Terms & Conditions