Excel RATE #NUM Error: Causes, Examples, and Fixes Explained

Excel’s RATE function returns #NUM! when it cannot find a rate that satisfies the cash-flow inputs, often because the values have no valid solution or the iterative calculation does not converge. Check that the number of periods is positive, cash-flow signs make sense, payment timing is correct, and—if needed—try a different guess.

What the RATE #NUM error means

RATE calculates the interest rate for each payment period in a loan, savings plan, or other series of regular payments. It is a numerical estimate, not a simple division: Excel repeatedly adjusts a candidate rate until the cash flows balance closely enough. If that search fails, the cell shows #NUM!.

The error does not, by itself, identify which input is wrong. It may mean the cash flows cannot produce a real rate under the assumptions, or that Excel’s iterative search did not reach a solution from its starting point. A nonnumeric argument is a different problem and generally produces #VALUE!. Reviewing the values and their meaning is more useful than treating every error as a formatting issue.

RATE is available in Microsoft Excel for Microsoft 365 on Windows, as well as other current Excel editions. The function’s name, arguments, and basic behavior are the same in Excel for Mac and Excel for the web.

How RATE works

The syntax is RATE(nper,pmt,pv,[fv],[type],[guess]). The required arguments are the total number of payment periods, the regular payment, and the present value. Future value, payment timing, and a starting guess are optional. If omitted, future value defaults to zero, timing defaults to end-of-period payments, and the initial guess defaults to 10%.

nper is the total number of periods, such as 36 monthly payments.
pmt is the same payment amount in every period, with the cash-flow sign convention applied.
pv is the value at the start of the series, such as the amount borrowed or deposited.
fv is the balance wanted after the final payment. For a fully repaid loan, it is usually zero.
type is 0 for payments at the end of each period or 1 for payments at the beginning.
guess is the rate Excel should try first. It is a starting point, not a guaranteed answer.

For example, with 12 monthly periods, a $1,000 present value, and payments of about $88.848788678 at each month’s end, the monthly rate is 1%. With periods in A2, payment in A3, present value in A4, future value of zero in A5, and type 0 in A6, enter =RATE(A2,A3,A4,A5,A6) to calculate that rate. The payment is negative and the amount received at the start is positive, because they represent opposite directions of cash flow.

Check the period count and argument values

Start by checking that nper is a positive number and represents the number of payments, not the number of years unless payments are annual. A loan with monthly payments for three years has 36 periods. A zero or negative period count does not describe a normal payment series and can prevent RATE from returning a usable result.

Also check that the optional arguments are intentional. A type of 1 means every payment occurs at the beginning of its period; it is not a way to indicate an annual rate. Use 0 for ordinary end-of-month loan payments. If the payment schedule includes a final balloon balance, enter that amount as fv rather than trying to fold it into the regular payment.

When the data is in cells, inspect the underlying values rather than only their display. A cell formatted as currency may still contain a number, which is fine, but text that looks like a number may not behave as expected. Check for blanks, accidental text, references to the wrong cells, and a payment amount entered in the wrong unit.

Use consistent cash-flow signs

RATE needs cash flows that represent money moving in opposite directions. For a borrower, the loan received is a positive present value and payments made are negative. For a saver, a contribution can be negative from the account holder’s perspective and the ending balance positive. Excel does not know which perspective is correct; it only evaluates the signs and values you provide.

If the present value, payment, and future value are all positive, for example, they describe cash flows all moving in the same direction. There is no ordinary rate that balances that series, so RATE may return #NUM!. Reversing every sign together does not change the calculated rate, but reversing only one value can change the problem being solved.

For a quick sign check, write down what happens at the start, during each period, and at the end. Mark money received as positive and money paid out as negative from one consistent viewpoint. Then make sure the result you expect is compatible with those cash flows. Do not flip signs merely to hide the error if that would misrepresent the actual loan or investment.

Try a different guess when the solver struggles

RATE uses iteration: it starts at the supplied guess, evaluates the cash-flow equation, and adjusts its estimate. Excel’s documentation describes a default guess of 10% and notes that RATE may return #NUM! if it does not converge after 20 iterations. A difficult calculation can sometimes converge from a different starting point, especially when the expected rate is far from 10%.

