How do I calcualte capital repayments against the interest?

emmalt

Hi,

I am trying to pay off my mortgage asap and want to set up a spreadsheet to show how much we owe. I know that at the beginning of the mortgage the interest part of the repayment is larger than the capital bit but I am looking for a rough estimate of the split each month. How can I calculate this?

Thanks

zweety

ok, this is how I’d do it:

- Take the mortgage amount.
- Multiply it by the interest rate
- Multiply this amount by the number of year you've had it.
That's your interest payment.
However, don't understand how they calculate the monthly repayment as it is always the same amount every months…

Example:
Mortgage amount: 200,000
Interest rate: 5.5 %
You have had this mortgage for 2 years and 150 days

200,000 X 0.055 = 11,000 (that’s what you pay per year)
11,000 / 365 (days) = 30,1370 interest per day
30,1370 * 150 (days) = 452.05 (=interest for 150 days)

11,000 + 452.05 = 11452.05 (=interest you’ve paid since you’ve had your mortgage)

The rest you’ve paid per month should be you mortgage repayment (without interest). But please note that I am not sure about my calculation, as I don’t know how they calculate it to have a constant amount.

emmalt

:T

Hi Zweety,

Thanks for that. I have done something similar in a spreadsheet and I have it more or less correct> It will be fun to see the months shaved off and we overpay each month

kennyboy66_2

if you have microsoft excel, they have a template (file, new, open from template) called Loan Amortization. This does everything for you and has the facility to put extra payments in.

I think Lotus have a similar template.

zweety

I'm happy to help.
But the problem, is that everytime you pay your monthly repayment, you pay interest AND mortgage repayment. And the 2nd one reduces your mortgage sum, which reduces you interest...

Is there any mortgage adviser out there to tell us how banks do to charge the same amount everymonths ?

zweety

Wow, just tried the Excel template. That's GREAT !!!

You can forget my stupid calculation !!!!

massa247

Cheers Kennyboy..

Just tried that Microsoft Excel you spoke of..(didn't even know i had it!)
Great stuff..gives you an exact amount over the years on intrest against what your actually paying towards the bricks & mortar (capital)..& breaks in down to a monthly figure too!

Try this Folks!