The annual rate in Excel is most directly calculated with the RATE function, which returns the periodic interest rate; multiply that result by the number of periods per year to get the nominal annual rate. For a true annual percentage rate (APR) that includes fees, you combine RATE with the total amount financed and then multiply by 12. This page explains both approaches, shows the exact formulas, and works through a real example you can copy into your own spreadsheet.
Formula
Excel’s RATE function solves for the periodic interest rate that makes a series of equal payments equal to a present value. The syntax is:
=RATE(nper, pmt, pv, [fv], [type], [guess])
- nper – total number of payment periods (e.g., 60 for 5 years of monthly payments)
- pmt – the payment made each period (negative because it is money out)
- pv – the present value, i.e., the loan amount (positive because it is money in)
- fv – future value, usually 0 for a fully amortized loan (optional)
- type – 0 for end-of-period payments, 1 for beginning-of-period (optional)
- guess – your starting estimate for the rate (optional, defaults to 10%)
RATE returns the rate per period. To get the nominal annual rate, multiply by the number of periods per year. For monthly payments, that is =RATE(nper, pmt, pv) * 12. If you want the effective annual rate (EAR) that accounts for monthly compounding, use =(1 + RATE(nper, pmt, pv))^12 – 1.
Worked Example
You borrow $20,000 at an 8% APR for 5 years, with monthly payments. First, find the monthly payment using the standard formula:
Monthly rate r = 8% / 12 = 0.0066667
Number of months n = 5 × 12 = 60
Payment = 20000 × 0.0066667 / (1 – (1 + 0.0066667)^-60)
Calculate step by step:
(1 + 0.0066667)^-60 = 0.671216
1 – 0.671216 = 0.328784
20000 × 0.0066667 = 133.333
133.333 / 0.328784 = 405.53
So the monthly payment is $405.53 (rounded to cents). Now use RATE to back out the annual rate from that payment:
=RATE(60, -405.53, 20000) returns 0.0066667 (the monthly rate). Multiply by 12 to get 8.00% – the nominal annual rate. The effective annual rate is (1.0066667)^12 – 1 = 8.30%.
Assumptions
- Payments are made at the end of each month (standard for most loans).
- Interest compounds monthly at APR/12.
- The loan is fully amortized – level payments that pay off principal and interest by the end of the term.
- No fees, taxes, insurance, or other charges are included. If your loan has upfront fees, the effective APR will be higher than the nominal rate.
When to Use RATE vs. a Simple Division
Many people mistakenly divide the total interest by the loan amount and then by the number of years to get an “annual rate.” That only works for a simple-interest loan with no amortization – i.e., you pay interest on the full principal for the entire term and then repay the principal at the end. For an amortizing loan (like a mortgage or car loan), the average balance is roughly half the original amount, so that shortcut badly understates the true rate.
Use RATE whenever you have the payment amount and want to know the underlying interest rate. This happens when you are comparing loan offers with different fees or payment schedules, or when you want to reverse-engineer the rate from a quoted monthly payment.
How to Include Fees in the APR
The true APR includes origination fees, closing costs, or points. To calculate it, reduce the amount you actually receive (the loan amount minus fees) and use that as the present value in RATE, while keeping the same payment and term. For example, if you borrow $20,000 but pay a $500 fee, your net proceeds are $19,500. With the same $405.53 monthly payment for 60 months:
=RATE(60, -405.53, 19500) * 12 = 8.63%
That is the true APR – higher than the nominal 8% because you are effectively paying the same interest on less money. Excel’s built-in EFFECT function can also convert a nominal rate to an effective annual rate: =EFFECT(nominal_rate, npery) where npery is the number of compounding periods per year.
Common Mistakes and How to Avoid Them
- Forgetting to multiply by periods per year. RATE returns a monthly rate; you must multiply by 12 for an annual figure.
- Using the wrong sign for pmt or pv. In Excel, money you receive (loan amount) is positive, and money you pay (payment) is negative. If both are positive, RATE returns an error or a nonsensical result.
- Confusing nominal and effective rates. The nominal rate is the stated rate; the effective rate includes compounding. For monthly compounding, the effective rate is always higher.
- Using the payment amount before rounding. If you use an unrounded payment like 405.528, RATE will give a slightly different answer. Always use the rounded payment that the lender actually charges.
- Ignoring the guess argument for unusual loans. For very high or low rates, RATE may fail to converge. Provide a guess close to the expected rate, e.g., =RATE(60, -405.53, 20000, 0, 0, 0.1).
Alternatives to RATE
If you don’t have the payment amount but know the total interest paid, you can use the IPMT and PPMT functions to break down each payment, or use NOMINAL to convert an effective rate to a nominal one. For a simple interest loan (no amortization), the annual rate is simply total interest / (principal × years). For a discount loan where interest is deducted upfront, the effective rate is higher than the stated rate – use RATE with the net proceeds as the present value.
Excel also has a Data Table feature that lets you see how the rate changes with different payments or terms, which is useful for sensitivity analysis.
Frequently Asked Questions
Why does RATE return a monthly rate even though I want an annual one?
Because RATE is designed to work with any period. The nper and pmt arguments define the period, so the result is per that period. Multiply by 12 for a nominal annual rate, or use the compounding formula for an effective annual rate.
What if my loan has quarterly or semi-annual payments?
Adjust nper and pmt accordingly. For quarterly payments over 5 years, nper = 20 and pmt is the quarterly payment. Multiply RATE by 4 for the nominal annual rate. For effective annual rate, use (1 + RATE)^4 – 1.
Can I use RATE to find the rate on an investment with irregular cash flows?
No. RATE assumes level, periodic payments. For irregular cash flows, use the IRR function, which returns the internal rate of return for a series of cash flows at any dates. For a single lump sum that grows, use RRI (nper, pv, fv).
Why does RATE sometimes give a #NUM! error?
That happens when Excel cannot find a rate that satisfies the equation, often because the sign of pmt/pv is wrong, or because the payment is too low to cover the interest (e.g., a negative amortization loan). Check your signs and try a different guess.
Is the APR from RATE the same as the APR quoted by lenders?
Lenders’ APRs often include fees and use a specific calculation method defined by law. RATE gives the mathematical rate for the cash flows you enter. To match a lender’s APR, you must include all fees in the present value and use the exact payment schedule. The result is an estimate; the lender’s disclosure provides the exact figures.
Related
Formula used
in p | Loan amount | $ | 20000 | 1 | 100000000 in apr | Interest rate (APR) | % | 8 | 0.01 | 100 in yrs | Loan term | years | 5 | 0.0833 | 50 out pmt | Monthly payment | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2 out int | Total interest | $ | pmt * yrs * 12 - p | 2
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.
More in this section
- Debt Ratio Benchmarks: What Is a Good Debt Ratio?24.0 %
- Debt Ratio Calculation Formula & How to Use It25.00 %
- Car Loan Calculator: Estimate Your Monthly Payment$405.53
- Calculate My Payoff Date: Loan Payoff Calculator
- Student Loan Interest Deduction Limit: 2025 Rules$405.53
- Car Loan EMI Calculator Month Wise$405.53