The tutorial shows how to use PMT function in Excel to calculate payments for a loan or investment based on the interest rate, number of payments, and the total loan amount.
Before you borrow money its good to know how a loan works. Thanks to the Excel financial functions such as RATE, PPMT and IPMT, computing the monthly or any other periodic payment for a loan is easy. In this tutorial, we will have a closer look at the PMT function, discuss its syntax in detail, and show how to build your own PMT calculator in Excel.
Calculating loan payments is an important part of personal and business finance. The PMT (Payment) function in Excel allows you to calculate the periodic payment amount for a loan based on constant payments and interest rates.
In this comprehensive guide, I’ll explain what the PMT function is, when to use it, and walk through several examples to help you master this useful Excel function.
What is the PMT Function in Excel?
The PMT function in Excel is one of the financial functions that calculates the payment for a loan based on constant payments and interest rates.
Specifically, it allows you to calculate a loan payment amount per period based on:
- The interest rate of the loan
- The total number of payment periods
- The present value (the total loan amount)
The payment returned by PMT includes principal and interest, but no taxes or fees.
The PMT function is very versatile and can calculate payments for loans with terms in months, quarters, years, etc. All you need to do is be consistent with the time units you specify.
When to Use the PMT Function
The PMT function is useful any time you need to determine the periodic payment amount for a loan.
Here are some examples of when to use PMT in Excel:
- Calculating monthly mortgage payments
- Figuring out monthly car loan payments
- Determining quarterly or annual loan payments
- Creating a loan amortization schedule
- Modeling business loans or cash flows
- Financial planning for personal loans
The PMT function works for all types of loans – including mortgages, auto loans, business loans, personal loans, and more.
How to Use the PMT Function in Excel
Using the PMT function in Excel is straightforward once you understand the syntax and arguments.
PMT Function Syntax
The syntax for the PMT function is:
=PMT(rate, nper, pv, [fv], [type])The PMT function has the following arguments:
- Rate – The interest rate for the loan.
- nper – The total number of payment periods.
- pv – The present value (loan amount).
- [fv] – Optional future value argument.
- [type] – Optional argument for payment timing.
Let’s go over these arguments in more detail:
- Rate – This is the interest rate for the loan. Be sure to use consistent units, like monthly or annually.
- nper – The total number of payment periods. Be sure the units match the rate argument.
- pv – The present value, or total loan amount.
- [fv] – Optional future value after last payment. Defaults to 0.
- [type] – Optional timing of payments, 0 = end of period, 1 = beginning. Defaults to 0.
PMT Examples
Let’s look at some examples of using the PMT function to calculate loan payments in Excel.
Monthly Loan Payment
To calculate the monthly payment on a $100,000 loan at 6% interest for 5 years, use:
=PMT(0.06/12, 5*12, 100000) Result: $1,775.53This calculates the monthly payment amount by dividing the annual interest rate by 12 and specifying 60 monthly periods.
Quarterly Loan Payment
To calculate the quarterly payment on a $500,000 loan at 4.5% interest for 10 years, use:
=PMT(0.045/4, 10*4, 500000)Result: $15,577.71Here we divide the interest rate by 4 for quarterly periods and specify 40 quarters.
Annual Loan Payment
To calculate the annual payment on a $50,000 loan at 8% interest for 15 years, use:
=PMT(0.08, 15, 50000) Result: $5,597.83For annual payments, we don’t need to divide the interest rate.
Loan Payment Timing
To calculate the payment at the beginning of each month on a $75,000 loan at 7% for 6 years:
=PMT(0.07/12, 6*12, 75000, , 1)Result: $1,104.65 The optional 5th argument specifies payment at the beginning of the period with 1.
As you can see, the PMT function is flexible and allows you to calculate payments for all types of loans. The key is being consistent with your time units.
PMT Function Examples
Here are some additional examples of using the PMT function for different loan calculations:
Monthly Mortgage Payment
To calculate the monthly payment on a $300,000 mortgage at 4.5% for 30 years:
=PMT(0.045/12, 30*12, 300000) Result: $1,432.25This returns the standard monthly principal and interest payment for a mortgage.
Car Loan Payment
To calculate the monthly payment on a 5 year $30,000 auto loan at 2.9% interest:
=PMT(0.029/12, 5*12, 30000)Result: $562.07Annual Business Loan Payment
To calculate the annual payment on a $500,000 business loan for 7 years at 6% interest:
=PMT(0.06, 7, 500000) Result: $89,002.06 Loan Amortization Schedule
The PMT function can be used with other Excel functions to create a complete loan amortization schedule showing payments, interest and principal over time.
Useful PMT Resources
Here are some helpful resources if you want to learn more about the PMT function:
-
Microsoft PMT Function Documentation – Official documentation from Microsoft.
-
Excel PMT Function – Examples from ExcelJet.
-
YouTube Video Tutorials – Video lessons on using PMT.
Key Takeaways
The PMT function is an essential Excel tool for calculating loan payments. Here are some key takeaways:
- PMT calculates the periodic payment amount for a loan based on interest rates and payment periods.
- Be consistent with time units for interest rate and number of periods.
- Useful for modeling all kinds of loans – mortgages, auto, business, etc.
- Flexible for monthly, quarterly, annual payments.
- Key inputs are interest rate, total periods, and loan amount.
- Great for loan amortization schedules.
Learning to use PMT allows you to model loan payments and construct financial models more easily in Excel. It’s a must-know function for business analysts, accountants, bankers and anyone working with loans or cash flows in Excel.
:max_bytes(150000):strip_icc()/Syntax-5bf5c47746e0fb0051768699.jpg)
Excel PMT function – syntax and basic uses
The PMT function has the following arguments: PMT(rate, nper, pv, [fv], [type])
Where:
- Rate (required) – the constant interest rate per period. Can be supplied as percentage or decimal number. For example, if you make annual payments on a loan at an annual interest rate of 10 percent, use 10% or 0.1 for rate. If you make monthly payments on the same loan, then use 10%/12 or 0.00833 for rate.
- Nper (required) – the number of payments for the loan, i.e. the total number of periods over which the loan should be paid. For example, if you make annual payments on a 5-year loan, supply 5 for nper. If you make monthly payments on the same loan, then multiply the number of years by 12, and use 5*12 or 60 for nper.
- Pv (required) – the present value, i.e. the total amount that all future payments are worth now. In case of a loan, its simply the original amount borrowed.
- Fv (optional) – the future value, or the cash balance you wish to have after the last payment is made. If omitted, the future value of the loan is assumed to be zero (0).
- Type (optional) – specifies when the payments are due:
- 0 or omitted – payments are due at the end of each period.
- 1 – payments are due at the beginning of each period.
For example, if you borrow $100,000 for 5 years with an annual interest rate of 7%, the following formula will calculate the annual payment:
To find the monthly payment for the same loan, use this formula:
Or, you can enter the known components of a loan in separate cells and reference those cells in your PMT formula. With the interest rate in B1, no. of years in B2, and loan amount in B3, the formula is as simple as this:
Please remember that the payment is returned as a negative number because this amount will be debited (subtracted) from your bank account.
By default, Excel display the result in the Currency format, rounded to 2 decimal places, highlighted in red and enclosed in parenthesis, as shown in the left part of the below. The on the right shows the same result in the General format. 
If youd like to have the payment as a positive number, put a minus sign before either the entire PMT formula or the pv argument (loan amount):
=PMT(B1, B2, -B3) 
Tip. To calculate the total amount paid for the loan, multiply the returned PMT value by the number of periods (nper value). In our case, wed use this equation: 24,389.07*5 and find that the total amount equals $121,945.35.
Calculate weekly, monthly, quarterly and semi-annual payments
Depending on the payment frequency, you need to use the following calculations for rate and nper arguments:
- For rate, divide the annual interest rate by the number of payments per year (which is deemed to be equal to the number of compounding periods).
- For nper, multiply the number of years by the number of payments per year.
The below table provides the details:
| Payment Frequency | Rate | Nper |
| Weekly | annual interest rate / 52 | years * 52 |
| Monthly | annual interest rate / 12 | years * 12 |
| Quarterly | annual interest rate / 4 | years * 4 |
| Semi-annual | annual interest rate / 2 | years * 2 |
For instance, to find the amount of a periodic payment on a $5,000 loan with an 8% annual interest rate and a duration of 3 years, use one of the below formulas.
Weekly payment:
Monthly payment:
Quarterly payment:
Semi-annual payment:
In all cases, the balance after the last payment is assumed to be $0, and the payments are due at the end of each period.
The screenshot below shows the results of these formulas: 
Calculate Loan Payments with Excel PMT Function
FAQ
What is PMT and how do you calculate it?
What is PMT in FV formula?
What is the formula for the monthly payment?
How do you calculate PMT in sheets?
How does PMT work in Excel?
PMT, one of the financial functions, calculates the payment for a loan based on constant payments and a constant interest rate. Use the Excel Formula Coach to figure out a monthly loan payment. At the same time, you’ll learn how to use the PMT function in a formula. PMT (rate, nper, pv, [fv], [type])
How do you calculate PMT in Excel?
In cell C6, the PMT function calculates the monthly payment, based on the annual rate, the number of payments (periods) and the loan amount (present value): =PMT ( (C2/2+1)^ (1/6)-1,C3,C4) Instead of simply dividing the rate by 12, the rate calculation is: (Rate/2+1)^ (1/6)-1
What is a PMT formula?
“PMT” stands for “payment”, hence the function’s name. For example, if you are applying for a two-year car loan with an annual interest rate of 7% and the loan amount of $30,000, a PMT formula can tell you what your monthly payments will be. For the PMT function to work correctly in your worksheets, please keep in mind these facts:
How do you use PMT function?
The same function can be used to write formulas that calculate periodic payments for a mortgage loan, a car loan, or a student loan. As long as the interest rate is constant, the PMT function can be used to determine the loan payment. Depending on the payment frequency, you will have to convert the rate and nper arguments.
