Financialintermediate
Calculate a fixed loan payment with PMT
=PMT(B1/12,B2*12,-B3)
Excel how-to guide
How to rate interest rate in Excel
Reverse-engineer the interest rate from a quoted monthly payment and loan amount, or compare the implied rate of two financing offers.
Step-by-step instructions
How to rate interest rate — 4 steps
1
Enter the total number of payment periods — for monthly payments over B1 years, use B1*12.
2
Enter the periodic payment as a negative number — it is a cash outflow.
3
Enter the loan principal (present value) as a positive number.
4
Multiply the result by 12 to convert from monthly to annual rate.
Example data
Worked example
| Parameter | Value |
|---|---|
| Loan Term (years) | 5 |
| Monthly Payment | 350 |
| Loan Amount | 18000 |
| Annual Rate | =RATE(5*12,-350,18000)*12 |
Common mistakes
Errors to watch out for
RATE returns
RATE uses iterative calculation and may fail to converge. Try adding a guess as the fifth argument — =RATE(nper,pmt,pv,,0.1) — to help it find the solution.
Result is a monthly rate, not annual
RATE returns the rate per period. Multiply by 12 for monthly cash flows to get the annual rate, or use (1+RATE(...))^12-1 for the exact effective annual rate.
FAQ
Frequently asked questions
How is RATE different from IRR?
RATE is designed for equal periodic payments (annuities). IRR handles irregular cash flows of different amounts. Use RATE for standard loans, IRR for project cash flows.
Describe your exact spreadsheet problem — get the formula in seconds.
Open AI Formula Generator — FreeRelated how-to guides
Continue learning.
Financialadvanced
Calculate net present value with NPV
=NPV(B1,B3:B7)+B2