Periodic Interest Rate in Excel: Formula and Examples

To calculate a periodic interest rate in Excel, divide the annual nominal rate by the number of compounding periods per year—for example, enter =8%/12 for a monthly rate from an 8% annual rate. If you know a loan’s payment, term, and present value instead, Excel’s RATE function can solve for the implied rate per payment period.

Formula

The calculator finds the periodic rate that makes the present value of a fixed series of payments equal to the amount borrowed:

PV = PMT × [1 − (1 + r)−n] / r

• PV is the present value or loan amount, in dollars.
• PMT is the payment made each period, in dollars.
• n is the total number of equal payment periods.
• r is the periodic interest rate as a decimal. The calculator reports it as a percentage by multiplying by 100.

Excel’s corresponding function is RATE(nper, pmt, pv, [fv], [type], [guess]). Its required inputs are the number of periods, payment, and present value. The function returns a rate for one period—not automatically an annual rate. The calculator’s second output annualizes the result as a nominal annual rate by multiplying the periodic percentage by 12, assuming monthly periods.

Worked Example

Using the calculator’s default values—60 monthly payments of $405.53 and a present value of $20,000—the periodic rate is found by solving:

20000 = 405.53 × [1 − (1 + r)−60] / r

1. Enter 60 for nper, -405.53 for pmt, and 20,000 for pv in Excel’s RATE function. The payment is negative because it is money paid out, while the loan proceeds are money received.
2. Excel solves for r ≈ 0.006000, or 0.6000% per month.
3. For a nominal annual rate with monthly periods, multiply by 12: 0.6000% × 12 = 7.2000%.

Excel formula: =RATE(60,-405.53,20000)*100 returns approximately 0.6000, meaning 0.6000% per period. To display a nominal annual percentage, use =RATE(60,-405.53,20000)*12*100, which returns approximately 7.2000%. RATE uses an iterative calculation, so results can vary slightly with the guess and displayed rounding. The example payment is rounded to cents; the rate is therefore an implied estimate for those rounded cash flows.

Enter the Formula in Excel

Choose the calculation that matches the information you have. If the annual nominal rate is known, the periodic rate is a direct division. If the loan amount, periodic payment, and term are known, use RATE to solve for the rate:

• Convert an annual nominal rate to a monthly periodic rate: If cell A1 contains 8%, enter =A1/12. The result is 0.6667% per month.
• Find an implied monthly rate from cash flows: Enter =RATE(60,-405.53,20000) for 60 monthly payments of $405.53 and a $20,000 amount received.
• Convert a monthly rate to a nominal annual rate: Multiply the periodic rate by 12, such as =B1*12 when B1 contains the monthly rate as a decimal.

Excel stores percentages as decimal values: 8% is stored as 0.08. Applying percentage formatting changes how a value appears, not its underlying value. Avoid multiplying or dividing by 100 a second time unless you are deliberately converting between a decimal and a displayed percentage.

When to Use Each Method

Use division when the annual nominal rate and compounding frequency are specified. The general formula is periodic rate = nominal annual rate / periods per year. Thus, an 8% nominal rate compounded monthly gives 8% / 12 = 0.6667% per month; compounded quarterly it gives 8% / 4 = 2% per quarter. This conversion does not require a loan payment or term.

Use RATE when the rate itself is unknown but the cash flows are known. It is useful for checking the implied periodic rate of a level-payment loan or annuity. The result depends on the period represented by each payment: if payments are monthly, RATE returns a monthly rate; if they are quarterly, it returns a quarterly rate. Match the number of periods to the payment schedule.

Nominal Rate, Effective Rate, and Compounding

A periodic rate is the rate applied during one compounding or payment period under the stated assumptions. A nominal annual rate is commonly converted by dividing by the number of periods in a year. An effective annual rate accounts for compounding within the year, so it is not generally equal to the nominal rate.

For a periodic decimal rate r applied m times per year, the effective annual rate is (1 + r)^m − 1. For example, a monthly rate of 0.6667% corresponds to an effective annual rate of about 8.30%, not 8%, because monthly compounding accumulates interest on prior interest. Do not multiply a periodic rate by 12 and call the result an effective annual rate; that multiplication gives a nominal annual rate.

Common Excel Errors and Limits

• Wrong cash-flow signs: RATE needs opposing signs for money received and paid. For a borrower’s perspective, enter the loan amount as positive and payments as negative, or reverse both signs consistently.
• Mismatched periods: The payment amount and nper must use the same interval. For monthly payments over five years, use 60 periods, not 5.
• Misreading the result: RATE returns a decimal. A result of 0.006 means 0.6% per period; use percentage formatting or multiply by 100 for a numeric percentage.
• Rounding too early: Keep the full precision of Excel’s result for later calculations, then round only for display where appropriate.
• Unusual cash flows: RATE assumes level, regularly timed payments and solves iteratively. Irregular dates or changing payments may need a different cash-flow method, such as XIRR, rather than RATE.

The simple division formula also assumes the annual figure is a nominal rate and that the stated compounding frequency applies. It does not by itself account for fees, payment timing differences, variable rates, or other loan terms. A quoted APR may include costs beyond the interest rate, so it may not be appropriate to treat every APR as a pure periodic interest rate.

Frequently Asked Questions

What is the Excel formula for a monthly interest rate?
If you know the nominal annual rate, divide it by 12: =annual_rate/12. For an 8% annual rate, the monthly rate is 0.6667%. If you need to infer the rate from payments and a loan amount, use RATE.

Does Excel RATE return an annual interest rate?
No. RATE returns a rate per period. If each payment is monthly, its answer is monthly. Multiply the result by 12 for a nominal annual rate, or use (1+monthly_rate)^12-1 for an effective annual rate.

Why does my RATE formula return an error?
Check that the payment and present value have opposite signs, that the payment is sufficient for the cash flows to make sense, and that periods match the payment schedule. RATE is iterative; supplying a reasonable optional guess can help Excel converge.

How do I calculate the periodic rate from APR?
If APR is a nominal annual rate with monthly compounding, divide it by 12. For example, 7.2% / 12 = 0.6% per month. Confirm how the quoted rate is defined before applying this conversion, especially if fees or other costs are included.

What is the difference between periodic and effective annual interest?
The periodic rate applies during each period. The effective annual rate includes the effect of compounding across the year, calculated as (1 + periodic rate)^periods per year − 1.

This result is an estimate, and the lender’s disclosure gives the exact figures.

Related

• Advanced Loan Payment Calculator: Payments and Interest
• Calculate a Monthly Loan Payment: Formula and Example
• How to Calculate Loan Payments: Formula and Example

in n | Number of payments | - | 60 | 1 | 600
in pmt | Payment per period | $ | 405.53 | 0.01 | 100000000
in pv | Present value | $ | 20000 | 0.01 | 100000000
out r | Periodic interest rate | % | rate(n, pmt, pv) * 100 | 4
out apr | Nominal annual rate | % | r * 12 | 4