Excel RATE Function Not Working? Fix Common Errors

If Excel’s RATE function is returning an error or an unexpected number, check that the cash-flow signs are opposite, the payment and period units match, and the payment timing is correct. For a loan repaid monthly, for example, use =RATE(24,-564.88,12000) to find the monthly rate; the result is approximately 1% per month.

What the RATE function calculates

RATE finds the interest rate for a series of equal payments made at regular intervals. It is commonly used to work backward from a loan amount and payment, or from regular deposits and a future savings target. The result is a rate for one payment period, not automatically an annual rate.

Excel solves for the rate that makes the present value of the payments and the future value balance with the present value. In practical terms, it needs to know how many periods there are, the payment in each period, the value at the start, and optionally the value at the end. If those inputs describe different scenarios or use inconsistent units, a formula can calculate successfully but still give a misleading answer.

RATE uses an iterative numerical calculation. It starts with a guess and searches for a rate that satisfies the cash-flow equation. As a result, some combinations of inputs may not converge, even when the formula is typed correctly.

Check the arguments and payment timing

RATE takes the number of periods, payment per period, present value, and optional future value, payment timing, and starting guess, in that order. If you omit the optional future value and timing, Excel treats them as zero: the balance is paid down to zero, and payments occur at the end of each period.

Number of periods: The total count of payment intervals, not necessarily the number of years.
Payment: The fixed cash flow at each interval. Use zero if there is no regular payment.
Present value: The amount at the beginning, such as the original loan principal or starting investment.
Future value: The balance or target remaining after the last payment. It is commonly zero for a fully repaid loan.
Type: Use 0 for payments at the end of each period or 1 for payments at the beginning. The default is 0.
Guess: An optional starting estimate for the rate per period. The default is 10%.

The type matters because a payment made at the beginning has more time to reduce a loan or earn interest than one made at the end. If the contract calls for payments at the start of each month, leaving the type omitted may produce a different rate from the one that matches the contract.

Use opposite signs for money paid and received

Excel financial functions use signs to distinguish money flowing in from money flowing out. For a borrower, the loan proceeds received at the start and the repayments made afterward are opposite cash flows. A common setup is a positive present value and a negative payment. Reversing both signs is also valid; using the same sign for both usually prevents RATE from finding a meaningful solution.

For a 24-month loan of 12,000 with a monthly payment of 564.88 and no remaining balance, assume the borrower receives 12,000 and pays 564.88 at the end of each month. With that cash-flow layout, enter =RATE(24,-564.88,12000). Excel returns approximately 0.01, which means a monthly rate of about 1%. The payment amount is illustrative; actual loan payments depend on the lender’s terms, fees, and rounding.

For a saver, the direction can be reversed: regular deposits are outflows from the saver’s perspective, while the accumulated future balance is money received. The important point is consistency. If you are unsure, write down the cash flows from one person’s perspective and assign positive and negative signs according to whether that person receives or pays the money.

Make the rate period match the payment period

All RATE inputs must use the same interval. If payments are monthly, nper must count months and the result is a monthly rate. A three-year loan with monthly payments has 36 periods, not 3. If payments are quarterly, count quarters and interpret the result as a quarterly rate. Mixing a yearly period count with monthly payments is one of the most frequent causes of a surprising answer.

To convert a monthly rate into a nominal annual rate, multiply it by 12. To convert it into an effective annual rate, account for monthly compounding. If B2 contains the monthly RATE result, use =(1+B2)^12-1 to calculate the effective annual rate. With B2 equal to 1%, the result is approximately 12.68%, not 12%: the additional amount comes from compounding. These are different conventions, so compare like with like when checking a quoted rate.

Do not divide a RATE result by 12 if the function already solved for a monthly rate. That would convert a monthly result into a smaller number rather than an annual one. If you need a monthly rate from an annual effective rate, the conversion is also not simply division by 12; it must reflect the stated compounding convention.

Fix #NUM! and other calculation errors

#NUM! commonly appears when RATE cannot find a solution from its starting guess. Excel’s default guess is 10% per period, which can be far from the answer or unsuitable for the cash flows. Supply a more realistic guess in the sixth argument. For the same illustrative 24-payment loan, use =RATE(24,-564.88,12000,0,0,0.01) to start the search at 1% per month.

