Quick Navigation
RATE Function
Summary
The Excel RATE function calculates the interest rate per period for an annuity, making it essential for loan analysis, investment planning, and mortgage calculations. It uses iterative methods to solve for the rate when you know the payment amount, present value, and number of periods.
Syntax
RATE(nper, pmt, pv, [fv], [type], [guess])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| nper | Number |
Yes | Total number of payment periods in the annuity |
| pmt | Number |
Yes | Fixed payment amount made each period (typically negative for loans) |
| pv | Number |
Yes | Present value - current worth of the series of future payments (typically positive for loans) |
| fv | Number |
No | Future value or cash balance after final payment (defaults to 0) |
| type | Number |
No | Payment timing: 0=end of period, 1=beginning of period (defaults to 0) |
| guess | Number |
No | Initial rate estimate (defaults to 10%; adjust if function doesn't converge) |
Using the RATE Function
RATE is perfect for financial scenarios where you know the payment schedule and amounts but need to determine the interest rate. Common uses include calculating loan rates from monthly payments, determining investment yields, and mortgage analysis.
Common RATE Examples
Loan Monthly Rate Calculation
=RATE(A2*12,A3,A4)Calculates monthly interest rate for a 4-year loan with $200 monthly payments and $8,000 principal (result: 1%)
Annual Rate from Monthly Rate
=RATE(A2*12,A3,A4)*12Converts monthly rate to annual rate for same loan (result: 9.24%)
Investment Rate Calculation
=RATE(60,5000,-25000,25000)Finds monthly rate for investment with $5,000 payments over 5 years growing $25,000 to $25,000 future value
Frequently Asked Questions
Common Errors and Solutions
#NUM!
Cause: RATE couldn't converge after 20 iterations
Solution: Adjust guess parameter, ensure consistent time units, verify nper > 0
#VALUE!
Cause: Non-numeric arguments
Solution: Ensure all numeric inputs are valid numbers
Unexpected results
Cause: Inconsistent sign convention or time periods
Solution: Use negative pmt/positive pv for loans; match nper periods with guess rate units
Notes
- RATE uses iteration and stops when results converge within 0.0000001
- Default guess = 10%; try values between 0-1 if convergence fails
- Always match time units: monthly rate with monthly nper (years*12)
- For loans: pmt typically negative, pv positive
- See PV function documentation for complete parameter explanations
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in:
Content last reviewed: December 11, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+