Question

Forms function equivalent to excel PMT

Forms

Updated June 5, 2023

asked on February 13, 2018

Since the function library in Forms doesn't contain an equivalent to MS Excel's PMT function, I'm trying to put together one long-hand. Here's the standard equation:

P = (Pv*R) / [1 - (1 + R)^(-n)]

Forms Variables are:

Pv = Loan_Amount
R = Fixed_Rate/Payment_Frequence (Payment_Frequency is a drop-down field with word values that I'm assigning numeric values to)
n = Ammortization (total number of payments over life of the loan)

Here's the formula I've come up with. I did it based on 1/(1+R)^n instead of (1+R)^(-n) just in case Forms couldn't handle the negative exponent.

DIV(MULT(Loan_Amount,DIV(Fixed_Rate,Payment_Frequency)),SUB(1,DIV(1,POWER(SUM(1,DIV(Fixed_Rate,Payment_Frequency)),(Amortization)))))

I keep getting a calculation error, though it's not telling me where the issue is. Any ideas where I'm going wrong?

0 0

Answer

SELECTED ANSWER

replied on February 13, 2018

Hi Paul

FYI, I recreated your formula in 10.3 and it works, when all of the fields included in the form are populated. It will throw an error when any of the fields are not populated with a value.

=DIV(MULT(Loan_Amount,DIV(Fixed_Rate,Payment_Frequency)),SUB(1,DIV(1,POWER(SUM(1,DIV(Fixed_Rate,Payment_Frequency)),(Amortization)))))

1 0

Replies

replied on February 13, 2018

Try PRODUCT instead of MULT.

0 0

View 3 previous replies

replied on February 13, 2018

Sorry, should have mentioned this, but I did try it with both "PRODUCT" and "MULT". Same result either way.

0 0

SELECTED ANSWER

replied on February 13, 2018

Hi Paul

FYI, I recreated your formula in 10.3 and it works, when all of the fields included in the form are populated. It will throw an error when any of the fields are not populated with a value.

=DIV(MULT(Loan_Amount,DIV(Fixed_Rate,Payment_Frequency)),SUB(1,DIV(1,POWER(SUM(1,DIV(Fixed_Rate,Payment_Frequency)),(Amortization)))))

1 0

replied on February 13, 2018

Great to hear. Thanks for going to that much trouble for me, Steve. I think the issue might be that two of the variables I'm using are on the same form as the field doing this calculation. I think it's trying to calculate after the first field is filled in, while the second is still blank, resulting in the error. I may have to find a way to pull the data from previous forms in the process.

Again, thanks for the help!

0 0

replied on February 14, 2018

Steve, just a quick update. I was able to figure out the problem. Turns out that if one of the variables going into a calculation comes from a text field, it will fail. As soon as I changed the "Amortization" field to a number field instead of a text field, it worked. Thanks again for your help, as I would have kept suspecting the formula of being the issue.

0 0

replied on February 14, 2018

Great to hear Paul. Strangely enough though, all my fields were number fields when I was getting the error when Fixed Rate, Payment Frequency or Amortization did not contain a value or were 0. Wonder if this is the difference between versions as I was on 10.3. Something you may to watch out for if you haven't already upgraded. So wish LF would add ISERROR to their supported Functions

0 0

replied on February 14, 2018

I am on 10.3 and saw the same behavior. I fixed it with an IF statement in the function so if the Amortization field is empty, it just fills the payment amount with 0. That works for me as the Amortization field is the last to be filled out. Kind of dumb that I have to do it that way, but it worked.

0 0

replied on June 5, 2023

Hi,

I have a doubt. What value do you pass in place of Payment_Frequency? What numeric value do you pass if its monthly, quarterly, half-yearly or annual?

For eg., if the loan tenure is in Months (6,12,18,36...), what value should we take in place of Payment_Frequency in the above mentioned formula?

0 0

You are not allowed to follow up in this post.

Question

Question

Forms function equivalent to excel PMT

Answer

Replies

Sign in to reply to this post.