Re: Calculate the repayments

Code:

`qtr payment interest balance`

0 60000.00

1 -5415.50 750.00 55334.50 : 60000*.05/4 = 750.00

2 -5415.50 691.68125 50610.68125 : 55334.50*.05/4 = 691.68125

ok?

Re: Calculate the repayments

Quote:

Originally Posted by

**Wilmer** Code:

`qtr payment interest balance`

0 60000.00

1 -5415.50 750.00 55334.50 : 60000*.05/4 = 750.00

2 -5415.50 691.68125 50610.68125 : 55334.50*.05/4 = 691.68125

ok?

Thx for replying. I really appreciate it. But can i ask you how u found out 55334.50 in balance? and what about the payment , how did they got 5415.50? and my last question is how do i find the abatement?

Re: Calculate the repayments

Quote:

Originally Posted by

**zanjeeeer** Thx for replying. I really appreciate it. But can i ask you how u found out 55334.50 in balance? and what about the payment , how did they got 5415.50? and my last question is how do i find the abatement?

These are all basics...which your teacher SHOULD have explained.

Can't give a classroom session here...but go here:

Amortization Calculation Formula and Payment Calculator

Hope that helps...

Re: Calculate the repayments

Quote:

Originally Posted by

**zanjeeeer** Thx for replying. I really appreciate it. But can i ask you how u found out 55334.50 in balance? and what about the payment , how did they got 5415.50? and my last question is how do i find the abatement?

Payment calculations with Excel PMT function Calculator

RATE = 5/4

NPER = 3*4

PV = 60000

FV = 0

TYPE = 0

PMT = £-5415.5

Interest Paid at the end of Quarter 1 using Excel CUMIPMT function Calculator

RATE = 5/4

NPER = 3*4

PV = 60000

Start_Period = 1

End_Period = 1

Interest Paid at the end of Quarter 1 = £-750

Principal Paid at the end of Quarter 1 = £-5415.5 - £-750

Principal Paid at the end of Quarter 1 = £-5415.5 + £750

Principal Paid at the end of Quarter 1 = £-4665.5

Interest Paid at the end of Quarter 2 using Excel CUMIPMT function Calculator

RATE = 5/4

NPER = 3*4

PV = 60000

Start_Period = 2

End_Period = 2

Interest Paid at the end of Quarter 1 = £-691.68

Principal Paid at the end of Quarter 1 = £-5415.5 - £-691.68

Principal Paid at the end of Quarter 1 = £-5415.5 + £691.68

Principal Paid at the end of Quarter 1 = £-4723.82

Cumulative interest paid from Quarter 1 to Quarter 12

RATE = 5/4

NPER = 3*4

PV = 60000

Start_Period = 1

End_Period = 12

Interest Paid at the end of Quarter 1-12 = £-4985.98

Principal Paid at the end of Quarter 1-12 = £-5415.5x12 - £-4985.98

Principal Paid at the end of Quarter 1-12 = £-64986 + £4985.98

Principal Paid at the end of Quarter 1-12 = £-60000

Re: Calculate the repayments

Dexter, Excel is just a "tool" to obtain results.

I don't think showing what different "tools" need as input will help anybody learn anything.

I feel a student should learn the "formula(s)" providing the output, in this case

the "loan payment" formula: easily obtainable if one uses a google search...

Just my opinion.

Re: Calculate the repayments

Quote:

Originally Posted by

**Wilmer** Dexter, Excel is just a "tool" to obtain results.

I don't think showing what different "tools" need as input will help anybody learn anything.

I feel a student should learn the "formula(s)" providing the output, in this case

the "loan payment" formula: easily obtainable if one uses a google search...

Just my opinion.

(Happy)

Well it was just a silly promotion of my online Excel calculators that I am developing these days. You are correct just as you say, for me to develop these tools I had to research and extend my knowledge to use the underlying formulas. Sometimes you will find the formula on the internet other times you will get hints from places and you will have to come up with your own version of the formula

As was the case with Excel CUMIPMT function calculator, I am sure there is some mathematical formula that will result in finding cumulative interest between two periods yet I didn't find one so i did a piecemeal job with four equations that resulted in finding cumulative interest between two periods

Here is what I did

1 Found the monthly payment on loan

