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 (Rock)

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

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

Re: Loan Interest Accumulation

Quote:

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

Re: Loan Interest Accumulation

Thanks Dexter (1st mistake this year!); corrected by edit.

Will get back to you later, on your last post...

Re: Loan Interest Accumulation

Quote:

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.

Re: Loan Interest Accumulation

Quote:

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 (Evilgrin)

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

Re: Loan Interest Accumulation

Quote:

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

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?

Re: Loan Interest Accumulation

Quote:

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

Re: Loan Interest Accumulation

Quote:

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

Re: Loan Interest Accumulation

Quote:

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 (Wait)