When you’re looking to purchase a property, being aware of the repayments of a mortgage will impact the amount of the purchase and whether the monthly costs are within budget. You will also be comparing various mortgages and seeking out the best deal but sadly, mortgage providers rarely provide the details consistently with different variables such as duration, interest, and fixed rate periods all changing the monthly installments.

There’s a significant importance behind ensuring you can meet the repayments because when you undertake a mortgage, the bank will purchase the property and loan it to you. You will then be paying back the bank plus interest of the homes value.

However, if you fail to make the monthly payments, the bank are entitled to seize the property back, so it’s essential to check you can make the repayments, hence why it was previously commonplace to have payment protection insurance for those repayments. Here’s 3 simple ways to calculate your mortgage repayments:

Using a spreadsheet

This method will utilise the payment function (PMT) for most spreadsheet softwares which then follow details specifically for Microsoft excel.

  • Initially enter the function =PMT( in a cell.
  • Once entered you will be shown =PMT(rate,nper,pv,[fv],[type])

rate (decimal) = Monthly interest rates =

nper = Number of periods/payments.

pv = Present value of the loan so the principal.

fv & type = These are both optional variables so you can leave them blank.

  • Create relating cells which contain the variables for the equation and link each part of the equation to the corresponding value.
  • Once entered, you will be shown your monthly repayment value. This value will be a negative as it is defined as an outgoing payment.

You can manipulate the variables between various mortgage providers to ensure that you’re getting the best deal. It’s important to understand that this value is representative of the interest remaining consistent throughout the entirety of the loan.

Using an equation

M = Monthly repayments

P = Principal value of the loan

r = Monthly interest rate

n = Number of payments to be made over the duration of the loan

Using an Amortisation schedule

Amortisation schedules provide a detailed breakdown of monthly repayments which can be easily comparable between mortgages. This type of schedule will highlight the periodic loan payments and the repayments contribution towards interest and the principal load. Whilst each payment is consistent between months, the majority of the initial payments will be paying interest and later within the schedule will be towards the principal.

Follow the instructions below including the specific cells 

1 Annual Interest Rate Value#
2 Loan Duration Value#
3 Payment Per Year Value#
4 Loan Principal Value#
6 Payment Number Payment Amount Principal Payment Interest Payment Loan Balance
7 1 =pmt(B1/B3,B2*B3,B4) =ppmt(B1/B3,A7,B2*B3,B4) =ipmt(B1/B3,A7,B2*B3,B4) =B4+C7


Once compiled, you can highlight A7-E7 and drag down to expand the equation to provide a breakdown of each repayment over the total number of repayments for a thorough example of how the monthly installments will work towards full repayment.

For example: For a £250,000 mortgage which would be fixed 5% interest for 30 years would be:

Month 1 2 3
Monthly Payment £1,208.00 £1,208.00 £1,208.00
Principal £270.00 £271.00 £273.00
Interest £938.00 £936.00 £935.00
Total Interest £938.00 £1,874.00 £2,809.00
Loan Balance £224,730.00 £224,458.00 £224,186.00