Repayment mortgage - interest loaded early on?

Charlton_King

Yes please. Me too.

real1314

You can do it in Excel, using the PMT function I think.

Other than that, you can do it with pen and paper, but it would take a list of 300 lines of payments.

It works like Clapton showed below. Get a pen and some paper and go though it month by month if you like?

I doubt it would format very well on here.

real1314

Or in Excel (or Open Office Calc):-

Assuming you start at Cell A1:-

Put the value 0.05 in cell H2 (this is the interest rate)

5 Columns:-

Month, Capital o/s, Interest, Payment, Capital Paid Off


In Row 2 put: 1 , £100,000 , =B2*$H$2/12 , £585.50 , =D2-C2

In Row 3 put: 2 , =B2-E2 , =B3*$H$2/12 , =D2 , =D3-C3


Then autofill from row 3 down to row 301. It should give you the right outcome.

I tried to paste it into here but it just went mad.

TrickyDicky101

Monthly mortgage repayment =

Amount Owed * (Monthly Rate * (1+Monthly Rate)^Number of months) / (((1+Monthly rate)^Number of months)-1)

where ^ means "to the power of"

Taking the above:

£100k * (5%/12 * (1+5%/12) ^(25*12))/(((1+5%/12)^(25*12))-1)

=584.59 per month

Asssumptions: repayments made in arrears (most mortgages quoted on repayment in advance basis - makes minimal difference, however)

jamesperrett

Remember that a mortgage term doesn't have to be 25 years. You could always go for a shorter term if you want to pay off the capital quicker.

James.