Excel RATE Function
last modified October 5, 2026
The RATE function calculates the interest rate per period of an
annuity. It's essential for financial analysis, loan calculations, and
investment planning. This tutorial provides a comprehensive guide to using
the RATE function with detailed examples. You'll learn basic
syntax, practical applications, and advanced techniques to master this
financial function.
RATE Function Basics
The RATE function returns the interest rate per period for a loan
or investment. Excel does not solve the annuity equation in closed form; it
starts from a guess and refines it step by step. RATE is calculated by
iteration and can have zero or more solutions. If the successive results do
not converge to within 0.0000001 after 20 iterations, RATE returns the
#NUM! error value.
| Component | Description |
|---|---|
| Function Name | RATE |
| Syntax | =RATE(nper, pmt, pv, [fv], [type], [guess]) |
| Required Arguments | nper, pmt, pv |
| Optional Arguments | fv, type, guess |
| Return Value | Interest rate per period |
This table breaks down the essential components of the RATE
function. It shows the function name, syntax format, required and optional
arguments, and return value characteristics.
The first three arguments are required and describe the annuity; the three optional arguments fine-tune the result.
| Argument | Meaning |
|---|---|
| nper | Total number of payment periods. Multiply the years by the periods per year, for example 5*12 for a five-year monthly loan. |
| pmt | Payment made each period, unchanged over the life of the annuity. Money you pay out is entered as a negative number, money you receive as a positive number. |
| pv | Present value: the amount borrowed or invested today. A loan you receive is entered as a positive number. |
| fv | Future value: the cash balance to reach after the last payment. When omitted it defaults to 0, meaning the loan is paid off in full. |
| type | Payment timing: 0 or omitted for the end of a period, 1 for the beginning. |
| guess | Initial estimate of the rate, expressed for one period. When omitted it is assumed to be 0.1 (10 percent). |
The nper, pmt and guess arguments must
all use the same time unit, and the returned rate is a rate for that same
unit. A monthly loan is described with a count of months, a payment per
month, and a monthly guess. Microsoft's own example is a four-year loan at
12 percent annual interest, written as 4*12 for
nper and 12%/12 for guess. Mixing
units produces a meaningless rate or an iteration that never settles.
Basic Loan Interest Rate Calculation
This example calculates the monthly interest rate for a loan with fixed payments. We'll determine the rate for a $10,000 loan paid over 5 years.
| Parameter | Value |
|---|---|
| Loan Amount (pv) | $10,000 |
| Term (nper) | 60 months |
| Monthly Payment (pmt) | -$200 |
| Future Value (fv) | 0 |
| Type | 0 (payments at end) |
The table shows loan parameters for calculating the monthly interest rate. Negative payment indicates cash outflow. Future value is zero as loan will be fully paid.
=RATE(60, -200, 10000)
This formula calculates the monthly interest rate for a $10,000 loan with 60 monthly payments of $200. The result is approximately 0.62% per month. To get the nominal annual rate, multiply by 12 (7.42%). Because the payments repeat every month, the rate that comes back is a monthly rate, not an annual one.
Investment Growth Rate Calculation
This example calculates the required annual return rate for an investment to reach a target value. We'll find the rate needed to grow $5,000 to $10,000.
| Parameter | Value |
|---|---|
| Present Value (pv) | -$5,000 |
| Future Value (fv) | $10,000 |
| Term (nper) | 10 years |
| Payment (pmt) | 0 |
The table shows investment parameters. Negative present value indicates initial
cash outflow. No periodic payments are made in this lump-sum investment, so
pmt is zero and the whole answer rests on pv and
fv.
=RATE(10, 0, -5000, 10000)
This formula calculates the annual growth rate needed to turn $5,000 into
$10,000 in 10 years. The result is approximately 7.18% per year, which is the
same as (10000/5000)^(1/10)-1. This shows how RATE can determine
required investment returns.
Annuity Interest Rate with Payments at Beginning
This example calculates the interest rate for an annuity with payments at the beginning of each period. We'll find the rate for a $100,000 annuity that is repaid with 15 annual payments of $12,000 due at the start of each year.
| Parameter | Value |
|---|---|
| Present Value (pv) | $100,000 |
| Annual Payment (pmt) | -$12,000 |
| Term (nper) | 15 years |
| Type | 1 (beginning) |
The table shows annuity parameters. The present value is positive because the $100,000 is received today; the payment is negative because it is paid out. Type 1 indicates payments at period start. Future value defaults to zero.
=RATE(15, -12000, 100000, 0, 1)
This formula calculates the annual interest rate for a $100,000 annuity paid
back with $12,000 at the start of each year for 15 years. The result is
approximately 10.08%. Changing type to 0, so that the same
payments fall at the end of each year, gives 8.44%. The timing parameter is
not cosmetic: with type 1 the money is outstanding for one period less each
year, so the same payments imply a higher rate.
Loan with Balloon Payment
This example calculates the interest rate for a loan with a balloon payment. We'll find the rate for a $50,000 loan with $850 monthly payments and a $10,000 balloon.
| Parameter | Value |
|---|---|
| Loan Amount (pv) | $50,000 |
| Monthly Payment (pmt) | -$850 |
| Term (nper) | 60 months |
| Balloon Payment (fv) | -$10,000 |
The table shows loan parameters with balloon payment. Both payment and future value are negative as cash outflows. Term is 5 years (60 months).
=RATE(60, -850, 50000, -10000)
This formula calculates the monthly interest rate for a $50,000 loan with $850
monthly payments and a $10,000 balloon due after 5 years. The result is
approximately 0.58% per month, which becomes 6.99% per year when multiplied by
12. The balloon changes the rate because $10,000 of the principal is still
owed when the payments stop; setting fv to 0 for the same $850
payment would describe a fully amortizing loan and returns only 0.07% per
month.
Using the Guess Parameter
RATE reaches its answer by iteration. It starts at the guess
argument, evaluates the annuity, adjusts the rate and repeats, stopping when
two successive results agree to within 0.0000001 or after 20 iterations. If
the iteration does not settle in those 20 steps, RATE returns
#NUM!, and a different guess is the documented remedy. RATE
usually converges if guess is between 0 and 1.
| Parameter | Value |
|---|---|
| Present Value (pv) | $1,000 |
| Payment (pmt) | -$300 |
| Term (nper) | 5 periods |
| Guess | 0.15 (15%) |
The table shows a $1,000 amount received today and repaid in five installments of $300. The payment is large relative to the present value, so the rate sits far above the 10% default guess.
=RATE(5, -300, 1000, 0, 0, 0.15) ' 15.24% per period =RATE(5, -300, 1000, 0, 0, 0.05) ' 15.24% per period =RATE(5, -300, 1000, 0, 0, 0.25) ' 15.24% per period
The three formulas return the same 15.24%. The guess is only the starting
point of the search, not an input to the annuity equation, so it never changes
a converged answer. What a guess changes is whether Excel gets there: when
#NUM! appears for cash flows that do have a solution, pass a
guess closer to the expected rate, expressed for one period. Omitting the
argument uses 0.1 (10%) per period, which is fine for ordinary loans but is a
poor starting point for a rate of 15% or 0.5%.
Comparing Loan Options
This example compares two loan options by calculating their effective rates. We'll determine which option has better terms.
| Parameter | Option 1 | Option 2 |
|---|---|---|
| Loan Amount | $25,000 | $25,000 |
| Term | 36 months | 48 months |
| Payment | -$800 | -$650 |
The table compares two loan options with different terms and payments. We'll calculate monthly rates for both to determine which is more favorable. Both terms are counted in months, so both rates are monthly rates and can be compared directly.
=RATE(36, -800, 25000) ' Option 1: 0.79% monthly =RATE(48, -650, 25000) ' Option 2: 0.94% monthly
These formulas calculate monthly rates for both options. Option 1 charges 0.79% monthly (9.43% nominal annual), Option 2 charges 0.94% monthly (11.32% nominal annual). Option 1 also costs less in total: 36 payments of $800 come to $28,800 against $31,200 for 48 payments of $650. The cheaper option is therefore the one with the higher payment.
Cross-Checking RATE with PMT
RATE picks the rate that balances the annuity equation. The easiest way to
prove a result is to hand the rate back to PMT, which prices the
same annuity from the other side. The payment you started with should come
out again.
=PMT(RATE(60, -200, 10000), 60, 10000) ' -200 =PMT(RATE(60, -850, 50000, -10000), 60, 50000, -10000) ' -850
Both formulas return the original payment, up to floating-point rounding,
which confirms that the rate, the term and the payment describe the same loan.
A mismatch here means the arguments were entered inconsistently, most often a
period in nper that does not match the period of pmt.
=PV(RATE(60, -200, 10000), 60, -200) ' 10000 =PV(RATE(60, -200, 10000), 60, -200) - 10000 ' 0
The PV function rebuilds the loan amount from the rate and the
payments, so it returns the present value you started with and the difference
is zero. The same idea works with NPV: discounting the payment
series at the rate returned by RATE reproduces pv. Use one of
these checks before quoting a rate.
Annualizing a Periodic Rate
RATE always answers for one period. To express the answer per year you multiply by the number of periods in a year, which gives the nominal annual rate. That multiplication only labels the rate; it does not describe what the money actually earns once interest compounds.
=RATE(60, -200, 10000) * 12 ' 7.42% nominal =(1 + RATE(60, -200, 10000))^12 - 1 ' 7.68% effective =EFFECT(RATE(60, -200, 10000) * 12, 12) ' 7.68% effective
Because the payments repeat twelve times a year, the annual effect of the monthly rate is 7.68%, not the 7.42% that simple multiplication suggests. Quote the nominal figure when you are comparing with a stated annual percentage rate, and the effective figure when you are comparing what a loan costs or an investment earns. Multiplying is valid only when the periods are equal and cover the year; for quarterly payments multiply by 4, and when the compounding frequency differs from the payment frequency, convert the rate instead of multiplying it.
RATE or IRR
RATE and IRR solve the same problem, the rate that
makes a stream of cash flows add up to zero, but they take the cash flows in
different shapes. RATE describes an annuity with six arguments: one payment,
one count, one present value. IRR takes a range with a separate value for each
period. Both iterate from a guess of 10 percent and both can return
#NUM! when the iteration fails.
' A1 holds -10000, B1:BI1 hold 200 (60 payments) =RATE(60, -200, 10000) ' 0.6183% per month =IRR(A1:BI1, 1%) ' 0.6183% per month
The two answers are identical because the loan amount in pv is
exactly the cash flow in the first cell and the payment is the repeated value
that follows. Use RATE when the payments are equal, since it is compact and
takes type and fv directly. Use IRR when the amounts
change from period to period, such as a project with a different return each
year, and pass a guess as the second argument when the default one does not
converge. For cash flows that are not evenly spaced in time, use
XIRR instead.
RATE Errors and Troubleshooting
Most RATE problems come from impossible cash flows, mismatched units, or an iteration that never settles.
| Cause | Symptom | Fix |
|---|---|---|
| nper is 0 or negative | #NUM! error | Use at least one payment period |
| pv, pmt and fv all have the same sign | #NUM! error | Enter cash you receive and cash you pay with opposite signs |
| Iteration does not converge within 20 steps | #NUM! error | Supply a guess between 0 and 1 that is close to the expected rate |
| guess is given for a different period than nper | #NUM! error or slow convergence | Express guess per period, for example 12%/12 for monthly |
| An argument is text | #VALUE! error | Make sure the referenced cells contain numbers |
| nper and pmt use different units | Rate is far too high, too low, or negative | Match units, for example 60 with -200, not 5 with -200 |
| type is a value other than 0 or 1 | Payments treated as beginning-of-period | Use exactly 0 or 1 |
A rate that looks wrong is usually a unit mismatch: describing the $10,000
loan as =RATE(5, -200, 10000) treats the payments as yearly and
returns -47.33%, because five payments of $200 never repay $10,000. Keep
nper and pmt in one unit and the sign convention
straight, then confirm the answer with PMT or PV.
The RATE function is the tool Excel offers for pricing any
annuity: given the payment, the term and the amount, it returns the periodic
rate that ties them together. Because Excel reaches that rate by iteration
rather than by a fixed formula, three habits keep it reliable. Keep
nper, pmt and guess in the same period,
make the cash-flow signs point in opposite directions, and hand in a
guess whenever the function answers with #NUM!.
Check any figure you intend to publish by feeding it back into
PMT or PV, and remember that multiplying the periodic
rate gives the nominal annual rate while compounding it gives the effective
one. With those habits, RATE gives trustworthy results for loans, savings
plans and investments alike.
Author
List all Excel Formulas.