The Excel PMT function calculates the periodic payment for a loan based on a constant interest rate and a constant payment schedule. In its simplest form, the formula is =PMT(rate, nper, pv), where rate is the interest rate per period, nper is the total number of payment periods, and pv is the loan amount (present value). For a 30-year fixed mortgage of $300,000 at an annual interest rate of 6% with monthly payments, the formula =PMT(6%/12, 30*12, 300000) returns -$1,798.65 (the negative sign indicates a cash outflow).
Understanding the PMT Function Syntax
The PMT function has three required arguments and two optional ones:
- Rate (required): The interest rate per period. For monthly payments, divide the annual rate by 12. For quarterly, divide by 4, etc.
- Nper (required): The total number of payment periods. For a 30-year mortgage with monthly payments, that’s 360 (30*12).
- Pv (required): The present value, or the total loan amount. This is the amount you are borrowing.
- Fv (optional): The future value, or a cash balance you want after the last payment. For a standard mortgage, this is 0 (the loan is fully paid off). If omitted, it defaults to 0.
- Type (optional): When payments are due. Use 0 (or omit) for payments at the end of the period (most common), or 1 for payments at the beginning of the period (e.g., rent).
All arguments must be numeric. The result is a negative number because it represents money you pay out. If you prefer a positive number, you can multiply the formula by -1 or put a minus sign before the PMT function, like =-PMT(...).
How to Calculate a Mortgage Payment Step by Step
Suppose you want to calculate the monthly payment for a $250,000 home loan, with a 4.5% annual interest rate, over 15 years. Here’s how to set it up in Excel:
- Open a new worksheet. In cell A1, type “Loan Amount”, in B1 type “Annual Rate”, in C1 type “Years”, and in D1 type “Monthly Payment”.
- Enter the values in the adjacent cells: A2 = 250000, B2 = 0.045 (or 4.5%), C2 = 15.
- In D2, enter the formula:
=PMT(B2/12, C2*12, A2) - Press Enter. The result will be
-$1,912.48. That is your monthly payment (principal and interest only, not including taxes, insurance, or PMI).
If you want to see the payment as a positive number, use =-PMT(B2/12, C2*12, A2).
Key Details: Rate and Nper Must Match
A common mistake is using an annual rate with monthly payments without dividing. The PMT function assumes that the rate and nper are in the same time unit. If you make monthly payments, you must divide the annual interest rate by 12 and multiply the number of years by 12. If you use quarterly payments, divide by 4 and multiply by 4. If you use an annual rate with annual payments, then you can use the annual rate and years directly. For example, for a 5-year car loan of $20,000 at 7% annual interest with monthly payments, the correct formula is =PMT(7%/12, 5*12, 20000), which returns -$396.02. Using =PMT(7%, 5, 20000) would give a wildly incorrect result of -$4,877.82 because it treats the rate as a 7% monthly rate.
Using PMT for Different Mortgage Scenarios
The PMT function is flexible enough to handle various mortgage structures:
- Adjustable-rate mortgages (ARMs): You can calculate the payment for each adjustment period by changing the rate and the remaining term. For example, after a 5-year fixed period, you can recalculate using the new rate and the remaining nper (e.g., 25*12 for a 30-year ARM).
- Interest-only mortgages: For the interest-only period, the payment is simply
=pv * rate(e.g.,=300000 * 6%/12= $1,500). After that period, you can use PMT to amortize the principal over the remaining term. - Balloon payments: If you have a balloon payment due at the end, you can include the future value argument. For instance, a $100,000 loan at 5% annual interest, monthly payments, over 10 years, with a $20,000 balloon at the end:
=PMT(5%/12, 10*12, 100000, -20000)returns-$1,060.66. The negative FV indicates a cash outflow at the end.
Common Errors and How to Fix Them
Here are typical issues users encounter with PMT:
- #NUM! error: This occurs when the rate and nper are inconsistent, such as using a monthly rate with a yearly nper, or when the loan cannot be paid off (e.g., rate is too high relative to payments). Check that your rate and nper are in the same period.
- Negative vs. positive result: As noted, PMT returns a negative number. If you see a positive number, you may have entered a negative loan amount (pv). For example,
=PMT(6%/12, 360, -300000)returns a positive $1,798.65, which represents money received. For clarity, always enter the loan amount as a positive number and accept the negative payment, or use=-PMT(...). - Formatting as currency: The result may show many decimal places. To format, select the cell, go to Home > Number > Currency or Accounting, and choose your desired format.
- Using PMT for a mortgage with escrow: PMT only calculates principal and interest. Taxes, insurance, and PMI are not included. To get a total monthly payment, add those amounts separately.
Comparing PMT with Other Excel Loan Functions
PMT is one of several financial functions in Excel that work together:
- IPMT and PPMT: These calculate the interest and principal portions of a specific payment. For example,
=IPMT(6%/12, 1, 360, 300000)returns the interest for the first month ($1,500), and=PPMT(6%/12, 1, 360, 300000)returns the principal portion ($298.65). - CUMIPMT and CUMPRINC: These calculate cumulative interest or principal paid between two periods. For instance,
=CUMIPMT(6%/12, 360, 300000, 1, 12, 0)gives the total interest paid in the first year. - RATE: If you know the payment amount, you can find the interest rate. For example,
=RATE(360, -1798.65, 300000)*12returns approximately 6% annual rate. - NPER: This tells you how many periods it takes to pay off a loan. For example,
=NPER(6%/12, -1798.65, 300000)/12returns about 30 years.
These functions are useful for building a full amortization schedule. To create one, list periods 1 to 360 in a column, then use IPMT and PPMT for each row, and track the remaining balance by subtracting the principal paid from the previous balance.
Frequently Asked Questions
Why does PMT return a negative number? PMT returns a negative value because it represents an outgoing payment. In financial calculations, cash outflows are negative and inflows are positive. To display a positive number, multiply by -1 or use =-PMT(...).
Can I use PMT for a mortgage with extra payments? PMT assumes fixed payments. To model extra payments, you would need to create an amortization schedule manually or use a more complex formula. You can still use PMT to find the baseline payment, then add extra amounts to reduce the term.
What is the difference between PMT and IPMT? PMT gives the total periodic payment (principal + interest). IPMT gives only the interest portion for a specific period. For example, for a $200,000 loan at 5% annual interest over 30 years, PMT is -$1,073.64, and IPMT for the first month is -$833.33.
Does PMT include property taxes and insurance? No. PMT only calculates principal and interest. Escrow items like property taxes, homeowners insurance, and PMI must be added separately. Many lenders combine these into a single monthly payment, but Excel’s PMT does not account for them.
How do I calculate a mortgage payment if payments are due at the beginning of the month? Use the type argument set to 1. For example, =PMT(6%/12, 360, 300000, 0, 1) returns -$1,789.68, slightly lower than the end-of-period payment because you pay earlier, reducing interest.
Can I use PMT for a loan with a variable interest rate? PMT assumes a constant rate. For variable-rate loans, you would need to break the loan into segments with different rates and recalculate the payment for each segment, adjusting the remaining term and balance.
Important Considerations and Accuracy
When using PMT for real financial decisions, remember that the result is only as accurate as the inputs. Ensure your interest rate is the annual percentage rate (APR) or the actual rate, and that you use the correct compounding period. Most mortgages compound monthly, so dividing by 12 works. If your loan compounds differently, you may need to adjust. Also, PMT does not account for fees, points, or other closing costs that might be rolled into the loan. Always verify your calculations with your lender and consult a qualified financial professional for major decisions. This article is for educational purposes only and does not constitute financial advice.
Related
- Excel Eye Center: Services, Costs & What to Expect
- Excel Employee Rotation Schedule Template Guide
- Excel Employee Hourly Schedule Template: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Calendar Day Formula in Excel: Dates, Weekends & MoreFor the day number within a date, use =DAY(A2), where A2 contains an Excel date; for example, January…
- Using Transpose in Excel: Methods, Formulas, and TipsTo transpose data in Excel, copy the cells, select the destination, and choose Home > Paste > Transpose.…
- Excel COUNTIFS Between Dates: Formula and ExamplesTo count dates between two boundaries in Excel, use COUNTIFS with the same date range twice: once for…
- Filter Date Order in Excel: Sort Dates Oldest to NewestTo put Excel dates in order, select a cell in your data and choose Data > Sort Oldest…
- Minimum Sample Size Formula: How to Calculate ItThe minimum sample size depends on what you are estimating or testing. For a proportion in a large…
- Excel COUNTIF Duplicate Values Only OnceTo count duplicate values only once in Excel, you need to count each distinct value that appears more…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.