Thread: 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...

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

Originally Posted by Wilmer

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

Thanks Dexter (1st mistake this year!); corrected by edit.
Will get back to you later, on your last post...

Originally Posted by dexteronline

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.

Originally Posted by Wilmer

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

Originally Posted by dexteronline

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".

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?

Originally Posted by Wilmer

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

Originally Posted by dexteronline

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....

Originally Posted by dexteronline

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