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