Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.

# Thread: Interest Paid IPMT Excel equivelant

1. ## Interest Paid IPMT Excel equivelant

Does anyone have the formula or script to calculate the remaing interest left on a loan?

Using JS of course

Thanks

2. Interest only? Not the principal? Or principal + interest?

Most loans nowadays don't have prepayment penalties, so if you are ready to pay one off, you only owe the remaining principal.

So I'm not sure your question, as given, makes sense.

The title of your query made more sense: How much interest has been *paid* on a loan, at a given point in time? That's a sensible question.

3. It is not for payoff, I want to calculate the remaing interest that will be paid on a loan if all remaing scheduled payments are made

4. Okay...not too hard.

The basic formula for finding the payment amount on a loan is:
Code:
`PMT = PV / ( ( 1 - Math.pow( 1 + RATE, - PERIODS) ) / RATE );`
Where PMT is the payment, PV is the PresentValue (the loan amount), PERIODS is the number of periods (month, weeks, years, whatever) and RATE is the interest rate per PERIOD (that is, monthly rate if PERIODS are months) expressed as a fraction.

So, for example:
Code:
```PV = 150000;
YEARS = 30;
PERIODS = YEARS * 12;
APR = 4.25; // percent
RATE = 4.25 / 12 / 100; // convert APR to monthly rate

PMT = PV / ( ( 1 - Math.pow( 1 + RATE, - PERIODS) ) / RATE );

document.write(PMT);```
(Not good JavaScript; strictly for demo purposes.)

To find the interest paid over the life of a loan is simple:
Code:
`TOTALINTEREST = ( PMT * PERIODS ) - PV;`
That is, the total of payments less the original loan amount.

Now, the beauty of the payment formula is that you can turn it around:
Code:
`PV = PMT * ( ( 1 - Math.pow( 1 + RATE, - PERIODS) ) / RATE );`
That is, given the payemt, rate, and periods, you can calculate the loan amount.

So say that you have paid 10 years on a 30 year loan. Clearly, there are still 20 years, or 240 months, still to go. So to find out the PresentValue of the loan, with 240 months to go, you just plug in the PMT value you got from the first calculation and change the PERIODS to 240 and calculate the PV. Then, again, you get the interest paid over those next 20 years by subtracting the PV from the total of payments.

5. ## Users who have thanked Old Pedant for this post:

billboy (02-19-2013)

6. Try this:
Code:
```<!DOCTYPE html>
<html>
<title>Remaining interest on a loan</title>
<div>
<form id="loanForm">
Original loan amount: <input name="loan" /><br/>
Number of months of loan: <input name="months" /><br/>
Annual interest rate: <input name="apr" /><br/>
Number of months remaining on loan: <input name="remaining" /><br/>
<input name="calc" type="button" value="Calculate" />
<hr/>
For life of loan:<ul>
<li>Total of payments: \$<span id="pmtTotal"></span></li>
<li>Total interest: \$<span id="intTotal"><span></li>
</ul>
Remaining on loan:<ul>
<li>Total of payments: \$<span id="rpmtTotal"></span></li>
<li>Total interest: \$<span id="rintTotal"><span></li>
</ul>
</div>

<script type="text/javascript">
(
function( )
{
var form = document.getElementById("loanForm");
form.calc.onclick = function( )
{
var pv = Number(form.loan.value);
var periods = Number(form.months.value);
var annual = Number(form.apr.value);
var remain = Number(form.remaining.value);
if ( isNaN(pv) || isNaN(periods) || isNaN(annual) || isNaN(remain) )
{
return;
}
var rate = annual / 12 / 100; // convert to monthly rate
var pmt = pv / ( ( 1 - Math.pow( 1 + rate, - periods ) ) / rate );
var pmttotal = pmt * periods;
var inttotal = pmttotal - pv;
document.getElementById("pmtTotal").innerHTML = pmttotal.toFixed(2);
document.getElementById("intTotal").innerHTML = inttotal.toFixed(2);

// okay so what is pv of remainder of loan?
var rpv = pmt * ( ( 1 - Math.pow( 1 + rate, - remain ) ) / rate );
var rpmttotal = pmt * remain;
var rinttotal = rpmttotal - rpv;
document.getElementById("rpmtTotal").innerHTML = rpmttotal.toFixed(2);
document.getElementById("rintTotal").innerHTML = rinttotal.toFixed(2);
}
}
)();
</script>
</body>
</html>```
****
EDIT: Change the ID of the <form> to keep Philip and Felgall happy.