For the 12-period example above, a 1% starting guess can be supplied explicitly. With the same assumptions—12 periods, payment of -88.848788678, present value 1,000, future value zero, and end-of-period payments—use =RATE(12,-88.848788678,1000,0,0,0.01). The result is approximately 1% per period. Changing the guess does not repair inconsistent inputs; it only gives the numerical search a different starting point.

If a plausible rate is known, try a nearby guess and then a meaningfully different one. Do not assume that a returned value is automatically the intended answer: unusual cash flows can have more than one mathematical solution, and a solver can converge to a different root than the one relevant to the situation. Compare the answer with the contract terms or the expected cash-flow pattern.

Interpret the result in the correct time units

RATE returns a rate for one payment period. If nper and pmt are monthly, the output is a monthly rate; if they are quarterly, it is a quarterly rate. A result of 1% for monthly periods is not automatically a 1% annual rate.

A simple nominal annualized figure is the periodic rate multiplied by the number of periods in a year. With a monthly rate in B8, enter =B8*12 in another cell to calculate that nominal annual rate. To calculate the effective annual rate from a monthly rate in B8, enter =(1+B8)^12-1. At exactly 1% per month, those figures are 12% nominal and about 12.68% effective, because the effective calculation includes monthly compounding.

These conversions assume the same rate applies every period and that there are 12 monthly periods in the year. A lender’s quoted annual percentage rate, fees, compounding rules, and payment schedule may use different conventions. RATE solves the series you enter; it does not automatically account for charges or define a regulated borrowing cost. Check the actual terms with the lender or a qualified professional before relying on a financial figure.

A practical troubleshooting sequence

When RATE displays #NUM!, work through the inputs in a deliberate order rather than changing several values at once:

Confirm that nper is a positive count of payment periods and matches the payment frequency.
Check the signs of pv, pmt, and fv from one consistent cash-flow perspective.
Verify the regular payment amount, any ending balance, and whether payments happen at the start or end of a period.
Make sure the values are numeric and the formula references the intended cells.
Estimate whether a rate can plausibly balance the cash flows. If not, correct the data or revisit the assumptions.
For a plausible series that still fails, provide a different guess, then check whether the returned rate matches the situation.

To isolate a problem, you can temporarily enter the known values directly in a test cell, then restore references once the result makes sense. Keep the test consistent with the real timing and sign convention. If RATE still fails, check whether the contract has irregular payment dates, variable payments, fees, or a changing rate; those details do not fit RATE’s regular-payment assumptions.

When RATE is not the right function

RATE assumes equal payments at regular intervals and one constant rate for the periods being modeled. It is appropriate for standard fixed-payment cash flows, but not for every loan or investment. If payments vary in amount or occur on irregular dates, a single RATE result may misrepresent the series.

For a cash flow with irregular dates, Excel’s XIRR function may be a better fit; it uses dates and does not require evenly spaced payment periods. For a series of regular payments with a known interest rate, PMT calculates the payment instead of solving for the rate. These functions answer different questions, so changing functions is not a general fix for a wrong RATE formula.

If a loan includes fees paid separately, a balloon payment, or a first payment on a different date, decide whether those amounts belong in the modeled cash flows and whether the regular-period assumptions still hold. For money decisions, verify the spreadsheet’s assumptions against the written terms and consult a qualified professional when needed.

Frequently Asked Questions

Why does Excel RATE return #NUM even when my formula looks correct?
RATE can return #NUM! when the cash flows have no suitable solution or its iterative calculation does not converge. Check the signs, period count, payment timing, and optional future value; then try a different guess if the inputs are sound.

What guess should I use in RATE?
The default guess is 10%. If you have a reasonable estimate of the periodic rate, use it as the guess. If the calculation fails, try another plausible starting value, but do not use guesses to conceal incorrect cash flows.

Does RATE return an annual interest rate?
No. It returns a rate per period. Monthly periods produce a monthly rate, which can be annualized separately. The nominal and effective annual rates differ when compounding applies.

Should the loan amount be positive or negative?
It depends on the viewpoint, but the cash flows must be signed consistently. For a borrower, the amount received is commonly positive and payments made are negative. Reversing all signs together is also valid.

Can RATE calculate a loan with changing payments?
Not directly under its regular-payment assumptions. RATE expects the same payment each period. For irregular amounts or dates, a function designed for the cash-flow pattern, such as XIRR for dated cash flows, may be more appropriate.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.