For a fixed-rate loan with equal payments at regular intervals, calculate the periodic rate with RATE and annualize it: =RATE(number_of_payments,-payment_amount,amount_financed)*payments_per_year. For example, =RATE(60,-250,12000)*12 estimates the nominal annualized rate for 60 monthly payments of $250 on $12,000 financed.
That result is not automatically the lender’s legally disclosed APR. Fees, balloon payments, irregular dates, variable rates, and product-specific finance-charge rules require a complete cash-flow model using IRR or XIRR.
What you need before calculating APR
- Amount financed or net proceeds actually received.
- Payment amount and number of payments.
- Payments per year and whether payments are at the beginning or end of each period.
- Origination fees, points, prepaid charges, mandatory services, and other required amounts.
- A balloon or other final payment, if applicable.
- Actual payment dates when periods are not evenly spaced.
APR, interest rate, and annual rate are different
The interest rate is the rate charged on the outstanding balance. A nominal annual rate multiplies a periodic rate by the number of periods per year. An effective annual rate compounds those periods, for example =(1+monthly_rate)^12-1.
APR is a yearly measure of the cost of credit that relates the amount and timing of value received to the timing and amount of payments. In U.S. closed-end consumer credit, Regulation Z provides the controlling definitions and methods, including rules for finance charges and timing: 12 CFR 1026.22 and Appendix J. Fees can therefore make APR higher than the stated interest rate.
#1 Best Overall
Calculate a regular-payment loan with RATE
Set up the worksheet like this:
| Cell | Input | Example |
|---|---|---|
| B2 | Amount financed | $12,000 |
| B3 | Term in years | 5 |
| B4 | Payments per year | 12 |
| B5 | Payment amount | $250 |
| B6 | Future value or balloon balance | 0 |
| B7 | Payment timing | 0 |
For end-of-month payments with no balloon, use:
=RATE(B3*B4,-B5,B2,0,0)*B4
The more complete version references the optional future value and timing cells:
=RATE(B3*B4,-B5,B2,-B6,B7)*B4
RATE(nper,pmt,pv,[fv],[type],[guess]) returns the rate per payment period. nper is the total number of periods, pmt is the periodic payment, pv is the amount received, fv is the final balance, and type is 0 for payment at the end of a period or 1 for payment at the beginning. Microsoft documents the function and its iterative convergence behavior at RATE function.
Use opposite cash-flow signs
Enter money received by the borrower as positive and payments made by the borrower as negative. If pv and pmt have the same sign, Excel can return a negative rate or an error.
Match the frequency
| Payment schedule | Formula pattern |
|---|---|
| Monthly | =RATE(years*12,-monthly_payment,principal)*12 |
| Quarterly | =RATE(years*4,-quarterly_payment,principal)*4 |
| Weekly | =RATE(years*52,-weekly_payment,principal)*52 |
| Annual | =RATE(years,-annual_payment,principal) |
The multiplication produces a nominal annualization. The effective annual rate is instead =(1+periodic_rate)^payments_per_year-1; label the two outputs separately. Microsoft’s loan guidance explains using matching periods and annual rates at Using Excel formulas to figure out payments and savings.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteIf RATE returns #NUM!
RATE solves iteratively. Check the signs, number of periods, payment, and balance first. A reasonable initial guess can help:
=RATE(60,-250,12000,0,0,0.01)*12
Different guesses should not be used to hide multiple mathematical solutions; that signals a cash-flow modeling problem.
Include fees with IRR
RATE does not discover fees. If a $10,000 loan withholds a $300 origination fee, the borrower receives $9,700, not $10,000. Create a regular-period cash-flow column:
| Period | Cash flow |
|---|---|
| 0 | 9,700 |
| 1 | -323.07 |
| 2 | -323.07 |
| … | -323.07 |
| 36 | -323.07 |
With the advance in B2 and all 36 monthly payments through B38, calculate the monthly internal rate and nominal annualization with:
=IRR(B2:B38)*12
IRR finds the periodic rate that makes the net present value of regularly spaced cash flows equal to zero. It requires at least one positive and one negative value and assumes equal intervals; see Microsoft’s IRR function documentation.
=(1+IRR(B2:B38))^12-1 is the effective annual rate, not the same nominal annualization as IRR(B2:B38)*12.
Use XIRR for actual or irregular dates
Use XIRR when the first payment is 45 days after closing, dates shift for weekends or holidays, the first or last period is short or long, or payments otherwise do not occur at equal intervals.
| Date | Cash flow |
|---|---|
| January 10, 2026 | 9,700 |
| February 24, 2026 | -323.07 |
| March 24, 2026 | -323.07 |
| … | -323.07 |
If dates are in A2:A38 and values in B2:B38:
=XIRR(B2:B38,A2:A38)
The ranges must contain the same number of valid dates and cash flows, with receipts positive and payments negative. Microsoft states that XIRR handles nonperiodic schedules and discounts using a 365-day year; see XIRR function. It is an annualized IRR for your supplied data, not automatically the legal APR.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Rank #4
- Used Book in Good Condition
Balloon payments and other charges
Balloon payment
With a fixed periodic payment and a final balloon, include the balloon as a future value:
=RATE(total_periods,-payment,amount_financed,-balloon,0)*payments_per_year
In a cash-flow model, add the balloon to the final payment’s negative cash flow. Omitting it understates the borrowing cost.
Classify charges carefully
For a practical estimate, begin with the amount actually received, enter every required repayment, and include required upfront or periodic charges. Optional services, taxes, insurance, late charges, penalties, and other items may receive different treatment. Whether a charge is a finance charge depends on the product and applicable law, so do not assume every fee belongs in APR. Regulation Z’s APR provisions and Appendix J control covered U.S. transactions.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Build an amortization schedule
An amortization table lets you reconcile the payment and ending balance. For a fixed-rate annuity, use columns for period, date, beginning balance, payment, interest, principal, and ending balance.
| Column | Example formula |
|---|---|
| Period | 1, 2, 3 … |
| Date | Actual due date |
| Beginning balance | Prior ending balance |
| Payment | =PMT(periodic_rate,total_periods,-principal) |
| Interest | =IPMT(periodic_rate,period,total_periods,-principal) |
| Principal | =PPMT(periodic_rate,period,total_periods,-principal) |
| Ending balance | Beginning balance minus principal |
PMT calculates principal and interest but excludes taxes, reserves, and fees unless you model them separately, as Microsoft notes at PMT. PPMT calculates the principal portion of a specified payment; see PPMT. Keep full precision in intermediate calculations and round only displayed values unless reproducing the lender’s exact schedule.
Check the result with NPV
For regular monthly cash flows, discount future payments at the calculated monthly rate:
=NPV(periodic_rate,B3:B38)+B2
The result should be approximately zero, subject to rounding. If your cash flows use actual dates, do not treat this monthly-period check as equivalent to a dated regulatory calculation; validate the dated model independently.
Recommended Free Tools
Why Excel may differ from the lender’s APR
- The lender may apply Regulation Z’s product-specific finance-charge, actuarial, U.S. Rule, timing, and tolerance rules.
- Fees may be classified differently or paid separately from the advance.
- Actual dates, day-count conventions, payment timing, and rounding may differ.
- A variable-rate loan changes rates or payments by period and does not fit a single
RATEassumption. - Credit cards and other open-end accounts use rules such as daily periodic rates, average daily balances, grace periods, multiple balance categories, promotional rates, and minimum payments. See 12 CFR 1026.14.
For these reasons, describe a spreadsheet output as a calculated or estimated APR unless its inputs and method exactly reproduce the applicable legal standard.
Troubleshooting
| Problem | Likely cause | Fix |
|---|---|---|
#NUM! |
RATE did not converge |
Check inputs and signs; try a reasonable guess. |
| Negative rate | Cash-flow signs reversed | Make receipts positive and payments negative. |
| APR too low | Fees or balloon omitted | Use net proceeds and include every required cash flow. |
| APR differs from lender | Different dates, precision, or fee rules | Reproduce the lender’s schedule and assumptions. |
XIRR error |
Dates and values do not align or dates are invalid | Use equal-sized ranges containing valid dates. |
| Payment differs from lender | Taxes, insurance, reserves, or fees are outside principal and interest | Separate those items from the PMT calculation. |
Quick formula reference
=RATE(nper,-pmt,pv)*payments_per_year=RATE(nper,-pmt,pv,-fv,type)*payments_per_year=IRR(cash_flows)*payments_per_year=XIRR(cash_flows,dates)=(1+periodic_rate)^payments_per_year-1
Frequently Asked Questions
Can I use RATE for a variable-rate loan?
No. RATE assumes a constant periodic rate and payment structure. Model each period’s changing rate and payment with cash flows instead.
Is XIRR the lender’s legal APR?
Not necessarily. XIRR annualizes the cash flows and dates you provide; legal APR may use product-specific finance-charge, timing, day-count, and tolerance rules.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Free tools Windows power users keep installed
One-click scans. No signup required.