2 Found the Outstanding balance at the end period

3 Found the Outstanding balance at the start period minus 1

From these the difference gave me cumulative principal paid between two periods

Then found the total monthly payments between the periods

And the difference from this and the cumulative principal gave me the cumulative interest between two periods

But then I had to tweak the formula for payments that were due at the beginning of month and payments that were due at the end of month and the special case when start period is 1

Thus far I have finished 6 Excel financial functions and there are a good number remaining

Interesting part is if I were like the OP, I could have simply gone over to an open source site that has the source code for all Excel functions in Php. But I rather learn and earn my way in life (which for some reason I have been deprived off) instead of living off stolen goods ( which people often do where I live)

Re: Calculate the repayments

Good stuff Dexter! You're to be admired...

Let's play with this one some more:

we have A=60000, P=5415.50, i=.05/4:

how much interest fas been paid over first 6 quarters (n=6)?

F = balance owing iafter 6 payments:

F = A(1+i)^n - P[(1+i)^n - 1] / i : that'll be ~31117.50

INTEREST = F - (A - P*n) : that'll be ~3610.50

In other words, "balance owing including interest" - "balance owing IF no interest".

But you knew that, right?

Re: Calculate the repayments

Quote:

Originally Posted by

**Wilmer** Good stuff Dexter! You're to be admired...

Let's play with this one some more:

we have A=60000, P=5415.50, i=.05/4:

how much interest fas been paid over first 6 quarters (n=6)?

F = balance owing iafter 6 payments:

F = A(1+i)^n - P[(1+i)^n - 1] / i : that'll be ~31117.50

INTEREST = F - (A - P*n) : that'll be ~3610.50

In other words, "balance owing including interest" - "balance owing IF no interest".

But you knew that, right?

For F I tried the same formula as you have but I was having difficultly using it when payments were due at the start of the month

So I resorted to a different formula for F as follows

F = A [ (1+i)^n - (1+i)^k ] / [(1+i)^n - 1]

This gave me the outstanding principal after k periods

No to be honest, I did not know of the second formula you have used that got the cumulative interest

Is it possible to make the formula for F work with start of period payments, if so then your formula is much simpler to use to find cumulative interest between to periods with just two calculations

Wait you selected a range of period Q1 through Q6, what if you were to find cumulative interest between Q4 through Q8. Will your formula still work in such a case

Re: Calculate the repayments

This is how I did it

start_period = start_period - 1

OutstandingPrincipal_End = A * ( (1+i)^n - (1+i)^end_period-type ) /( (1+i)^n-1)

OutstandingPrincipal_Start = A * ( (1+i)^n) - (1+i)^start_period-type) ) /( (1+i)^n-1)

TotalPrincipalPaid = OutstandingPrincipal_End - OutstandingPrincipal_Start

TotalPayment = P * (end_period - start_period)

TotalInterest = TotalPayment - TotalPrincipalPaid

But for the case when start_period is 1 there is a different set of the same formula

Re: Calculate the repayments

Quote:

Originally Posted by

**dexteronline** Wait you selected a range of period Q1 through Q6, what if you were to find cumulative interest between Q4 through Q8. Will your formula still work in such a case

Get balance owing at end of Q4 (n=4).

Now, treat this as a new amount A; ok?

In other words:

calculate F

A = F

calculate F, then INTEREST (as in my previous post)

Re: Calculate the repayments

Thxxxxx alot guys,but still dont get it :/. I am not allowed to use online excels . The only thing i can use is my simple calculator.

Re: Calculate the repayments

Quote:

Originally Posted by

**Wilmer** Get balance owing at end of Q4 (n=4).

Now, treat this as a new amount A; ok?

In other words:

calculate F

A = F

calculate F, then INTEREST (as in my previous post)

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.

Thanks

Re: Calculate the repayments

To Zanjeeeer:

your posted problem is a bit hard to follow; you are using strange words like annuity for loan, abatement .....

Could you repost it CLEARLY, state what you mean by "calculator", and use COMMON loan terminology.

To Dexter:

we're hijacking Zan's thread!

I'll start a new thread named "Loan Interest Accumulation".

I'll answer your last post there...

To Mr Fantastic: hope that's ok (Speechless)