Click here to Skip to main content
Click here to Skip to main content

Some functions for calculating loans and car leases

, 10 Mar 2003
Rate this:
Please Sign up or sign in to vote.
Maybe not exciting, but definitely handy.

Introduction

I know, this is probably not very exciting stuff, but may turn useful for some of you one day… Maybe for calculating your car / house repayments, or maybe writing a program that needs to calculate this.

The basis for this set of function is derived from the Microsoft Excel formula documentation on the subject and with lots of help of our own in house “Math teacher” (thanks Andrew) we managed to put this together for the benefit of the community and as our humble contribution to the forum.

Firstly – here is the Excel formula in its raw format

loan equation

Now to some relevant terminology…

A “loan” is made up of several elements, each playing a different role in the equation. At times, you may need to know different things about the loan; therefore I provided here several functions.

 

NPV or Net Present Value, this is the amount of the loan
FV or Future Value (or sometime known as Residual, or ‘Pay-Out’) is the amount of money that will still be outstanding as the final payment AFTER all the payments are made.
NumPay Number of Payments / Installments, for example if a monthly payment is made for a period of 5 years, this will equal 60.
IntRate Interest Rate, expressed as say 10.00 (for 10%)
bStart this should be a 0 or 1, if 0, each repayment is made at the end of the period (end of month) otherwise, payments are made ahead of each period.

OK – enough of that blurb… here are the actual functions:

double MKCalcPayment(int NumPay, double IntRate, double NPV, double FV, 
                     BOOL bStart)
{
    IntRate /= 1200.00;
    double P = (- NPV * pow(1+IntRate,NumPay) + FV) / 
               ((1 + IntRate * bStart)*((pow((1 + IntRate),NumPay) - 1) / 
               IntRate));
    return P * (-1); // Just convert it into a positive value.
}
 

double MKCalcInterest(double NumPay, double Payment, double NPV, double FV, 
                      BOOL bStart)
{
    // Start with an arbitrary 1% Interest rate
    double IntRate = 1.00 / 1200.00;
    NPV *= (-1);
    double iPer = log((Payment + Payment*IntRate*bStart - FV*IntRate) / 
                  (NPV*IntRate + Payment + Payment*IntRate*bStart) ) / 
                  log (1 + IntRate);
 
    if (iPer > NumPay)
    {
        while (iPer > NumPay)
        { 
            IntRate -= 0.000001;
            iPer = log((Payment + Payment*IntRate*bStart - FV*IntRate) / 
                       (NPV*IntRate + Payment + Payment*IntRate*bStart) ) / 
                   log (1 + IntRate);
        }
    }
    else
    {
        while (iPer < NumPay)
        {
            IntRate += 0.000001;
            iPer = log((Payment + Payment*IntRate*bStart - FV*IntRate) / 
                       (NPV*IntRate + Payment + Payment*IntRate*bStart) ) / 
                   log (1 + IntRate);
        }
    }
    return IntRate * 1200.00;
}
 

double MKCalcPeriods(double Payment, double IntRate, double NPV, double FV, 
                     BOOL bStart)
{
    IntRate /= 1200.00;
    NPV *= (-1);
    return log((Payment + Payment*IntRate*bStart - FV*IntRate) / 
               (NPV*IntRate + Payment + Payment*IntRate*bStart) ) / 
           log (1 + IntRate);
}

 
double MKCalcResidual(double Payment, int NumPay, double IntRate, double NPV, 
                      BOOL bStart)
{
    IntRate /= 1200.00;
    return -Payment * ((1 + IntRate * bStart)*((pow((1 + IntRate),NumPay) - 1) 
           / IntRate)) + (NPV * pow(1+IntRate,NumPay)); 

}

All of the above functions are arithmetic juggling of the original formula, with the exception of the one named MKCalcInterest which does a bit of “Guessing” as to what the Interest rate is for a set of given arguments. If you read the Excel on line help you will see that there is no direct way of finding the answer otherwise.

So now – lets look at some examples

  1. I want a loan of $25,000 for a 5-year period with monthly repayments and an annual compound interest rate of 10% and ZERO residual at the end. Calling the MKCalcPayment function results in $531.18 per month.
  2. If on the other end, I can afford to pay only $400.00 and on the same amount I wish to borrow (at the same interest rate) how long will it take me to repay this? Calling the MKCalcPeriods shows that we now have to pay 88.65 payments instead of 60…
  3. Well now – what if I want to stay with the 60 instalments, but I can only afford that same $400.00 a month – there will obviously be some left over (residual) – calling the MKCalcResidual returns this to be equal to $10,157.89. Well it seems you cant have it all – you either pay more each month, or you pay for a longer period, or you are left out with something to pay at the end…
  4. Finally, what if I want to find out – what will be the actual effective / compound annual interest rate on that same amount – if I borrow it for a 60 month period, pay $550.00 per month and call the MKCalcInterest function – the answer is 11.52% per annum… This means that you should be paying less than 60 installments – otherwise you are paying too much. See why by looking at the 1st example.

Well, I am just about done, you can play with more examples – paying ahead or in arrears, with or without a residual etc..

Have fun

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Alex Evans
Web Developer
Australia Australia
Born in Romania in 1945, lived for some 30 years in Israel and moved to join the family in Australia in 1985.
 
Started programming in 1973 (yes, the stone age…) on WANG, with BASIC, Assembler, then some Fortran around 1979, in 1981 got my first home “Personal Computer” – A Dragon 32 (had it connected to a TV monitor and a cassette deck for data / program storage)..
 
Later on PDP-11 , Data General AOS/VS for some 8 years, IBM 32/34/36 with RPG II and III, real “Fun” language to work with (NOT).
 
In 1985 moved to Australia, started a love affair with dBase III, then Clipper 87 and finally in 1991 switched 100% to work in C and later in C++
 
In my “Spare time” I build and fly Radio Control Model Airplanes.

Comments and Discussions

 
QuestionIRR Pinmemberkiddo35413-Feb-12 0:49 
GeneralVery Helpful.. PinmemberDotNetDominator24-Apr-09 8:42 
GeneralMy vote of 1 Pinmemberthomastn29-Dec-08 22:26 
General1200.00 PinmemberTomislaW1-Feb-07 3:53 
AnswerRe: 1200.00 PinmemberDouglas R. Keesler1-Jul-07 17:51 
GeneralExcellent !! PinmemberColinDavies21-Jul-04 18:54 
Generalformula for calculating monthly installment PinsussKamrun10-Dec-03 21:51 
QuestionAmortization? PinmemberRick Crone18-Mar-03 5:31 
AnswerRe: Amortization? PinmemberAlex Evans18-Mar-03 10:07 
GeneralRe: Amortization? PinmemberRick Crone18-Mar-03 11:54 
GeneralRe: Amortization? PinmemberAlex Evans18-Mar-03 12:27 
GeneralRe: Amortization? PinmemberRick Crone19-Mar-03 5:19 
GeneralRe: Amortization? PinmemberRick Crone19-Mar-03 5:42 
GeneralNPV Pinmemberbruno leclerc11-Mar-03 23:47 
GeneralRe: NPV PinmemberAlex Evans12-Mar-03 16:40 
General:) PinmemberDanYELL11-Mar-03 17:01 
GeneralRe: :) PinmemberAlex Evans12-Mar-03 16:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 11 Mar 2003
Article Copyright 2003 by Alex Evans
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid