How to use the PMT function in Excel

The PMT function is used to calculate the total payment required to repay a loan or investment with a fixed interest rate over a specified period. It is particularly useful for determining periodic payments for loans, mortgages, or investments.

The PMT function uses the following syntax for its calculations:

=PMT(rate; nper; pv; [fv]; [type])

• Rate (Required Argument): This is the interest rate for the loan or investment.
• Nper (Required Argument): This represents the total number of payments to be made over the lifetime of the loan or investment.
• Pv (Required Argument): This is the present value, or the total amount that a series of future payments is worth now. It is also referred to as the principal.
• Fv (Optional Argument): This is the future value or the cash balance you aim to achieve after the last payment is made. If omitted, it defaults to 0.
• Type (Optional Argument): This indicates when payments are due. If omitted, it defaults to 0, meaning payments are due at the end of the period. If 1 is used, payments are due at the beginning of the period.

USING THE PMT FUNCTION

Using the table below, let’s assume we need to invest for three years to receive £85,000 with an annual interest rate of 3,5%. Payments will be made at the start of each month, and the future value is 0.

To calculate the PMT:

1. Select an empty cell and enter the function with its arguments:
   =PMT(B2/12; B3; B4; B5)

1. Press Enter, and the payment amount will be calculated. In this example, the payment is -£2 490,87, as shown in the table below.

IMPORTANT NOTES WHEN USING THE PMT FUNCTION

• A #VALUE! error occurs if non-numeric arguments are provided.
• A #NUM! error occurs if the given interest rate is less than or equal to -1.
• A #NUM! error also occurs if the number of payment periods (nper) is 0.
• When calculating monthly or quarterly payments, ensure the annual interest rate is converted to a monthly or quarterly rate.
• To find the total amount paid over the duration of the loan, multiply the calculated PMT by the total number of payment periods (nper).