PMT function

The PMT function in Excel calculates the periodic payment for a loan or investment based on constant payments and a constant interest rate. It is commonly used to determine the monthly or annual payments required to pay off a loan or mortgage over time.

Syntax

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

Parameters

1. rate: The interest rate for each period. For example, if you have an annual interest rate of 5%, you would enter 0.05. If the payment frequency is monthly, you would enter the monthly rate, which is the annual rate divided by 12 (0.05/12).
2. nper: The number of periods (payment periods) for the loan or investment. For example, for a 5-year loan with monthly payments, you would enter 5*12 (60 periods).
3. pv: The present value, or the principal amount of the loan or investment. This is the amount of money you are borrowing or investing.
4. [fv] (optional): The future value or the cash balance you want to attain after the last payment is made. The default is 0, which means the loan is paid off entirely at the end.
5. [type] (optional): The timing of payments:
   • 0 = Payments are due at the end of the period (default).
   • 1 = Payments are due at the beginning of the period.

How It Works

The PMT function calculates the amount of each periodic payment that would pay off a loan or investment, considering the rate of interest, the number of periods, and the loan principal.

The formula behind the PMT function is:

$$\text{Payment} = \frac{P \times r}{1 – (1 + r)^{-n}}$$

Where:

• $P$ = Principal (loan amount)
• $r$ = Interest rate per period
• $n$ = Number of periods

Example

Imagine you are taking out a loan of $10,000 at an annual interest rate of 6% for 5 years, with monthly payments. The formula to calculate the monthly payment would be:

=PMT(0.06/12, 5*12, 10000)

In this example:

• 0.06/12 is the monthly interest rate (annual rate divided by 12).
• 5*12 is the total number of months (5 years with 12 monthly payments per year).
• 10000 is the loan amount (principal).

This formula will return the monthly payment amount required to pay off the loan.

Important Notes

• The result of the PMT function is always a negative number because it represents an outgoing payment.
• The rate should match the payment frequency (monthly, yearly, etc.).
• If you want to calculate a payment for an investment or savings plan where you are making periodic deposits (like a retirement account), the function works the same way, but the payment is positive since you are contributing money rather than borrowing.

Summary

The PMT function is essential for calculating periodic payments for loans, mortgages, and investments. It takes into account the interest rate, the number of periods, and the principal amount to determine how much you need to pay in each period. By understanding the inputs, you can use this function to determine monthly or annual payments for various financial scenarios.