Excel Formula for Quarterly Payments: How to Calculate

Formulas & functions · Updated November 22, 2026

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:

  1. Enter the annual interest rate in cell B1 (e.g., 0.05 for 5%).
  2. Enter the number of years in cell B2 (e.g., 5).
  3. Enter the loan amount in cell B3 (e.g., 10000).
  4. In cell B4, calculate the quarterly rate: =B1/4
  5. In cell B5, calculate the total periods: =B2*4
  6. 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 -pv or 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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.