How do I annualize a monthly interest rate from RATE?

Multiply the monthly rate by 12. For example, if =RATE(60, -350, 18000) returns 0.00354, multiply by 12 to get 0.0425 or 4.25% annual. This assumes simple annualization; for exact compounding, use (1 + monthly_rate)^12 - 1.

What's the difference between RATE and IRR?

RATE assumes fixed periodic payments, while IRR handles variable or irregular cash flows. Use RATE for loans and savings; use IRR for projects with mixed deposits/withdrawals or one-time cash flows.

Why does RATE keep returning #NUM! even though my numbers look correct?

RATE uses iteration and may not converge if the parameters are mathematically conflicting. Try adding a guess parameter closer to the expected rate (e.g., 0.1 for ~10%), or verify pmt and pv have opposite signs and nper is positive.

Can RATE account for payments at the start of the month instead of the end?

Yes, use the type parameter: set type=1 for beginning-of-period payments, type=0 (default) for end-of-period. Beginning-of-period payments will yield a slightly higher interest rate because money has more time to grow.

RATE function

RATE calculates the interest rate per period for an investment or loan given regular periodic payments and present/future values.

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

Generate a RATE formula

Describe what you need. The generator will reach for RATE where RATE is the right tool, and tell you when it is not.

Arguments

How RATE reads its arguments

Argument	Required	Description
nper	Required	The total number of payment periods; must be positive. If zero or negative, returns #NUM!.
pmt	Required	The payment amount per period; positive for money received, negative for money paid out. If zero, the formula simplifies and may return #NUM! if pv and fv have the same sign.
pv	Required	The present value (principal or initial loan amount); negative when representing money out, positive when money in.
fv	Optional	The future value (balance at the end); defaults to 0. If omitted, assumes you pay off the loan completely.
type	Optional	Payment timing: 0 (default) for end-of-period, 1 for beginning-of-period. Non-zero/non-one treated as 1.
guess	Optional	Initial guess for the rate to speed iteration; defaults to 10% (0.1). Use if RATE converges slowly or returns #NUM!.

Returns

A decimal number representing the interest rate per period (e.g., 0.0425 for 4.25%).

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate your car loan's annual interest rate

Category	Month	Budgeted	Actual	Variance
Car Payment	Jan	$350	$350	$0
Car Payment	Feb	$350	$350	$0
Car Payment	Mar	$350	$350	$0
...	...	...	...	...
Car Payment	Dec (Year 5)	$350	$350	$0

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

Result: 0.0425 (or 4.25% annual)

You budgeted $350/month for 60 months on an $18,000 car loan with $0 remaining. RATE(60, -350, 18000) finds the monthly rate (~0.354%), then multiplying by 12 annualizes it. This car loan carries a 4.25% APR.

2. Find your savings account's monthly interest rate

Category	Month	Budgeted	Actual	Variance
Savings	Jan	$200	$200	$0
Savings	Feb	$200	$200	$0
Savings	Mar	$200	$200	$0
Savings	Apr	$200	$200	$0
Savings	May	$200	$200	$0
Savings	Jun	$200	$200	$0

=RATE(6, -200, 0, 1305)

Result: 0.0083 (or 0.83% monthly)

You deposited $200 each month for 6 months (Actual column) and ended with $1,305 instead of $1,200. The extra $105 came from interest. RATE finds the monthly rate that grows $0 through six $200 deposits to reach $1,305, yielding 0.83% monthly or roughly 10% annualized.

3. Calculate your investment portfolio's annual return rate

Category	Month	Budgeted	Actual	Variance
Investment	Jan	$200	$200	$0
Investment	Feb	$200	$200	$0
Investment	Mar	$200	$200	$0
...	...	...	...	...
Investment	Dec	$200	$200	$0

=RATE(12, -200, -5000, 7450) * 12

Result: 0.0844 (or 8.44% annual)

You started with $5,000 invested (pv = -5000, money out), added $200 monthly for 12 months (-200 payments), and your portfolio grew to $7,450. RATE finds the monthly return rate (~0.704%), and multiplying by 12 gives 8.44% annualized return—reflecting both your deposits and market growth.

Common errors

Which RATE error are you seeing?

Error	Why it happens	How to fix it
#NUM!	RATE cannot find a solution because the payment direction conflicts with the loan/investment direction, or the parameters are mathematically inconsistent (e.g., paying off a loan faster than the payments allow).	Check that pmt and pv have opposite signs (e.g., pv = 18000, pmt = -350). If they match, RATE has no solution. Also verify nper is reasonable for the scenario.
#VALUE!	One or more arguments is non-numeric (text, empty cell, or invalid data type instead of a number).	Ensure all six arguments are numbers: nper must be a count, pmt/pv/fv must be currency or numeric values, type must be 0 or 1, guess must be a decimal. Check for extra spaces, quotes, or formatting cells as text.
#DIV/0!	nper equals 0 or pmt is 0 and pv equals fv (making the calculation trivial or impossible).	Verify nper > 0 (at least 1 payment period). If pmt = 0, ensure pv ≠ fv, or use a different function like PV or FV that doesn't require regular payments.

Tips and when to use something else

RATE returns a decimal (e.g., 0.05 = 5%). Multiply by 100 and add a % symbol for display, or multiply by 12 to annualize a monthly rate.
If RATE returns #NUM!, try providing a guess (e.g., 0.05 for ~5% starting point) to help it converge: =RATE(60, -350, 18000, 0, 0, 0.05).
For irregular or one-time cash flows (not fixed monthly payments), use IRR or XIRR instead of RATE. RATE assumes every payment is identical.
When comparing loans or investments, always annualize first. A 0.8% monthly savings rate is ~9.6% annual, not 0.8%.

Related functions

PMTPMT calculates the constant periodic payment needed to repay a loan or investment over a fixed number of periods at a fixed interest rate.PVReturns the present value of an investment based on a series of regular payments and a constant interest rate.FVFV returns the future value of a series of regular cash flows at a constant interest rate, modeling compound growth over time.NPERNPER returns the number of payment periods needed to pay off a loan or reach an investment goal at a fixed interest rate.IRRReturns the internal rate of return for a series of regular periodic cash flows, measuring annualized investment profitability.

Frequently asked questions

How do I annualize a monthly interest rate from RATE?: Multiply the monthly rate by 12. For example, if =RATE(60, -350, 18000) returns 0.00354, multiply by 12 to get 0.0425 or 4.25% annual. This assumes simple annualization; for exact compounding, use (1 + monthly_rate)^12 - 1.
What's the difference between RATE and IRR?: RATE assumes fixed periodic payments, while IRR handles variable or irregular cash flows. Use RATE for loans and savings; use IRR for projects with mixed deposits/withdrawals or one-time cash flows.
Why does RATE keep returning #NUM! even though my numbers look correct?: RATE uses iteration and may not converge if the parameters are mathematically conflicting. Try adding a guess parameter closer to the expected rate (e.g., 0.1 for ~10%), or verify pmt and pv have opposite signs and nper is positive.
Can RATE account for payments at the start of the month instead of the end?: Yes, use the type parameter: set type=1 for beginning-of-period payments, type=0 (default) for end-of-period. Beginning-of-period payments will yield a slightly higher interest rate because money has more time to grow.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17