*How to calculate a mortgage repayment in Excel using PMT.*

Introduction.

Online mortgage and loan calculators are all well and good, but sometimes, you may want to build a loan repayment figure directly into your own Excel spreadsheet, without having to manually update the loan or repayment figures using an online calculator each time the loan variables change.

If you are using Excel to make a budget for your home or business to calculate the costs of moving to a new home or premises, mastering Excel's PMT (Payment) function will not only save you time but also keep your spreadsheets up to date, making you more efficient and productive.

Excel's PMT function is a simple yet powerful tool that allows you to quickly compute the regular payment amount needed to pay off a loan over a specified period at a fixed interest rate. In this article, we'll guide you through using this function, empowering you to confidently calculate your loan repayments.

How to calculate a mortgage repayment in Excel using PMT.

Understanding Excel's PMT function.

The PMT function in Excel stands for "payment", and it is used to calculate the periodic payment for a loan based on constant payments and a constant interest rate. The syntax for the PMT function is:

PMT(rate, nper, pv, [fv], [type])

Where the following rules apply:

Rate: The interest rate for each period.
Nper: The total number of payment periods.
Pv: The present value, or the total amount of the loan.
Fv: The future value, or a cash balance you want to attain after the last payment (optional, default is 0).
Type: When payments are due. 0 means the end of the period, and 1 means the beginning (optional, default is 0).

A Step-by-step guide to calculating mortgage repayments using PMT in Excel.

Step 1: Input the loan details.

Start by entering the loan amount, annual interest rate, and loan term into separate cells in Excel. For example:

Cell A1: Loan Amount (e.g., £100,000)
Cell A2: Annual Interest Rate (e.g., 5%)
Cell A3: Loan Term in Years (e.g., 20)

Step 2: Calculate the monthly interest rate.

Since the PMT function requires the interest rate per period, you must convert the annual interest rate to a monthly rate. In cell B1, enter the formula:

=A2/12

Step 3: Calculate the total number of payments.

Next, calculate the total number of monthly payments over the loan term. In cell B2, enter the formula:

=A3*12

Step 4: Use the PMT function.

Now, you can use the PMT function to calculate the monthly repayment amount. In cell B3, enter the formula:

=PMT(B1, B2, -A1)

Example.

Let's use the following example to illustrate:

Loan Amount: £100,000
Annual Interest Rate: 5%
Loan Term: 20 years

Here's what you enter into the cells:

A1: 100000
A2: 0.05
A3: 20

*How to calculate a loan repayment in Excel using PMT function.*

Then, enter the following formulas:

B1: =A2/12, which gives 0.004167 (monthly interest rate)
B2: =A3*12, which gives 240 (total number of payments)
B3: =PMT(B1, B2, -A1), which gives the monthly repayment amount.

The result in cell B3 will be approximately £659.96, which is the monthly repayment for the loan under the given conditions.

How to calculate a mortgage repayment in Excel using PMT function formula — *How to calculate a mortgage repayment in Excel using the PMT function calculations.*

Single-cell calculation with the PMT function in Excel.

If your interest rate and loan term are always constant (for example, if you know the mortgage will always be over 20 years and with a 5% interest rate), you can simplify the process by using the PMT function in a single cell. This method eliminates the need for intermediate calculations.

Assuming the loan amount is in cell A1. You can directly use the following formula in a single cell to calculate the monthly repayment:

=PMT(5%/12, 20*12, -A1)

Here's how it works:

5%/12: Converts the annual interest rate to a monthly rate.
20*12: Calculates the total number of monthly payments over 20 years.
-A1: Uses the loan amount from cell A1 (with a negative sign to indicate an outgoing payment).

For example, if cell A1 contains £100,000, entering the formula =PMT(5%/12, 20*12, -A1) directly in another cell will provide the monthly repayment amount, which is approximately £659.96.

Conclusion.

Using the PMT function in Excel simplifies the process of calculating loan repayments. By breaking down the steps and understanding each part of the function, you can quickly determine how much you need to pay monthly to service your mortgage or loan. For added convenience, you can even perform the calculation in a single cell when the interest rate and term are constant. This can be particularly useful for budgeting and financial planning. Excel's PMT function is a versatile tool that can help you manage your finances and spreadsheets more effectively.

What’s next?

If you need assistance with your personal finances, our Financial Advisers are here to help. You can get in touch with one of our advisers for independent financial advice and we offer a free initial consultation. Based in Royal Tunbridge Wells, we advise clients across the UK.

ARRANGE A FREE CONSULTATION

Don’t forget, this article offers general financial information and should not be taken as personal advice. Remember that investments and pensions can go up and down in value, so you could get back less than you put in. Tax rules can change and will depend on your individual circumstances.

How to calculate a mortgage repayment in Excel using PMT.