Spreadsheet Practice

How to calculate the interest rate on a loan in Excel

Formula (Excel and Google Sheets)

=RATE(B4,-B3,B2)*12

Use RATE with the number of payments, the payment and the amount, then multiply by 12: =RATE(B4,-B3,B2)*12.

Result on the example
6.83% in B5
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B5. Change any input and the result updates, or edit the formula to experiment.

How it works

RATE searches for the interest rate that makes 60 payments of $562 pay off $28,500 exactly. Because the payments are monthly, the answer is a monthly rate, and multiplying by 12 gives the annual rate a lender would quote. As with PMT, the payment is negative and the amount borrowed positive.

=RATE(B4,-B3,B2)*12

Result0.06826191772

  1. B460

    The value in B4.

  2. B3$562

    The value in B3.

  3. -B3-562

    Makes the value negative.

  4. B2$28,500

    The value in B2.

  5. RATE(B4,-B3,B2)0.005688493143

    RATE: Interest rate per period.

    • nperB460
    • pmt-B3-562
    • pvB2$28,500
  6. RATE(B4,-B3,B2)*120.06826191772

    The * operator multiplies.

In Google Sheets

The same formula works in Google Sheets: =RATE(B4,-B3,B2)*12. We ran it in Google Sheets mode on the same data and it returns 6.83%.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=(1+RATE(B4,-B3,B2))^12-17.04%Different resultThe effective annual rate, which includes monthly compounding. It is a little higher than the quoted rate. Effective rate: 7.04%.

Common mistakes

  • Forgetting the *12 shows a monthly rate of about 0.57%, which looks deceptively cheap.
  • Same signs on the payment and the amount make RATE return #NUM!.
  • If RATE can't find an answer it returns #NUM!; give it a starting guess as the sixth argument, such as 0.01.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate the interest rate on a loan

In B5, work out the annual interest rate of a loan of B2 repaid with B4 monthly payments of B3.

Questions

How do I include upfront fees?

Borrow less in the formula: =RATE(B4,-B3,B2-B6)*12 with the fees in B6. The higher rate is closer to the APR.

Can RATE find the return on an investment?

Yes: =RATE(years,0,-start,end) gives the yearly growth rate between two values.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.