Skip to content
Excel

RATE function in Excel

Returns the interest rate per period of an annuity

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

RATE syntax and parameters

Six arguments, three required.

  • npernumberRequired

    The total number of payment periods in an annuity.

  • pmtnumberRequired

    The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.

  • pvnumberRequired

    The present value — the total amount that a series of future payments is worth now.

  • fvnumberOptional

    The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). If fv is omitted, you must include the pmt argument.

  • typenumberOptional

    The number 0 or 1 and indicates when payments are due.

  • guessnumberOptional

RATE examples

Formulas you'll actually reuse.

  1. The annual rate implied by $350 a month over 5 years on $18,000 (×12 because periods are months):

    =RATE(60, -350, 18000) * 12

    Result0.0623

  2. The yearly return that doubles $10,000 in 10 years:

    =RATE(10, 0, -10000, 20000)

    Result0.0718

RATE in Google Sheets

Same name — your formula ports as-is.

Try RATE in the playground

Edit the example — nothing to install.

Preloaded with the RATE formula from Example 1 — change anything and watch it respond.

Loading the editor…

RATE errors

What they mean — and the fixes.

  • #NUM!

    The iteration didn't converge — pass a guess (6th argument) near the expected rate, and check that pv and pmt have opposite signs.

Related functions

More ways Excel gets this done.