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)
