Periodic Interest Rate Formula in Excel

In Excel, calculate a periodic interest rate from an annual percentage rate (APR) with =APR/periods_per_year; for a monthly rate, use =APR/12 when APR is entered as a percentage such as 8%. This calculator works out the monthly rate from APR and the number of payments per year, and shows the rate as a percentage per payment period.

Formula

The periodic rate is the annual percentage rate divided by the number of periods in one year:

Periodic rate (%) = APR (%) / periods per year

To express the result as a decimal for use in many financial formulas:

Periodic rate (decimal) = (APR (%) / 100) / periods per year

• APR is the annual percentage rate entered as a percentage, not a decimal; for example, enter 8 for 8%.
• Periods per year is the number of interest or payment periods in a year, such as 12 for monthly periods or 4 for quarterly periods.
• Periodic rate (%) is the percentage rate for each period.
• Periodic rate as a decimal is the same rate in decimal form, suitable for calculations such as multiplying a balance by the rate.

In Excel, if APR is in cell A2 and periods per year in B2, enter =A2/B2 to return the periodic percentage when A2 contains 8 as a number. If A2 contains a percentage-formatted value such as 8%, use =A2/B2 and format the result as a percentage. Excel stores 8% as 0.08, so the underlying result is 0.0066667, or about 0.6667% per month. Avoid dividing a percentage-formatted cell by 100 a second time.

Worked Example

Using the calculator’s defaults, APR is 8% and there are 12 periods per year:

1. Divide the APR percentage by the yearly frequency: 8 / 12 = 0.6666667.
2. Rounded to four decimal places, the periodic rate is 0.6667%.
3. For the decimal form, convert 8% to 0.08 and divide by 12: 0.08 / 12 = 0.00666667.
4. Rounded to six decimal places, the decimal rate is 0.006667.

The two outputs represent the same rate: 0.6667% per month is approximately 0.006667 as a decimal per month. The small difference in the displayed values comes from rounding.

Assumptions

• The APR is a nominal annual percentage rate supplied as an input; this calculation does not infer an APR from a balance or payment.
• Periods are treated as equal parts of a year, and the APR is divided by the selected number of periods.
• For a monthly calculation, this method uses APR divided by 12. It does not calculate an effective annual yield from compounding.
• There are no money inputs or outputs. Fees and other charges are not included in the periodic-rate calculation.

When to Use a Periodic Rate

A periodic rate is the rate associated with one repeatable interval in a calculation. It is useful when a formula applies interest once per payment period, such as monthly loan-payment arithmetic or a balance update that applies a monthly rate. The rate and the timing of the calculation must match: a monthly rate belongs with a monthly period, while a quarterly rate belongs with a quarterly period.

For a simple monthly balance calculation, the interest portion for a period can be estimated as the balance at the start of the period multiplied by the periodic rate in decimal form. For example, a balance of $1,000 at 0.6666667% per month has a one-period interest calculation of approximately 1000 * 0.006666667 = $6.67, before considering payments, fees, daily accrual conventions, or other account terms. This is an illustration of the arithmetic, not a statement about any particular loan or account.

APR, Periodic Rate, and Effective Annual Rate

These rate terms are related but not interchangeable. A nominal APR divided by the number of periods gives a periodic rate under the simple conversion used here. The effective annual rate accounts for compounding across those periods. If a nominal APR of 8% is compounded monthly, its mathematical effective annual rate is (1 + 0.08/12)^12 - 1, approximately 8.30%. That result is not the same as the nominal 8% APR and may not correspond to a product’s disclosed rate, because definitions and included charges can differ.

Do not divide an effective annual rate by 12 and assume the result is the matching monthly rate. To find the equivalent monthly rate for a known effective annual rate, the compounding relationship is (1 + effective annual rate)^(1/12) - 1. The appropriate conversion depends on what the quoted rate represents and how the balance actually accrues.

Excel Formulas and Common Mistakes

For a numeric APR entered as 8, with the frequency in B2, use =A2/B2 for a percentage-point result, or =A2/100/B2 for a decimal result. For an APR stored as 8% in Excel, use =A2/B2; Excel’s stored value is already 0.08. Format the result cell as Percentage if you want to display 0.6667% rather than 0.006667.

• Mixing percentage and decimal forms: 8% is 0.08 as a decimal. The number 8 used in a calculation as a decimal means 800%, not 8%.
• Dividing by 100 twice: If the input cell already contains 8%, Excel stores 0.08. Dividing it by 100 again makes the result 100 times too small.
• Using the wrong frequency: Divide by 12 for monthly periods, 4 for quarterly periods, and 2 for semiannual periods. Daily conventions can vary, so use the period definition relevant to the calculation.
• Confusing APR with an effective annual rate: The simple division formula is for a nominal APR with the stated periodic frequency, not every annual rate label.
• Rounding too early: Keep the full precision in the formula cell and round only the displayed result when possible. Repeatedly rounding the periodic rate can create small differences in later calculations.

Limits and Alternatives

This conversion assumes the APR is nominal and divided evenly across the stated periods. It does not model daily interest accrual, changing rates, irregular payment dates, fees, or a lender’s particular calculation rules. When a contract or statement specifies a daily rate, day-count basis, or another convention, use that stated method rather than substituting a monthly conversion.

Excel’s RATE function can estimate the periodic rate implied by a series of regular payments and a present value, rather than converting a known APR. Its result depends on the number and timing of payments and on consistent cash-flow signs. It answers a different question from dividing APR by periods per year. For a known APR and a fixed frequency, the direct division formula is the clearest calculation.

Frequently Asked Questions

What is the periodic interest rate formula in Excel?
For a nominal APR stored as a percentage value, use =APR_cell/periods_per_year. For example, an APR of 8% with 12 periods per year gives 0.6667% per period.

How do I calculate a monthly interest rate from APR?
Divide a nominal APR by 12. For an 8% APR, the monthly periodic rate is 8% / 12, or about 0.6667% per month.

Should I enter 8 or 8% in Excel?
Either can work if the formula matches the cell value. If you enter the number 8, use =A2/100/12 for a decimal rate or =A2/12 for a percentage-point result. If you enter 8% as a percentage, Excel stores 0.08, so =A2/12 returns the decimal rate; format it as a percentage to display it.

Is the monthly periodic rate the same as the effective annual rate?
No. The periodic rate is for one month. An effective annual rate reflects compounding over the full year and is calculated using a compounding formula.

Can I use the periodic rate in Excel’s PMT formula?
Yes, when the PMT periods match the rate periods. For monthly payments, use the monthly rate as the first argument and the total number of monthly payments as the number-of-periods argument. Check how the APR and cash flows are defined before relying on the result.

The result is an estimate of the rate conversion; the lender’s disclosure gives the exact figures.

Related

• Loan Amortization Table Excel: Formula and Example
• Loan Amortization Table Calculator and Payment Schedule
• Loan Amortization Factors Table: Formula and Calculator

in apr | Annual percentage rate (APR) | % | 8 | 0.01 | 100
in freq | Payments per year | - | 12 | 1 | 365
out rate | Periodic interest rate | % | apr / freq | 4
out dec | Periodic rate as a decimal | - | (apr / 100) / freq | 6