Airtable Cobuilder is here! Learn more about our new no-code app creation feature, powered by AI on the Airtable Academy

# Re: How to write the equivalent of Excel's PMT formula in AirTable field that produces the equivalent result in Excel?

Solved
1876 2
cancel
Showing results for
Did you mean:
4 - Data Explorer

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?

1 Solution

Accepted Solutions
10 - Mercury

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) ) )

4 Replies 4
10 - Mercury

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}))`

4 - Data Explorer

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.

10 - Mercury

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) ) )

4 - Data Explorer

Thanks augmented that did the trick.