Excel RATE Function

RATE solves for the constant interest rate per period from equal payments, a period count, and starting and ending balances.

Use it to infer a loan’s periodic interest rate or the growth rate needed to reach a savings target.

1
Set the number of periods Specify the total number of equally spaced payment periods.
2
Enter the amounts and payment timing Provide the fixed payment, starting amount, and ending amount. Choose whether payments occur at the beginning or end of each period.
3
Calculate the rate per period RATE finds the constant interest rate that balances the starting amount, periodic payments, and ending amount. Monthly periods produce a monthly rate; annual periods produce an annual rate.

RATE syntax & arguments

Syntax

=RATE(nper, pmt, pv, [fv], [type], [guess])
Required Optional
  1. 1

    nper

    Required

    The total number of periods. This should correspond to the rate period. For instance, use months with a monthly rate or years with an annual rate.

  2. 2

    pmt

    Required

    The fixed payment each period. Use a negative amount for money paid out, or 0 when growing only an initial lump sum.

  3. 3

    pv

    Required

    The balance or lump sum at time zero. Money received is positive and money paid out is negative.

  4. 4

    fv

    Optional

    The balance or lump sum at the end of the final period. Money received is positive and money paid out is negative. Defaults to 0.

  5. 5

    type

    Optional

    When periodic payments occur. This changes payment timing, not the dates of pv or fv.

    • 0: Payments at the end of each period. This is the default when the argument is omitted.
    • 1: Payments at the beginning of each period.
  6. 6

    guess

    Optional

    An initial estimate of the rate per period for the iterative calculation. Try another estimate if the calculation does not converge. Defaults to 10%.

Example

=RATE(2,-1100,2100,0,1)

Return the annual interest rate for a 2100 loan repaid over two years with 1100 paid at the beginning of each year and a final balance of 0.

RATE caveats

  • What positive and negative amounts mean

    The sign of pv describes the starting amount: positive for money you receive, such as loan proceeds, and negative for money you pay out, such as an initial savings deposit. The sign of pmt describes each periodic payment: positive for money received and negative for money paid out.

    The sign of fv describes the ending amount: positive for money you’ll receive or have available, and negative for money you’ll owe or pay out. An fv of 0 means no ending amount.

    For example, =RATE(2,-1210,2100) returns 10% per period for a loan repaid in full. =RATE(2,0,-1000,1210) returns the same rate for an initial savings deposit growing to 1210 with no periodic payments.

  • The result follows your period unit

    Annual periods produce an annual rate; monthly periods produce a monthly rate. To convert a monthly result to an effective annual rate, use (1+monthly_rate)^12-1. Multiplying by 12 instead gives a nominal annual rate.

  • Payment timing changes the rate

    Omitted type or 0 means end-of-period payments; 1 means beginning-of-period payments. The same payment amounts can imply a different rate when paid earlier. The pv amount stays at time zero and fv stays at the end of the final period.

  • A lump sum needs a zero payment

    To infer growth from an initial deposit to a final balance, enter 0 for pmt and opposite signs for pv and fv. For example, =RATE(2,0,-1000,1210) returns 10% per period. Putting the initial deposit in pmt would repeat it every period.

  • Payments must stay equal

    RATE assumes fixed payments and equal intervals. Use IRR for varying cash flows at regular intervals.

  • The calculation may fail to find a rate

    Excel searches for a rate starting from guess, which defaults to 10%. If it cannot find a sufficiently accurate result within 20 iterations, it returns #NUM!. A different guess may help, but does not guarantee a result.

  • More than one rate can fit the same cash flows

    Some combinations of starting amount, periodic payments, and ending amount can be balanced by more than one rate. Different guesses can lead to different rates that satisfy the same calculation.

Planning a savings goal? Use the rate you found with PMT to calculate the regular deposit needed to reach a target balance.

Intro RATE practice problem

Solve the intro problem directly here, or open it on its own page.

Open full problem

Intro Implied Interest Pro problem

RATE
Intro ☆☆☆
Pending

Advanced RATE practice problems

Use RATE alongside other Excel functions in realistic, less-prescriptive challenges.

No advanced RATE problems are currently available.