MS Excel: RATE function to calculate periodic interest rate

The Excel RATE function answers that question by calculating the periodic interest rate for loans, annuities, or investments based on known payment terms. It's especially useful when other values (payment, term, future/present value) are known, but the rate is unknown.

This article explains how to use RATE effectively—from simple loans to more advanced financial models.

The RATE function calculates the periodic rate of return or interest rate required to pay off or accumulate a given amount of money over time, based on consistent payment intervals.

It solves for the rate in the time value of money equation, assuming equal payment amounts and regular periods.

Syntax

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

Parameter Breakdown:

Argument	Description
nper	Total number of payment periods
pmt	Payment made each period (use negative for outflow)
pv	Present value (amount borrowed or invested)
fv	(Optional) Future value (default is 0)
type	(Optional) Payment timing: 0 = end of period, 1 = beginning
guess	(Optional) Initial guess for rate (default is 10%)

The result is the rate per period. If you're working with monthly payments, the result is the monthly rate. Multiply by 12 for annualized rate.

Example 1: Loan Interest Rate Calculation

Scenario: You borrow $20,000 and agree to pay $377.42 per month for 5 years. What’s the monthly interest rate?

=RATE(60, -377.42, 20000)

Result: 0.005 → or 0.5% per monthMultiply by 12 to get 6% annual interest.

Example 2: Investment Yield with Future Value

Scenario: You invest $5,000 and expect to receive $6,000 after 3 years with no interim payments. What’s the annual yield?

=RATE(3, 0, -5000, 6000)

Result: 0.0622 → or 6.22% per year

Use Cases for RATE

Use Case	Description
Loan comparison	Determine actual interest rates from different lenders
Lease or rent analysis	Calculate implicit interest in leasing arrangements
Investment evaluation	Calculate required rate of return to reach a goal
Bond-like instruments	Reverse-engineer yield to maturity
Amortization models	Help complete missing financial inputs

Related Time Value Functions

Function	Purpose
PV	Present value of future cash flows
FV	Future value of investments or loans
PMT	Payment per period
NPER	Number of periods
IRR	Internal rate of return for uneven cash flows
YIELD	Yield for bonds with periodic interest

Common Pitfalls & Tips

Mistake	Solution
Getting 0 or #NUM! error	Try adjusting the guess value (e.g., 0.1 or 0.05)
Not accounting for period length	Always match rate to period (monthly, quarterly, etc.)
Wrong payment signs	Use negative for outflows, positive for inflows
Forgetting to annualize	Multiply by 12 (monthly) or 4 (quarterly) if needed

Summary Table

Feature	Value
Function Name	RATE
Calculates	Interest or return rate per period
Ideal For	Loans, annuities, investment returns
Output	Periodic rate (e.g., monthly or annual)
Inputs Needed	NPER, PMT, PV (plus optional FV, type)

Final Thoughts

The RATE function is one of Excel’s most powerful and underused financial tools. It helps you uncover the true cost of borrowing or the real return on investment—both critical insights for sound financial decisions.

Used alongside functions like PMT, PV, and NPER, it forms a comprehensive toolkit for loan analysis, retirement planning, and corporate finance modeling.

Wrap your RATE formula inside a multiplication to convert it easily:

=RATE(...) * 12 ← for annual rate if periods are monthly