Mortgage Formula is not working for me

I Have Use this code here: =PMT(17%/12,2*12,5400) and it is not working. Please if you would kindly direct me to success. Thank you.

Have you tried an Excel column?

Or a javascript column?

function calculateMonthlyPayment(interestRate, numberOfPayments, loanAmount) {
  const monthlyInterestRate = interestRate / 12;
  const compoundFactor = Math.pow(1 + monthlyInterestRate, numberOfPayments);
  const monthlyPayment = (loanAmount * monthlyInterestRate * compoundFactor) / (compoundFactor - 1);

  return monthlyPayment;
}

const interestRate = 0.17; // 17%
const numberOfPayments = 2 * 12; // 2 years * 12 months
const loanAmount = 5400;

return calculateMonthlyPayment(interestRate, numberOfPayments, loanAmount);

Awesome, I will try this now, Thank you.


So, I used the Excel formula. This is what I used but it still didn’t not work. I do not have the option to run script or I am looking in the wrong place?

I see you Loan% is a text column. It may not be seeing it as a number. I would make sure that you are passing it a number without any extra characters, such as a percent symbol. It should be a decimal, such as 0.17. You may be able to run that percent through math column first to properly convert it to a number. Or you may be able to change the column type to a numeric column, but I’m not sure what that will give you.

Yes, sorry I have tried both methods and they both have seem to not work.

I feel like I am doing something wrong but is there an working template that I can analyze and study to get a better idea to accomplish this?

I don’t ever use the Excel Formula column, so I’m not sure. Maybe PMT is not supported…

You could always go with the javascript option. I’m more familiar with it and I’m pretty confident that it should work.

Okay Awesome, How would I accomplish this using JAVA?

I posted the javascript code above. Just copy and paste it to a JavaScript column. You can set up replacement values much like you did with the Excel column, except that the replacements are P1, P2, P3 instead of A1, A2, A3.

Sorry for being such a pain, but this is what my attempt looks like and I am getting a NaN

I think for the Excel Formula… remove the equal “:heavy_equals_sign: “ sign

1 Like

Can you show us the values of those p1, p2, p3 columns in your data? They should all be seen as numbers but there’s something off.

1 Like

Yes, that is very annoying. :pensive:

Here is the Values:
Data Mortgage

I’m going to bet money that it’s the ‘yrs’ in 30.00 yrs that’s causing the issue.

I didn’t think that it mattered due the “yrs” being added by the unit entry. Let me see if that is the issue.

Yeah, unfortunately no change.

I modified the javascript so it should be more forgiving. Could have been other things such as commas, but I’m not sure. I tested this and it seems to be working better.

function calculateMonthlyPayment(interestRate, numberOfPayments, loanAmount) {
  const monthlyInterestRate = interestRate / 12;
  const compoundFactor = Math.pow(1 + monthlyInterestRate, numberOfPayments);
  const monthlyPayment = (loanAmount * monthlyInterestRate * compoundFactor) / (compoundFactor - 1);

  return monthlyPayment;
}

const interestRate = p1.toFixed(5); 
const numberOfPayments = p2.toFixed(5)*12;
const loanAmount = p3.toFixed(5);

return calculateMonthlyPayment(interestRate, numberOfPayments, loanAmount);

Okay Awesome!, Thank you so much!