RATE Function
Calculates the interest rate per period of a loan or investment.
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| nper | Total number of payment periods. | Required |
| pmt | Payment made each period. | Required |
| pv | Present value or loan principal. | Required |
| fv | Future value (default 0). | Optional |
| type | 0 = end of period (default), 1 = beginning of period. | Optional |
| guess | Initial guess for the rate (default 10%). | Optional |
Basic Example
Interest rate on a loan
=RATE(60, -400, 20000)A $20,000 loan paid back with $400 monthly payments for 5 years has a monthly interest rate of about 0.63%.
Advanced Examples
Example 1: Annual interest rate
Loan comparisonConvert the monthly rate to an annual rate
=RATE(60, -400, 20000) * 12How RATE Works
RATE uses an iterative method to find the interest rate that makes the net present value of the cash flows equal to zero. If the formula fails to converge, try a different guess.
Important Notes & Limitations
Requires consistent period units.
May fail to converge for unusual cash-flow patterns; use a guess value to help.
Returns the periodic rate, not the annual rate.
Common Errors & Fixes
#NUM! errorThe formula cannot converge to a solution.Fix: Add a reasonable guess, e.g. =RATE(60, -400, 20000, 0, 0, 0.05).
Annual rate too lowResult is monthly but was interpreted as annual.Fix: Multiply by the number of periods per year.
Download Practice File
RATE
ratePractice RATE with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the RATE function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps
Related Tutorials
Calculate Loan Interest
Calculate loan interest in Excel using IPMT, PPMT, and PMT. Learn how to split every payment into interest and principal, and find the true total cost of a loan.
Compound Interest Calculator
Use a compound interest calculator to project any balance in Excel or Google Sheets. Build it with FV, RATE, NPER and PV, then try the live calculator.
FV Function: Future Value in Excel
The FV future value function projects what a lump sum or a series of regular deposits will grow into. Learn the syntax, the sign rule and worked examples.
Mortgage Calculator
Build a working mortgage calculator in Excel or Google Sheets using PMT, IPMT, and PPMT. Estimate monthly payments, interest, and principal for any fixed-rate loan.
PMT Function Loan Payment
Master the PMT function loan payment formula in Excel and Google Sheets: calculate monthly mortgage or car payments and split interest from principal.
PV Function: Present Value of a Future Sum or a Payment Stream
PV present value discounts a future lump sum or a stream of payments back to today, plus the sign rule, four traps and the Excel-to-Sheets differences.
Simple Interest Calculator
How to build a simple interest calculator in Excel or Google Sheets: the I = P x r x t formula, the IPMT trap, and a live calculator you can try right away.