Excel Mortgage Payment Formula

In Excel it is possible to calculate monthly repayments on a mortgage by using the PMT function.

1. www.bluepecan.co.uk
Excel Mortgage Payment
Formula
Microsoft Office Training
Excel Training

2. www.bluepecan.co.uk
The task at hand…
In Excel it is possible to calculate monthly
repayments on a mortgage by using the PMT
function.

3. www.bluepecan.co.uk
The PMT function has the
following arguments:
Rate – this is interest rate on
the mortgage loan divided by 12

4. www.bluepecan.co.uk
The PMT function has the
following arguments:
Nper – this is the term of the
mortgage or the number of monthly
repayments you will make. For
example with a 25 year mortgage you
would make 12 multipled by 25
monthly repayments.

5. www.bluepecan.co.uk
The PMT function has the
following arguments:
PV (present value) - is the
mortgage amount – the amount
you have borrowed, expressed
as a negative value.

6. www.bluepecan.co.uk
The PMT function has the
following arguments:
FV - you can leave blank. FV stands
for future value. As the future of
the loan when it has be repaid will
be zero and zero is the default for
this argument it can be left empty.

7. www.bluepecan.co.uk
The PMT function has the
following arguments:
Type – here you state whether you
will make the payment at the
beginning or at the end of each
month, type 1 if at the beginning
or 0 if at the end. Sorry no option
for halfway through the month.

8. www.bluepecan.co.uk
Our Example
An example. John takes out a £250,000 mortgage over
25 years with an annual interest rate of 4.5%. He will
make his mortgage payment on the 1st of each month.
Rate would be 4.5%/12
Nper would be 25*12
PV would be -250000
Type would be 1

9. www.bluepecan.co.uk
Our Example
To practice this example in Excel, in a blank
spreadsheet enter the following data starting in A1.
i) Rate goes in A1, 4.5% goes in B1 and so on for each
row.
Rate 4.5%
Term 25
Mortgage 250000
Repayment

10. www.bluepecan.co.uk
Our Example
To practice this example in Excel, in a blank
spreadsheet enter the following data starting in A1.
i) Rate goes in A1, 4.5% goes in B1 and so on for each
row.
Rate 4.5%
Term 25
Mortgage 250000
Repayment

11. www.bluepecan.co.uk
Our Example
ii) Click into cell B4 – this is where
we will calculate the monthly
repayment

12. www.bluepecan.co.uk
Our Example
iii) Now click on the fx button on the Excel formula bar
just above the spreadsheet’s column headers. This will
open the Insert Function dialogue box. In the search
box type PMT and then click Go. Select PMT from the
results list below and then click OK
In the Rate box type B1/12
In the Nper box type B2*12
In the Pv box type –B3
Leave the FV box empty
In the Type box type 1

13. www.bluepecan.co.uk
Our Example
iv) Click OK Your answer should be 1384.39

14. www.bluepecan.co.uk
More Excel Tutorials
For more Excel Tutorials visit the Blue Pecan
Free Microsoft Office Training page.
For a direct link to this Excel Training tutorial