Financial

RATE Function in Excel

Returns the interest rate per period of an annuity.

Syntax

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

Arguments

  • nper (required): Total number of payment periods
  • pmt (required): Payment made each period
  • pv (required): Present value (loan amount)
  • fv (optional): Future value (default 0)
  • type (optional): 0=end of period, 1=beginning
  • guess (optional): Initial guess for rate

Examples

  • =RATE(60, -500, 25000)*12 - Annual rate for car loan - Result: ~5.5%

Excel RATE function documentation

  • RATE calculates the interest rate per period for an annuity (loan or investment) given payments.
  • Syntax: =RATE(nper, pmt, pv, [fv], [type], [guess]). Iterative — needs reasonable guess sometimes.
  • Use to derive implied loan rate from payment schedule.
  • Related: [PMT](/functions/pmt/), [NPER](/functions/nper/), [PV](/functions/pv/), [IRR](/functions/irr/).

RATE syntax and sign convention

  • nper: total periods; pmt: payment per period; pv: present value (loan amount).
  • Cash outflows negative, inflows positive — match [PMT](/functions/pmt/) convention.
  • fv optional: balloon at end; type 0=end of period, 1=beginning.
  • guess optional — try 0.1 if #NUM! on unusual inputs.

Step-by-step: find loan APR per month

  • Step 1 — 360 months, payment -1000, loan amount 150000 (positive pv as cash received).
  • Step 2 — =RATE(360, -1000, 150000).
  • Step 3 — Result is monthly rate — multiply by 12 for nominal annual.
  • Step 4 — Format as percentage.
  • Step 5 — Validate with [PMT](/functions/pmt/) plugging rate back.

RATE vs IRR vs EFFECT

  • RATE — constant periodic payment annuity.
  • IRR — irregular cash flow series.
  • EFFECT — effective annual from nominal + compounding.
  • Financial hub: [/categories/financial/](/categories/financial/).

Worked examples to copy

  • Monthly rate: =RATE(60, -500, 25000).
  • With guess: =RATE(12, -100, 1000, 0, 0, 0.05).
  • Annual display: =RATE(n,pmt,pv)*12.
  • Beginning payments: type argument =1.

People also ask

  • RATE #NUM!? — Adjust guess or check inconsistent pmt/pv/nper.
  • RATE annual or monthly? — Same period unit as nper and pmt.
  • RATE vs interest rate in PMT? — RATE solves for rate; PMT uses known rate.
  • RATE negative? — Possible for certain cash flow sign patterns.

Common errors

  • #NUM! if cannot converge
  • Multiply by 12 for annual rate

Use cases

  • Loan rate calculation
  • Investment returns
  • Mortgage analysis

Frequently asked questions

  • Why do I need to multiply by 12? RATE returns the rate per period. For monthly payments, multiply by 12 for annual rate: =RATE(360, -1500, 300000)*12 gives annual mortgage rate. For quarterly payments, multiply by 4.
  • Why does RATE return #NUM! error? RATE uses iteration and may not converge if inputs are unrealistic. Try providing a guess closer to expected rate: =RATE(60, -500, 25000, 0, 0, 0.05) starts with 5% guess. Also check that payment sign is opposite to loan amount.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use RATE

  • Loan rate calculation — common Financial scenario for RATE.
  • Investment returns — common Financial scenario for RATE.
  • Mortgage analysis — common Financial scenario for RATE.

RATE in the Financial category

  • Browse all Financial functions at /categories/financial/ for related formulas.
  • RATE syntax: =RATE(nper, pmt, pv, [fv], [type], [guess])
  • nper (required): Total number of payment periods
  • pmt (required): Payment made each period
  • pv (required): Present value (loan amount)
  • fv (optional): Future value (default 0)
  • Confirm RATE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm RATE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • PMT (/functions/pmt/): Calculates the payment for a loan based on constant payments and a constant interest rate.
  • NPER (/functions/nper/): Returns the number of periods for an investment based on periodic payments and a constant interest rate.
  • PV (/functions/pv/): Returns the present value of an investment - the total amount that future payments are worth now.
  • FV (/functions/fv/): Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

Errors to watch for

  • #NUM! if cannot converge — review causes on linked error pages in the directory.
  • Multiply by 12 for annual rate — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter RATE on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

RATE worked examples to copy

  • =RATE(60, -500, 25000)*12 — Annual rate for car loan. Expected result: ~5.5%.

RATE reference summary for crawlers and offline review

  • RATE belongs to the Financial category in Excel. Returns the interest rate per period of an annuity.
  • Full syntax: =RATE(nper, pmt, pv, [fv], [type], [guess]). Open /functions/rate/ for parameters, FAQs, and related pages.
  • Common mistakes: #NUM! if cannot converge; Multiply by 12 for annual rate
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.