Results 1 to 11 of 11

Math Help - Loan Interest Accumulation

  1. #1
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,080
    Thanks
    66

    Loan Interest Accumulation

    Dexter, you posted:
    I may not be following you correctly on this one,
    I have set up a Google DOCS spreadsheet here Cumulative Interest Paid Formula

    There I have set up two columns , in the first one i have used Excel's built in functions and in the second column I have used your formulas

    There are columns with light yellow background where your formula listed in last reply should be placed. Can you have a look at the spreadsheet and fill those empty cells. To the left of these empty cells you can see what the results should be as they are calculated with Excel formula

    There are cells where it is assumed that payments for loan are made at the start of the month thus cumulative principal paid and cumulative interest paid should reflect these.
    .................................................. .................................................. ............
    I can only see an example there:
    Amount 125000, rate 9%, number of payments 360, interest accumulation 13 to 24.

    I pressed the "calculate(!)" button, got 11135.23 ... which is CORRECT:
    that is the total interest paid during months 13 to 24; GOOD JOB

    OK: now let's once more do this by FORMULA:

    The monthly repayment on that example works out to 1005.77827....

    So we have A = 125000, P = 1005.78, i = .09/12, u = 12, v = 24
    (u and v represent the time period, u being 1 less than 13;
    which means time period = 24 - 12 = 12 months)

    1: calculate F1 (amount due after u months):
    F1 = A(1 + i)^u - P[(1 + i)^u - 1]/i : that'll give you ~124146.00

    2: calculate F2 (amount due after v months):
    F2 = A(1 + i)^v - P[(1 + i)^v - 1]/i : that'll give you ~123211.90

    3: calculate total interest for months 13 to 24 (or u+1 to v):
    INTEREST = P(v - u) + F2 - F1: that'll give you ~11135.23

    Hope that helps...
    Last edited by Wilmer; August 23rd 2011 at 10:13 AM.
    Follow Math Help Forum on Facebook and Google+

  2. #2
    Member
    Joined
    Jan 2009
    From
    Punjab, Pakistan
    Posts
    88

    Re: Loan Interest Accumulation

    Hi Many thanks for creating this thread

    Once again due to my poor English I may not have been able to convey the message correctly

    Now I do understand how your formulas are to be used when calculating cumulative interest between two periods

    In the beginning of my message I left a link to a Google Docs spreadsheet, well here is the link without the anchor tag

    https://docs.google.com/spreadsheet/ccc?key=0AmyXqCqKRFBKdF9zMnc4SXFoT0hMcHczT0JoTmhNV EE&hl=en_US#gid=0

    The question that is left unanswered is how can i use your formula to calculate the cumulative interest paid when the mortgage payment is made at the start of the period

    Taking the problem example Zan posted earlier,

    Loan = $60,000
    Interest Rate = 5%/4
    Terms of the Loan = 3 yrs x 4 = 12

    The loan payment when made at the end of quarter is $5,415.50
    The loan payment when made at the start of quarter is $5,348.60

    Now if we were to find the interest paid in Period 1 or 1st quarter
    With End of quarter payment
    Interest Paid in first quarter is $750
    Principal Paid in first quarter is $4665.50

    With Start of quarter payment ( there is no interest paid in the first period )
    Interest Paid in first quarter is $0
    Principal Paid in first quarter is $5348.64

    So if you can have a look at the spreadsheet I have setup for this, and let me know how to use your formulas to find cumulative interest paid when loan payments are made in the beginning of the period instead of end of the period
    Last edited by dexteronline; August 23rd 2011 at 09:10 AM. Reason: fixed the reference to quarters that was listed as months
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Member
    Joined
    Jan 2009
    From
    Punjab, Pakistan
    Posts
    88

    Re: Loan Interest Accumulation

    Quote Originally Posted by Wilmer View Post
    1: calculate F1 (amount due after u months):
    F1 = A(1 + i)^u - P[(1 + i)^u]/i : that'll give you ~124146.00

    2: calculate F2 (amount due after v months):
    F2 = A(1 + i)^v - P[(1 + i)^v]/i : that'll give you ~123211.90
    You made a typo in your formula

    You stated it correctly yesterday, it should be

    F1 = A(1 + i)^u - P[{(1 + i)^u}-1]/i

    F2 = A(1 + i)^v - P[{(1 + i)^v}-1]/i
    Follow Math Help Forum on Facebook and Google+

  4. #4
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,080
    Thanks
    66

    Re: Loan Interest Accumulation

    Thanks Dexter (1st mistake this year!); corrected by edit.
    Will get back to you later, on your last post...
    Follow Math Help Forum on Facebook and Google+

  5. #5
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,080
    Thanks
    66

    Re: Loan Interest Accumulation

    Quote Originally Posted by dexteronline View Post
    Taking the problem example Zan posted earlier,
    Loan = $60,000
    Interest Rate = 5%/4
    Terms of the Loan = 3 yrs x 4 = 12

    The loan payment when made at the end of quarter is $5,415.50
    The loan payment when made at the start of quarter is $5,348.60

    Now if we were to find the interest paid in Period 1 or 1st quarter
    With End of quarter payment
    Interest Paid in first quarter is $750
    Principal Paid in first quarter is $4665.50

    With Start of quarter payment ( there is no interest paid in the first period )
    Interest Paid in first quarter is $0
    Principal Paid in first quarter is $5348.64
    Well, "start of quarter payment" means payment is made on SAME day as the
    loan is taken, so it all starts off with 60000.00 - 5348.64 = 54651.36 owing.
    Looks like this:
    Code:
    QTR  PROCEEDS    PAYMENT    INTEREST   BALANCE
    01   60000.00   -5348.64        .00   54651.36
    02              -5348.64     683.14   49985.86
    ...
    11              -5348.64      66.03        .00
    In other words, it's a "ridiculous" arrangement:
    that's really a loan of $54,651.36 over 11 quarters.

    So use the formula I gave you, but start with A = A - P.
    Follow Math Help Forum on Facebook and Google+

  6. #6
    Member
    Joined
    Jan 2009
    From
    Punjab, Pakistan
    Posts
    88

    Re: Loan Interest Accumulation

    Quote Originally Posted by Wilmer View Post
    In other words, it's a "ridiculous" arrangement:
    that's really a loan of $54,651.36 over 11 quarters.
    So use the formula I gave you, but start with A = A - P.
    As I stated in one of my previous discussions with you, that you are a finance genius

    Just a little comments about the first formula you had used to find outstanding principal at period u

    F1 = A(1 + i)^u - P[(1 + i)^u - 1]/i

    It seems this formula produces the same results as the formula that follows

    F1 = A [(1+i)^n - (1+i)^u]/[{(1+i)^n}-1]

    Both of these formula find the outstanding principal at period u
    Follow Math Help Forum on Facebook and Google+

  7. #7
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,080
    Thanks
    66

    Re: Loan Interest Accumulation

    Quote Originally Posted by dexteronline View Post
    F1 = A(1 + i)^u - P[(1 + i)^u - 1]/i

    It seems this formula produces the same results as the formula that follows

    F1 = A [(1+i)^n - (1+i)^u]/[{(1+i)^n}-1]

    Both of these formula find the outstanding principal at period u
    Correct.
    Mine is used (shorter) if P is known.
    The other one calculates P (hard to "see"; that's why "n" is used) plus
    calculates balance owing at "u".
    Follow Math Help Forum on Facebook and Google+

  8. #8
    Member
    Joined
    Jan 2009
    From
    Punjab, Pakistan
    Posts
    88

    Re: Loan Interest Accumulation

    Well now I am confused as to if I use n=11 instead of 12 to find the payment
    or whether to use 0 or 1 for the first period

    A= 60,000
    P=5348.60

    Principal outstanding = (A-P)*(1+i)^1 - P*((1+i)^1-1)/i
    Principal outstanding = $49985.86
    Interest Paid = $4665.50

    Principal outstanding = (A-P)*(1+i)^0 - P*((1+i)^0-1)/i
    Principal outstanding = $54651.36
    Interest Paid = 0

    Edit

    I think I am doing this the wrong way
    =P(v-u) + F2 - F1
    =$5348.60(1-0) + $54,651.36 - $49,985.86
    =$5348.60 +4665.5
    =$10,014.1

    What am I doing wrong Wilmer?
    Last edited by dexteronline; August 23rd 2011 at 11:02 PM.
    Follow Math Help Forum on Facebook and Google+

  9. #9
    Member
    Joined
    Jan 2009
    From
    Punjab, Pakistan
    Posts
    88

    Re: Loan Interest Accumulation

    Quote Originally Posted by Wilmer View Post
    Correct.
    Mine is used (shorter) if P is known.
    The other one calculates P (hard to "see"; that's why "n" is used) plus
    calculates balance owing at "u".
    I just expanded my version of the F1 formula and it turns out that it is the difference between monthly payment and total prinicipal paid at period u

    See the following
    F1 = A[(1+i)^n - (1+i)^u] / [{(1+i)^n}-1]
    F1 = [A(1+i)^n/[{(1+i)^n}-1] - [A(1+i)^u/[{(1+i)^n}-1]]
    ** F1 = [Loan Payment] - [Total Principal Paid after u periods] ** Wrong

    Edit

    I made an error in classifying the two parts of the equation above
    The first one is not a loan payment and the second one is not the total principal paid after u periods
    Last edited by dexteronline; August 24th 2011 at 01:18 AM. Reason: error corrected
    Follow Math Help Forum on Facebook and Google+

  10. #10
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,080
    Thanks
    66

    Re: Loan Interest Accumulation

    Quote Originally Posted by dexteronline View Post
    Well now I am confused as to if I use n=11 instead of 12 to find the payment
    Use n=12 (less confusing) but use (n-1) in formula:

    Let x = 1 / (1 + i)^(n - 1)

    P = i(A - P) / (1 - x) ; simplifies to P = Ai / (1 + i - x)

    A = 60000, n = 12, i = .05/4 : you'll get P = 5348.64073....
    Follow Math Help Forum on Facebook and Google+

  11. #11
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,080
    Thanks
    66

    Re: Loan Interest Accumulation

    Quote Originally Posted by dexteronline View Post
    Edit
    I think I am doing this the wrong way
    =P(v-u) + F2 - F1
    =$5348.60(1-0) + $54,651.36 - $49,985.86
    =$5348.60 +4665.5
    =$10,014.1
    What am I doing wrong Wilmer?
    You've got your F1 and F2 mixed up!
    F2 = 49985.86, F1 = 54651.36

    $5348.60(1-0) + $49,985.86 - $54,651.36 = 683.14

    You need some sleep
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Interest on a loan.
    Posted in the Business Math Forum
    Replies: 2
    Last Post: July 22nd 2010, 07:45 PM
  2. Total Interest on loan.
    Posted in the Algebra Forum
    Replies: 5
    Last Post: October 21st 2009, 06:52 PM
  3. loan interest problem
    Posted in the Algebra Forum
    Replies: 2
    Last Post: December 9th 2008, 11:20 AM
  4. Total Interest on the Loan?
    Posted in the Business Math Forum
    Replies: 1
    Last Post: November 3rd 2008, 07:00 AM
  5. Interest Only Loan
    Posted in the Business Math Forum
    Replies: 5
    Last Post: October 12th 2007, 11:16 AM

Search Tags


/mathhelpforum @mathhelpforum