Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Coder
    Join Date
    Feb 2013
    Posts
    44
    Thanks
    2
    Thanked 0 Times in 0 Posts

    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
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Feb 2013
    Posts
    44
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    billboy (02-19-2013)

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    Try this:
    Code:
    <!DOCTYPE html>
    <html>
    <head>
    <title>Remaining interest on a loan</title>
    </head>
    <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) )
              {
                  alert("All values must be numbers"); // alert obsolete, bad usage
                  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.
    Last edited by Old Pedant; 02-17-2013 at 10:44 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Philip M's Avatar
    Join Date
    Jun 2002
    Location
    London, England
    Posts
    18,154
    Thanks
    203
    Thanked 2,548 Times in 2,526 Posts
    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()
    Last edited by Philip M; 02-17-2013 at 12:59 PM.

    All the code given in this post has been tested and is intended to address the question asked.
    Unless stated otherwise it is not just a demonstration.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by Old Pedant View Post
    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.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    Quote Originally Posted by felgall View Post
    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".
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by Old Pedant View Post
    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.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #12
    Supreme Master coder! Philip M's Avatar
    Join Date
    Jun 2002
    Location
    London, England
    Posts
    18,154
    Thanks
    203
    Thanked 2,548 Times in 2,526 Posts
    Quote Originally Posted by Old Pedant View Post
    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.
    Last edited by Philip M; 02-18-2013 at 07:51 AM.

    All the code given in this post has been tested and is intended to address the question asked.
    Unless stated otherwise it is not just a demonstration.

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #14
    New Coder
    Join Date
    Feb 2013
    Posts
    44
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks

  • #15
    Regular Coder
    Join Date
    May 2012
    Location
    France
    Posts
    224
    Thanks
    0
    Thanked 32 Times in 30 Posts
    See this page to translate in English


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •