When considering a mortgage, a car loan, or business financing, understanding exactly how much you will pay each month is critical. While you could search for an online loan calculator, building your own in Microsoft Excel gives you complete control to compare different interest rates and loan terms side-by-side.
Excel includes a built-in financial function specifically designed for this purpose: the PMT (Payment) function. It mathematically calculates the constant periodic payment required to pay off a loan at a fixed interest rate over a specified amount of time.
Understanding the PMT Syntax
The PMT function requires three mandatory pieces of information to generate a calculation, and allows for two optional arguments.
=PMT(rate, nper, pv, [fv], [type])
- rate: The interest rate for the loan.
- nper: The total number of payments (Number of Periods) for the loan.
- pv: The Present Value, or the total principal amount you are borrowing.
- fv: (Optional) The Future Value, or a cash balance you want to attain after the last payment is made. (Defaults to 0).
- type: (Optional) When payments are due. 0 indicates the end of the period, 1 indicates the beginning. (Defaults to 0).
The Golden Rule of the PMT Function
The most common mistake people make when using the PMT function is mismatching the timeframes of the arguments. The rate and nper must always represent the exact same timeframe.
If you are making monthly payments on a 5-year loan with an annual interest rate of 6%, you cannot simply type “5” and “6%”.
- You must divide the annual interest rate by 12 to find the monthly rate (6% / 12 = 0.5%).
- You must multiply the years by 12 to find the total number of monthly payments (5 * 12 = 60).
Example: Calculating a Mortgage Payment
Let’s build a simple calculator using cell references. Set up your spreadsheet like this:
- A1: Loan Amount (e.g., $300,000)
- A2: Annual Interest Rate (e.g., 5%)
- A3: Loan Term in Years (e.g., 30)
- A4: Monthly Payment (This is where the formula goes)
Click inside cell A4 and type the following formula:
=PMT(A2/12, A3*12, A1)
How Excel processes this:
1. It takes the annual rate in A2 (5%) and divides it by 12 months.
2. It takes the years in A3 (30) and multiplies by 12 months to get 360 total payments.
3. It takes the total loan amount from A1 ($300,000).
4. It calculates the final result: -$1,610.46.
Why is the Number Negative?
You will immediately notice that the resulting payment is a negative number, often displayed in red or wrapped in parentheses. This is not an error. In financial accounting, cash flowing out of your pocket (a payment) is represented as a negative number.
If you prefer to view the monthly payment as a positive number for aesthetic reasons, you simply need to make the Present Value (the loan amount) negative in your formula. You can do this by placing a minus sign directly in front of the pv cell reference:
=PMT(A2/12, A3*12, -A1)
The result will instantly flip to a positive $1,610.46.