Skip to content

Home Mortgage Payment Calculator Using an Excel Spreadsheet

    If you are about to buy your next new home or are thinking of remortgaging Excel provides a great way to work out what your monthly repayments will be. Using the PMT function we can work out what your repayments will be – all you need to do is enter your mortgage amount, term of  loan and  borrowing interest rate – Excel does the rest for you.

    Download Worked Example

    Start by entering the data as shown below.  You can enter you own mortgage amount, loan term and interest rate.  Note that the loan amount has been entered as a negative figure. It will work if you don’t enter a negative but the resulting repayment figure will be negative if you don’t. The term or length of the loan has been entered in months to get a monthly repayment figure: 300 months is 25 years.

    Excel Mortgage Calculations using the PMT function

    In cell B7 type =PMT and then use the shortcut keyboard combination CTRL A to open the Functions Arguments dialog.

    In the Rate argument type B5/12 (you are dividing the annual interest rate by 12 to the month equivalent)

    The Nper argument needs to know the number of payments – the answer to that is in B4

    Fv (future value) is omitted and can stay that way if you plan to pay the mortgage off by the end of the loan term

    Type is omitted as we are making our repayments at the end of each month.  If you plan to make payments at the beginning of the month enter 1 in this argument. This makes very little difference to the formula result.

    Click OK to confirm.

    The PMT Function

    Flexible Mortgage Calculator

    If you have a flexible mortgage you can make huge savings by making regular overpayments or making lump sum overpayments. Our flexible mortgage calculator allows you to calculate the savings you can make. Download it now for free – it’s an Excel spreadsheet.
    Flexible Mortgage Calculator

    Watch the video below to see how to use the Excel PMT function…

    Leave a Reply