You are viewing limited content. For full access, please sign in.

Question

Question

Forms function equivalent to excel PMT

asked on February 13

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

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

Try PRODUCT instead of MULT.

 

 

0 0
replied on February 13

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

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

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

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

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

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
You are not allowed to follow up in this post.

Sign in to reply to this post.