A guess does not force the answer to equal that number; it only gives the calculation a starting point. If one guess still returns #NUM!, try a different plausible value and verify the signs, periods, and payment timing first. The inputs must describe a cash-flow pattern for which a rate can be solved. For example, a zero payment with no changing balance may not provide enough information to identify an interest rate.

#VALUE! often points to an input that Excel cannot interpret as a number. Check for text values, stray spaces, or a number stored as text in a referenced cell. Also confirm that the arguments are in the correct order: putting the payment where the period count belongs can produce an error or an implausible result.

If Excel displays a number but not a percentage, the calculation may be fine. RATE returns a decimal: 0.01 represents 1%. Select the result cell and choose Home > Percent Style, then adjust the displayed decimal places as needed. Formatting changes how the value appears, not the underlying calculation.

Use cell references to make the calculation easier to audit

Putting assumptions in cells makes it easier to spot incorrect inputs and change the scenario. For instance, place 24 in B1, -564.88 in B2, and 12000 in B3. With periods in B1, the signed payment in B2, and the present value in B3, enter =RATE(B1,B2,B3) in B4. The result should be approximately 1% per month. If the payment is actually due at the beginning of each month, add a type argument of 1 rather than leaving the default end-of-period timing.

Before trusting the result, check the underlying source values and the wording of the loan or savings terms. A payment may include insurance, fees, or other charges that are not part of the interest calculation. Likewise, a quoted annual percentage may use a convention different from the effective annual rate you calculated. Excel can solve the inputs you provide, but it cannot determine which charges or rate convention apply.

Know when RATE is not the right function

RATE is appropriate when the regular payments are equal and evenly spaced. If the question is how many equal payments are needed, NPER is the related function. If the payment amount is unknown, PMT calculates a regular payment when the rate and period count are known. These functions solve different unknowns, so choosing one based on what you want to find helps avoid forcing RATE to answer the wrong question.

For a list of cash flows that vary but occur at regular intervals, IRR may be more appropriate. For cash flows occurring on irregular dates, XIRR uses the dates as well as the values. RATE assumes a constant payment schedule; it does not model a changing payment amount or different-length intervals. When fees or extra payments change the schedule, use a cash-flow method that represents those details rather than treating all payments as equal.

Frequently Asked Questions

Why does Excel RATE return #NUM!?
Excel may not converge from its default starting guess, or the inputs may not define a solvable cash-flow pattern. Confirm the signs and period units, then try a realistic guess as the sixth argument. Also verify that the present value, payment, and optional future value are not accidentally stored as text.

Should the loan payment be positive or negative in RATE?
It should have the opposite sign from the loan proceeds when both are entered from the borrower’s perspective. A typical setup uses a positive present value and a negative payment. Reversing both signs is also consistent, but giving the payment and present value the same sign is usually not.

Does RATE return an annual interest rate?
No. It returns a rate per period. With monthly payments and a period count in months, it returns a monthly rate. Convert it using the convention you need: multiplying by 12 gives a nominal annual rate, while compounding monthly gives an effective annual rate.

What does the type argument do in RATE?
It tells Excel when each regular payment occurs. Type 0, the default, means payments are made at the end of each period. Type 1 means payments are made at the beginning. Use the timing stated in the agreement or savings plan.

Why is my RATE result displayed as 0.01 instead of 1%?
RATE returns a decimal value, so 0.01 is equivalent to 1%. Apply percentage formatting with Home > Percent Style to display it as a percentage. If you see 1.00%, the underlying numeric value remains 0.01.

Verify the assumptions before using the result

A working RATE formula is only as reliable as its cash-flow assumptions. Confirm the payment amount and frequency, whether payments happen at the beginning or end of each period, the starting and ending balances, and the signs used for receipts and payments. Then check whether the result is per month, quarter, or year and whether the annual comparison should be nominal or effective. For real borrowing or savings decisions, check the actual terms with the lender or a qualified professional; the example here is for spreadsheet calculation, not financial advice.

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.