The simplest Excel formula for a quarterly payment is =PMT(rate, nper, pv), where rate is the quarterly interest rate, nper is the total number of quarterly periods, and pv is the loan amount (entered as a negative number). For example, =PMT(0.05/4, 4*5, -10000) returns the quarterly payment for a $10,000 loan at 5% annual interest over 5 years.
Understanding the PMT Function
The PMT function calculates the payment for a loan or investment based on constant payments and a constant interest rate. It is the standard Excel tool for any periodic payment calculation, including quarterly. The function syntax is:
=PMT(rate, nper, pv, [fv], [type])
- rate – interest rate per period. For quarterly payments, divide the annual rate by 4.
- nper – total number of payment periods. For quarterly payments over N years, use
N*4. - pv – present value, or the total loan amount. Enter as a negative number to get a positive payment result.
- [fv] – future value (optional, default 0). Use if you want a balloon balance at the end.
- [type] – when payments are due (optional, default 0 for end of period; 1 for beginning).
The result is the payment per quarter, expressed as a negative number if you entered pv as positive. To display it as a positive value, either enter pv as negative or wrap the formula with =-PMT(...).
How to Set Up a Quarterly Payment Schedule
Beyond a single payment, you may want a full amortization schedule. Here’s a step-by-step setup:
- Enter the annual interest rate in cell B1 (e.g., 0.05 for 5%).
- Enter the number of years in cell B2 (e.g., 5).
- Enter the loan amount in cell B3 (e.g., 10000).
- In cell B4, calculate the quarterly rate:
=B1/4 - In cell B5, calculate the total periods:
=B2*4 - In cell B6, calculate the quarterly payment:
=PMT(B4, B5, -B3)
For an amortization table, list periods 1 through N in column A (e.g., A9:A28 for 20 quarters). Then for each period, calculate interest, principal, and balance:
- Interest for period t:
=balance_before * $B$4 - Principal:
=payment - interest - New balance:
=previous_balance - principal
Use absolute references (like $B$4) to lock the rate and payment cells.
Choosing the Right Rate and Periods
The most common mistake is using the annual rate directly in PMT. Because payments are quarterly, you must convert the annual rate to a quarterly rate. If the annual rate is a nominal rate (simple annual interest), divide by 4. If it’s an effective annual rate (compounded annually), use =(1+annual_rate)^(1/4)-1 to get the true quarterly rate. Similarly, the number of periods must be in quarters: 4 times the number of years.
For example, a 6% annual nominal rate over 3 years gives a quarterly rate of 1.5% and 12 periods: =PMT(0.06/4, 12, -5000) returns $460.17 per quarter. If the 6% is effective, the quarterly rate is =(1.06)^(0.25)-1 ≈ 1.467%, giving a slightly different payment of $459.79.
Common Errors and How to Fix Them
Several pitfalls trip up users:
- Wrong rate conversion – Using the annual rate directly yields a payment that is too low (since it assumes annual compounding). Always divide by 4 for nominal rates.
- Wrong number of periods – Forgetting to multiply years by 4 results in a payment that is too high. For 5 years, use 20, not 5.
- Sign of pv – If you enter pv as a positive number, PMT returns a negative payment. That’s not wrong, but it can be confusing. Use
-pvor wrap with-PMT(...). - Using 0 for fv when there is a balloon – If you plan to owe a lump sum at the end, include the fv argument. For example,
=PMT(0.05/4, 20, -10000, 2000)gives a payment that leaves $2,000 owed after 5 years. - Type argument – If payments are due at the beginning of each quarter, set type=1. This slightly reduces the payment because interest accrues less.
Comparing Quarterly vs. Monthly Payments
Sometimes you need to compare a quarterly payment plan with a monthly one. The key is to use the same effective rate. For a nominal annual rate, the monthly rate is annual/12 and the number of periods is years*12. For example, a $10,000 loan at 6% nominal over 5 years:
- Quarterly:
=PMT(0.06/4, 20, -10000)→ $579.98 per quarter - Monthly:
=PMT(0.06/12, 60, -10000)→ $193.33 per month
Total paid quarterly: $579.98 * 20 = $11,599.60. Total paid monthly: $193.33 * 60 = $11,599.80. They are nearly identical, but monthly payments are slightly higher in total due to more frequent compounding. The difference is tiny for nominal rates, but for effective rates, the conversion matters more.
Using IPMT and PPMT for Interest and Principal
If you need to know how much of each quarterly payment goes to interest versus principal, use the IPMT and PPMT functions. They share the same arguments as PMT but require a period number:
=IPMT(rate, per, nper, pv) – interest portion for period per
=PPMT(rate, per, nper, pv) – principal portion for period per
For example, with a 5% annual rate, 20 quarters, and $10,000 loan, the interest in the first quarter is =IPMT(0.05/4, 1, 20, -10000) which returns $125.00. The principal is =PPMT(0.05/4, 1, 20, -10000) which returns $443.29 (since the payment is $568.29). These functions are useful for tax reporting or tracking equity.
Frequently Asked Questions
How do I calculate quarterly payments in Excel for a car loan?
Use the same PMT formula. For a $15,000 car loan at 4.5% annual interest over 4 years, enter =PMT(0.045/4, 4*4, -15000) which returns $1,027.42 per quarter. Make sure the rate is divided by 4 and the years multiplied by 4.
What if my interest rate is compounded annually?
If the annual rate is an effective annual rate (EAR), convert it to a quarterly rate with =(1+annual_rate)^(1/4)-1. For example, 6% EAR gives =(1.06)^(0.25)-1 ≈ 1.467%. Then use that in PMT. If the rate is quoted as nominal (APR), just divide by 4.
Can I use PMT for quarterly savings contributions?
Yes. PMT works for future value calculations too. To find out how much you need to save each quarter to reach a target, use the fv argument. For example, to have $50,000 in 10 years at 4% annual interest compounded quarterly, use =PMT(0.04/4, 10*4, 0, 50000) which returns -$1,010.22 per quarter (negative because it’s a deposit).
Why does my PMT result show a negative number?
Excel returns a negative number because it represents cash outflows (payments). To see a positive number, either enter the present value as a negative number (e.g., -10000) or wrap the formula with a minus sign: =-PMT(...).
How do I calculate the total interest paid over the loan term?
Multiply the quarterly payment by the total number of periods, then subtract the principal. For example, with a $10,000 loan, 20 quarters, and a payment of $568.29, total paid is $11,365.80, so interest is $1,365.80. In Excel, you can use =PMT(rate, nper, pv)*nper + pv (with pv as positive) to get the total interest directly.
Is there a difference between quarterly and every-3-months payments?
No, they are the same. Quarterly means four times per year, which is every three months. The formula treats each period as equal length, so it doesn’t matter if you call it quarterly or every 3 months.
Related
- Excel Center Kokomo Indiana: What It Is & How to Enroll
- Excel Car Loan Amortization Template: Build Your Own
- Excel Calendar Template with Notes? How to Make One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF in Excel: How It Works and ExamplesIn Excel, COUNTIF counts cells in a range that meet one condition. Its syntax is =COUNTIF(range, criteria); for…
- Excel SUM Based on Another Column: Formulas and ExamplesTo sum numbers in Excel according to a value in another column, use SUMIF: with categories in A2:A5…
- Remove Spaces in Excel Rows: Trim, Find, and ReplaceTo clean spaces at the start or end of text, or reduce repeated spaces between words, use Excel’s…
- XLOOKUP Excel Criteria Match: Exact and Multiple CriteriaYes. XLOOKUP can match a criterion exactly by default, and it can also search with approximate or wildcard…
- Link Cells in Excel: Formulas, Sheets, and WorkbooksYes. In Excel, you can link cells with a formula that displays another cell’s value, whether the source…
- Isnumber in C++: Standard Function or Platform Extension?No: C++ has no standard function named isnumber. To test whether one character is a decimal digit, use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.