#NUM! in RATE: Iteration limits exceeded

The #NUM! error in Excel’s RATE function occurs when the calculation engine cannot solve for an interest rate within 20 iterations. Because an interest rate cannot be calculated through direct algebra when recurring payments and compounding balances are involved, Excel uses an iterative numerical search. If the search algorithm fails to converge on a result accurate to within 0.0000001 (0.00001%) within 20 attempts, Excel stops calculating and returns #NUM!.

Quick Fix

Add a realistic estimate to the optional [guess] argument at the end of your formula: =RATE(nper, pmt, pv, [fv], [type], [guess]). For example, try =RATE(nper, pmt, pv, 0, 0, 0.01) for monthly loans. If that fails, verify that cash inflows and outflows have opposite signs: the present value (pv) and periodic payment (pmt) cannot both be positive numbers.

---

Understanding the Syntax of the RATE Function

The RATE function calculates the interest rate per period for an annuity, loan, or investment:

=RATE(nper, pmt, pv, [fv], [type], [guess])

• nper (Required): Total number of payment periods.
• pmt (Required): Payment made each period; remains constant over the life of the annuity.
• pv (Required): Present value (the lump-sum loan or investment amount).
• fv (Optional): Future value, or cash balance desired after the last payment (defaults to 0).
• type (Optional): When payments are due: 0 = end of period (default), 1 = beginning of period.
• guess (Optional): Your starting estimate for the rate (defaults to 10% or 0.1).

When Excel runs the RATE formula, it begins at your guess (or 10% if omitted). It tests whether that rate equates the present value and future cash flows to zero. It then adjusts the rate and tries again. If the numbers in your formula are contradictory, or if the actual rate is far from 10%, the engine exhausts its 20 attempts and fails with #NUM! as described in #NUM! in Mathematical Iterations: Fixing Excel’s Calculation Limits.

---

The Three Root Causes of #NUM! in RATE

1. Inconsistent Cash Flow Signs (The Sign Convention Trap)

The most common cause of #NUM! in financial formulas is forgetting Excel’s cash flow sign convention. Money you receive is a positive cash inflow (+), while money you pay out is a negative cash outflow (-):

• A Standard Loan: You receive loan proceeds today (pv is positive), but you make monthly payments to the bank (pmt must be negative).
• An Investment Portfolio: You deposit money today (pv is negative), add recurring savings (pmt is negative), and withdraw a lump sum at retirement (fv is positive).

If you enter both pv and pmt as positive numbers:

=RATE(60, 450, 20000)   <-- Fails: Returns #NUM!

Excel reads this as receiving $20,000 today and receiving $450 every month without ever paying anything back. No positive or negative interest rate can balance this transaction, causing the formula to fail immediately.

The Fix:
Ensure your loan payment has a minus sign:

=RATE(60, -450, 20000)  <-- Correct: Returns approximately 1.08% per month

---

2. Period Mismatches (Years vs. Months)

The nper (number of periods) and pmt (payment) must share the same time frequency. If you mix annual terms with monthly payments, the mathematical scenario becomes impossible.

Consider a 5-year auto loan paid monthly:

• Number of periods (nper): 5 * 12 = 60 months.
• Payment (pmt): $400 per month.
• Loan amount (pv): $20,000.

If you write:

=RATE(5, -400, 20000)   <-- Fails: Returns #NUM!

You have told Excel that you will repay a $20,000 loan with only 5 payments of $400 ($2,000 total). Because $2,000 cannot pay off a $20,000 balance, the implied interest rate is massively negative, falling outside the algorithm’s calculation bounds.

The Fix:
Multiply the years by the payment frequency:

=RATE(5 * 12, -400, 20000)

Note: The result returned by RATE reflects the rate per period. To convert a monthly rate to an Annual Percentage Rate (APR), multiply the result by 12:

=RATE(60, -400, 20000) * 12

---

3. Negative Amortization (Payments Below Accrued Interest)

If the recurring payment (pmt) is smaller than the periodic interest accrued on the principal balance, the loan balance grows over time instead of amortizing, as covered in Negative Amortization Errors: Handling loan models where interest exceeds payments..

If a lender provides a loan where payments are insufficient to cover basic principal reduction and no terminal balloon payment (fv) is scheduled, the mathematical iteration cannot converge on a stable rate, returning #NUM!.

The Diagnostic Check:
Check whether the total payments exceed the principal:

=ABS(pmt * nper) > pv

If this expression evaluates to FALSE, you are attempting to amortize a loan by paying back less money in total than the amount borrowed. Unless a positive fv balloon payment is entered, the calculation is financially unviable.

---

Step-by-Step Diagnostic Routine

Follow this diagnostic workflow whenever RATE returns #NUM!:

               [ RATE returns #NUM! ]
                         │
        Are pv and pmt the same sign?
        (e.g., both positive numbers)
             ├── YES ──► Invert pmt to negative (or pv to negative)
             └── NO
                  │
        Is nper in years while pmt is monthly?
             ├── YES ──► Multiply nper by 12
             └── NO
                  │
        Does total payment fall below pv?
             ├── YES ──► Adjust pmt or add [fv] balloon payment
             └── NO
                  │
        Default guess (10%) failed to converge
             └───► Add explicit [guess] (e.g., 0.01 or -0.05)

Step 1: Supply a Periodic Guess

If your signs and periods are correct, the true interest rate may simply be too far from Excel’s default starting point of 10% per period.

Remember: 10% per month equals an annual rate over 120%. A typical monthly auto loan rate is around 0.5% (0.005), which is far from Excel’s 10% default:

• For monthly consumer loans, set guess to 0.005 or 0.01: =RATE(360, -1200, 200000, 0, 0, 0.005)
• For troubled assets or investments with heavy losses, supply a negative guess: =RATE(10, 0, 100000, -20000, 0, -0.1)

This mechanic mirrors the anchor estimate approach used in capital budgeting models, as described in #NUM! in IRR: Why the formula needs a “Guess”.

Step 2: Test Against the PMT Function

If RATE continues to fail despite multiple guess inputs, verify your financial assumptions by running the inverse formula: PMT according to PMT Function Errors: Why your loan amortization doesn’t match the bank statement.:

1. Take an estimated market interest rate (e.g., 6% APR / 12 = 0.5% per month).
2. Enter =PMT(0.005, nper, pv).
3. Compare the calculated payment against the pmt value you passed into RATE. If the payment values diverge wildly, your input parameters contain unrealistic financial assumptions.

For broad diagnostic rules regarding numerical limits and floating-point errors, consult Fixing #VALUE!, #DIV/0!, and #NUM! Errors: The Excel Data Logic Guide.