Mastering the PMT Function for Mortgage Calculations in Excel

Understanding the PMT Function

The PMT function in Excel is designed to calculate the payment for a loan based on constant payments and a constant interest rate. Here's a breakdown of the PMT function's arguments:

• Rate: The interest rate for the loan. For monthly payments, divide the annual rate by 12.
• Nper: The total number of payments for the loan. Multiply the number of years by 12 for monthly payments.
• PV (Present Value): The principal amount of the loan. This should be input as a negative number to represent an outgoing payment.
• FV (Future Value): The remaining balance you want to attain after the last payment. For most loans, this will be 0, which is the default value, so this argument can often be omitted.
• Type: Indicates when payments are due. Use 0 for payments at the end of the period and 1 for payments at the beginning.

A Practical Example: Calculating Monthly Mortgage Payments

Let's consider John, who has taken out a £250,000 mortgage with a 25-year term and an annual interest rate of 4.5%. He plans to make payments at the beginning of each month. Here's how you would set up the PMT function for John's mortgage:

• Rate: 4.5% annual interest rate divided by 12 (monthly payments)
• Nper: 25 years multiplied by 12 (monthly payments)
• PV: -£250,000 (mortgage amount as a negative value)
• Type: 1 (payments at the beginning of the month)

Step-by-Step Guide to Using the PMT Function in Excel

To practice calculating John's monthly mortgage repayment in Excel, follow these steps:

1. Open a new Excel spreadsheet and enter the following data starting in cell A1:

| Rate       | 4.5%   |
| Term       | 25     |
| Mortgage   | 250000 |
| Repayment  |        |

1. Click into cell B4, where the monthly repayment will be calculated.

2. Click on the 'fx' button on the Excel formula bar to open the 'Insert Function' dialog box. Type "PMT" in the search box, click 'Go', select PMT from the results list, and then click 'OK'.

3. In the function arguments, enter the following:

   • Rate: B1/12
   • Nper: B2*12
   • Pv: -B3
   • Leave the FV box empty
   • Type: 1

4. Click 'OK'. The monthly repayment amount should appear in cell B4. For John's mortgage, the answer should be £1,384.39.

Additional Insights and Statistics

While the PMT function is straightforward, there are some interesting nuances and statistics related to mortgage payments that are often overlooked:

• According to the Bank of England, as of Q3 2022, the average interest rate on new residential mortgages in the UK was 1.83% (Bank of England).
• The average mortgage term has been increasing, with many borrowers opting for 30-year terms to lower their monthly payments (UK Finance).
• Overpayment is an option that can significantly reduce the total interest paid over the life of the mortgage, yet it's estimated that only a small percentage of borrowers take advantage of this (MoneySavingExpert).

By understanding and utilizing the PMT function in Excel, you can gain better control over your mortgage finances and make informed decisions about your loan repayment strategy.