7. You can try this example:
Original loan amount: 2124.33
Number of months of loan: 24
Annual interest rate: 12
Number of months remaining on load: 3

And that happens to be one that I worked out almost 40 years ago and it's the right answer.

8. May I point out that you have assigned the same word "loan" to the form id and the name of an input field (the original loan amount)?

That seems to me to be a recipe for confusion.

And presumably the number of months must be integer. All input values must be positive, so I would suggest use Math.abs()

9. Ehhh...it doesn't look like he cares about the answer, so I don't think I will much, either.

The validation was purposely weak (see the comment only re use of alert), as I didn't want to spend much time on it. Not knowing what kind of loans he is talking about, I'd probably also want to restrict the interest rate to a reasonable range (e.g., so somebody doesn't put in 0.055 in place of 5.5%). I don't think I agree with using Math.abs(). I would rather reject a negative value, on the basis that the - may have been a typo.

I know your objection to reusing a name, but an id for a <form> and a name for an <input> are unlikely to be confused, as they won't be used in the same way. But yes, I should have called it "loanForm" or such, instead.

10. Originally Posted by Old Pedant
I know your objection to reusing a name, but an id for a <form> and a name for an <input> are unlikely to be confused, as they won't be used in the same way.
However versions of Internet Explorer that pollute the id namespace with name attributes will potentially confuse the two.

11. Originally Posted by felgall
However versions of Internet Explorer that pollute the id namespace with name attributes will potentially confuse the two.
Hasn't been my experience.

If an element is given an ID, then that overrides the name-promoted-to-id in every case I have seen.

Don't forget, I still have hundreds of page hits from MSIE 7 and below (even hundreds of MSIE 5.5!) every week. And though I don't have many cases where I have an ID and NAME that are the same, I'm sure there are some.

Yes, if you have two elements with the same NAME and no element with that ID, then older MSIE will get confused. But that's not the same as one with ID and one with NAME.

Not saying it can't happen. Just that I haven't seen it happen.

But okay, I'll keep you and Philip happy. I changed the ID in my code to "loanForm".

12. Originally Posted by Old Pedant
If an element is given an ID, then that overrides the name-promoted-to-id in every case I have seen.
I was thinking in more general terms rather than your specific code.

That the id overrides the name would have the potential to confuse someone who saw the HTML with the name and was expecting to be able to reference it directly the way IE allows only to find that the variable they were trying to use actually points to an id elsewhere in the HTML that they didn't even notice until their code didn't work.

Not that I am suggesting using those variables anyway - they only really give an additional reason to wrap code inside anonymous functions so that you don't get accidental clashes with the extra variables that IE creates.

13. Originally Posted by Old Pedant
But okay, I'll keep you and Philip happy. I changed the ID in my code to "loanForm".
I am sure we have discussed this before.

Code:
```<input id = "text">

<script type = "text/javascript">
text = "Hello World";  // global variable
document.getElementById('text').value = text;
</script>```
Does not work in IE9 (or any other version).

To me the solution is simple. Never, ever, duplicate HTML names/ids/Javascript variable names. Also make the names descriptive ("loanamount", not "la"or "xyz").

Although a global variable is not the same as a local variable of the same name, it is bound to create confusion, and is simply pointless. The days when the names of variables were limited to a single letter have long gone!

IMO the name of an HTML element and the id of that element should be the same word. But as you say, there is often no point in assigning ids to form elements.

14. No disagreement, Philip. You see any place in my code where I have a variable name the same as an element id or name? This is a different case: Having an id of one element that is the same as the name of another. NOT trying to say it's a good idea, just that it doesn't cause conflicts. But in any case, I changed the code.

15. Thanks