Help

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

Solved
Jump to Solution
1218 0
cancel
Showing results for 
Search instead for 
Did you mean: 
James_Bolduc
4 - Data Explorer
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

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

See Solution in Thread

4 Replies 4
augmented
10 - Mercury
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}))

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.

image

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.