Results 1 to 4 of 4

Math Help - Loan Repayment

  1. #1
    Newbie
    Joined
    Aug 2008
    Posts
    7

    Loan Repayment

    Hi, This is a query relating to the calculation method for the repayments of loan.
    Example:- Using the PMT Function on a Loan of 7500, Over 4 years Monthly repayments @ 8.7%. The Function instructions uses the Yearly interest rate divides by 12.
    So basing the calculation on "0.087/12" you get :-
    In this case the answer :-=PMT(0.087/12,48,7500) Ans =185.57
    If you now look at the actual Repayments on this loan from any Reliable Loan Company, you will see the actual repayment is 184.41 This is calculated as follow:- Using the Twelth root of the (interest rate+1 )-1
    i.e.1.087^(1/12)
    =PMT(1.087^(1/12)-1,48,7500)= 184.41
    As you are Compounding the Interest, the repayments from the second method seems the more logical, so WHY does the PMT Function return the value it does, by calling for the interest rate to be divided by 12.
    Any enlightenment , much appreciated.
    NB:- I have check these result over several Loan Companies and various loans amounts /interest rates and get the same Basic result.
    Regards Mick
    Follow Math Help Forum on Facebook and Google+

  2. #2
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,110
    Thanks
    68
    185.57 is the correct payment IF the rate is quoted as 8.7% annual
    compounded monthly.

    184.41 is the correct payment IF the rate is quoted as 8.7% EFFECTIVE
    annual compounded monthly.

    Rate needs to be converted:
    (1 + r)^12 = 1.087 : after compounding, result is 8.7%
    r = .006976.... (monthly)

    .006976 * 12 = .0837122.... (approx. 8.37%)
    In other words, an annual rate of 8.37 if compounded monthly
    will result in an EFFECTIVE rate of 8.7%

    Hope that helps.
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    Aug 2008
    Posts
    7
    Hi, Thank you for your reply.
    I understand your equations, but not so sure on the logical purpose of them.
    This is an example of my query.:-
    If you invest 100 for 2 years @ 10% the equation is 100 x 1.1^2 = 121
    i.e Monthy compounded = 100 x 1.1^(1/12)^24 = 100x1.007974^24 = 121. i.e. The rate used is the 12th root of 1.1 = 1.1^(1/12) =1.07974

    If you do this monthy compounding the "PMT" way you get :-
    100 x[(1.1-1)/12+1]^24 = 122.039 i.e. The compounding rate used is the interest rate 0.1 divided by 12 +1= 1.0083333.
    This means in the first way ( to my mind) you get the correct answer using a rate of 10% and in the second , using the PMT way you get an interest rate of = [(0.1/12)+1]^12 =10.4713.
    Which does not appear correct.
    The real question is, if Loan companies use the first method (returns a logical and fair answer) Why does "Excel" with its PMT Function use a methods that apparently returns an illogical Ans.
    Regards Mick
    Follow Math Help Forum on Facebook and Google+

  4. #4
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,110
    Thanks
    68
    To start, on your original example, 184.41 is the CORRECT payment IF the rate used is 8.37%, NOT 8.7%; already told you that, and explained as
    well as I could (not in a classroom!).

    We'll now take your 100 at 10% example; make it one year:

    100 * 1.10 = 110.00 : EVIDENTly 10% annual
    If paid monthly:
    100 * (1 + .10/12)^12 = 110.47 : 10.47% annual

    The object is to pay monthly and end up with 10% annual;
    so of course a lesser rate must be used:
    (1 + r)^12 = 1.10 ; solve for r ; r = .007974 monthly :
    .007974 * 12 = .09568, 0r 9.57%

    In other words:
    paying 10% only once per year results the SAME as paying 9.57% 12 times per year;
    BOTH methods will have 100 end up at 110.
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Loan repayment
    Posted in the Business Math Forum
    Replies: 1
    Last Post: October 7th 2010, 06:29 AM
  2. Loan repayment
    Posted in the Business Math Forum
    Replies: 5
    Last Post: January 11th 2010, 04:16 AM
  3. Superannuation and Loan Repayment Questions Part Two
    Posted in the Business Math Forum
    Replies: 1
    Last Post: January 9th 2010, 06:52 AM
  4. Loan repayment questions last part
    Posted in the Business Math Forum
    Replies: 0
    Last Post: January 9th 2010, 03:28 AM
  5. min repayment
    Posted in the Business Math Forum
    Replies: 5
    Last Post: September 1st 2009, 11:37 AM

Search Tags


/mathhelpforum @mathhelpforum