Formuladrill

Financial Updated November 2026

RATE in Excel

RATE(nper, pmt, pv, [fv], [type], [guess]) returns the interest rate per period of a loan or an investment with fixed payments. With monthly payments the result is a monthly rate, so multiply by 12 for the nominal yearly rate. The amount received and the payments must have opposite signs.

Syntax

RATE(nper, pmt, pv, [fv], [type], [guess])
PartWhat it is
nperThe total number of payments.
pmtThe payment each period. Negative when you pay it.
pvThe amount borrowed or invested today.
fvOptional. The balance left after the last payment. Default 0.
typeOptional. 0 for payments at the end of each period (default), 1 for the beginning.
guessOptional. A starting guess for the rate. Default 10%.

Examples

=RATE(36, -320, 10000)

Result: About 0.79% per month

36 payments of 320 on a 10,000 loan.

=RATE(36, -320, 10000)*12

Result: About 9.5% a year

The nominal yearly rate.

=RATE(5, 0, -1000, 1500)

Result: About 8.45%

No payments, 1,000 growing to 1,500 in five years: the compound annual growth rate.

=(1+RATE(36, -320, 10000))^12-1

Result: About 9.9%

The effective yearly rate, with monthly compounding.

Common mistakes

  • #NUM! means payments and amount have the same sign, or no rate fits. Enter the payments as negative numbers.
  • Reporting the monthly result as the yearly rate. Multiply by 12 when the payments are monthly.
  • Counting years in nper while the payment is monthly. nper is the number of payments.
  • A result shown as 1% is often rounded. Format the cell with two decimals before judging it.

Practice RATE

These drills check your answer on live data, free, no account needed.

  • The rate behind a financing offerA dealer finances the amount in B1 against the monthly payment in B2 for the number of payments in B3. Work out the yearly interest rate behind the offer, as the monthly rate times 12, in B4.
  • Compound annual growthRevenue grew from the value in B1 to the value in B2 over the years in B3. Calculate the compound annual growth rate in B4.
  • Effective annual rateA card quotes the nominal annual rate in B1, compounded the number of times per year in B2. Work out the effective annual rate in B3: how much the balance really grows in one year.

Questions

How do I find the interest rate of a loan in Excel?

=RATE(number of payments, -payment, loan amount) gives the rate per payment period. Multiply by 12 for a yearly rate when the payments are monthly.

What is the difference between RATE and IRR?

RATE needs equal payments and takes them as one number. IRR takes a range of cash flows that can all be different.

Can RATE calculate CAGR?

Yes. =RATE(years, 0, -start, end) returns the compound annual growth rate.

Related

  • PMTHow the Excel PMT function calculates the payment on a loan or the deposit for a savings goal, with mortgage and car loan examples and the monthly rate rule.
  • NPERHow NPER works in Excel: the number of periods needed to pay off a loan or reach a savings goal at a fixed rate and payment, with monthly examples.
  • IRRHow the Excel IRR function finds the rate at which cash flows net to zero, with a worked example, the cash flow layout it needs, the #NUM! fix and XIRR.
  • NPVHow the Excel NPV function discounts future cash flows at a rate, why the initial investment is added outside the function, with a worked example and XNPV.

Previous: NPERNext: IPMT and PPMT