# Calculate the repayments

• Aug 21st 2011, 02:35 AM
zanjeeeer
Calculate the repayments
Hello everyone

I am 24 years old and attending college and need some help. Here is the question:

You take up a short-term annuity of £ 60 000 The loan is repayable over three years in equal installments at the end of each quarter. Borrowing rate is 5% per year. We ignore other borrowing costs. What is the forward amount, and what is paid, respectively, as interest and abatement in the 2 quarter of the first year?

Forward Amount ( repayment) -Interest- abatement
1 quarter of Year 1: 5 415,50 - 750- 4 665.50
2 quarter of year 1: 5 415,50 - 691.68 4 723.82

have also tried this formula but I get the wrong answer:
60000x (1 + 0.05) ^ 4 x 0.05 / (1 + 0.05) ^ 3 to 1
1.2155x 0.05 / 1.2155-1
0.06077/0.2155 = 0.28199 x 60,000 = 16,919

• Aug 21st 2011, 10:26 PM
Wilmer
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?
• Aug 22nd 2011, 01:46 AM
zanjeeeer
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?
• Aug 22nd 2011, 06:54 AM
Wilmer
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...
• Aug 22nd 2011, 07:46 AM
dexteronline
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
• Aug 22nd 2011, 09:06 AM
Wilmer
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.
• Aug 22nd 2011, 09:40 AM
dexteronline
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)
• Aug 22nd 2011, 11:33 AM
Wilmer
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?
• Aug 22nd 2011, 12:13 PM
dexteronline
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
• Aug 22nd 2011, 12:23 PM
dexteronline
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
• Aug 22nd 2011, 05:53 PM
Wilmer
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)
• Aug 22nd 2011, 11:04 PM
zanjeeeer
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.
• Aug 23rd 2011, 01:43 AM
dexteronline
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
• Aug 23rd 2011, 07:42 AM
Wilmer
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: