How to Use RATE Function in Excel (Find Hidden Interest)
Learn how to use RATE function in Excel to find out the real interest rate the bank is charging you. Very simple and easy!
Hello friends! Today I show you RATE() function in Excel.
What is RATE? Sometimes a shop or bank tell you: "Buy now, pay $100 every month!" but they don't tell you the interest rate! RATE function will help you find that hidden interest rate like a detective! Very easy!
🛠️ How to write RATE Function
To use RATE, you just need to know 3 things: how many months you pay, how much you pay per month, and the original price of the item.
Formula Syntax:
=RATE(nper, pmt, pv)
nper: Total number of months you will pay. (Example: 2 years = 24 months).pmt: The money you pay every month. (Must be negative-number!).pv: Present Value. The original price or loan amount you borrow right now.
📊 Short & Clear Example
Imagine you want to buy a motorbike. The price is $2,500 (This is PV). The shop says: "Just pay $120 every month for 24 months!" What is the interest rate? Let's check!
| What it is | Cell | Value | Note |
|---|---|---|---|
| Total Months (nper) | B1 | 24 | 2 years |
| Pay Every Month (pmt) | B2 | -120 | Minus because money leave your pocket |
| Motorbike Price (pv) | B3 | 2500 | Total price right now |
Formula to find Interest Rate:
=RATE(B1, B2, B3) * 12
- Result:
13.91% - Why multiply by 12 at the end? Because
RATEonly gives you the monthly interest rate. You must multiply by 12 to see the true yearly interest rate! Now you know the shop charge you almost 14% a year!
Check out my Advance Loan Calculator Tool to calculate loan payment easily!
💡 Easy Pro Tips
- Don't Forget Minus: Your monthly payment (
pmt) MUST have minus sign (-). If no minus sign, the formula will show#NUM!error because it is confused! - Format as Percentage: If your result show
0.139, don't panic! Just go to Home tab and click%button to format as Percentage. - Use with PMT and NPER:
RATEis the brother ofPMTandNPER. If you know any 3 pieces of a loan, Excel can always find the 4th piece for you!
📚 Useful Resources
Related Tutorials
Opens in new tab ↗How to Use IPMT Function in Excel (Find Interest Payment)
Learn how to use IPMT function in Excel to see exactly how much interest you pay the bank every month! Simple and easy guide.
How to Use PV Function in Excel (Find Present Value)
Learn how to use PV function in Excel to find out how much a loan is worth right now. Very simple and easy examples.
How to Use PMT Function in Excel (Calculate Loan Easy)
Learn how to use PMT function to calculate monthly payment for car or house loan. Very simple and short examples.