Calculate monthly repayment

w00519772

Say I have a mortgage of £100,000 over 25 years at an interest rate of 3.25%. How can I calculate what the monthly payment would be?

I realise there are online tools to do this calculation for you. However, I want to calculate it myself on a month by month basis after taking into account overpayments. I am looking for a mathematical formula.

getmore4less

the term you are looking for is amortization.

built in functions in spreadsheets.


locoblade has a spreadsheet that will probably do the job or be a good basis for your own

tacpot12

If you have access to Microsoft Excel, it comes with a loan amortization template that works for mortgages and includes the ability to enter overpayment. If you unprotect the sheet you can see all the formulas.

SalsaDanca

In Excel, the function is PMT. It needs three values: the interest rate (divided by twelve to turn it into from annual to monthly), the number of months, and the balance.

eg.
=PMT(0.0325/12, 25*12, 100000)

The result is -£487.32