Mortgage Formula is not working for me

Ask for Help
1

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.

2

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

Awesome, I will try this now, Thank you.

4

Formula Error
Formula Error383Ɨ725 26.3 KB

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?

5

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.

6

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

7

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?

8

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.

9

Okay Awesome, How would I accomplish this using JAVA?

10

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.

11

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

Java Attempt
Java Attempt718Ɨ906 64.1 KB

12

I think for the Excel Formulaā€¦ remove the equal ā€œ:heavy_equals_sign: ā€œ sign

1 Like
13

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
14

Yes, that is very annoying. :pensive:

15

Here is the Values:
Data Mortgage

16

Iā€™m going to bet money that itā€™s the ā€˜yrsā€™ in 30.00 yrs thatā€™s causing the issue.

17

I didnā€™t think that it mattered due the ā€œyrsā€ being added by the unit entry. Let me see if that is the issue.

18

Yeah, unfortunately no change.

19

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

Okay Awesome!, Thank you so much!