The RATE function in Excel is used to calculate the interest rate per payment period for a loan or investment. It is useful when you know the loan amount, regular payment, and repayment period but want to find the interest rate.
In this tutorial, you will learn the RATE function from the basics, understand its arguments, and calculate interest rates using practical financial examples.
What Is the RATE Function in Excel?
The RATE function calculates the interest rate for each payment period.
For example, suppose you borrow ₹5,00,000 and agree to pay ₹11,122.22 every month for 5 years. You know the loan amount, monthly payment, and total number of payments, but you do not know the interest rate.
The RATE function can estimate the monthly interest rate based on these values.
Important: RATE returns the interest rate per payment period, not automatically the annual interest rate.
If your payments are monthly, RATE returns a monthly rate. To calculate a nominal annual rate, multiply the monthly rate by 12.
RATE Function Syntax
=RATE(nper, pmt, pv, [fv], [type], [guess])
Understanding the Arguments
| Argument | Meaning |
|---|---|
nper | Total number of payment periods. |
pmt | Regular payment made or received in each period. |
pv | Present value, usually the loan or initial investment amount. |
[fv] | Optional future value after the final payment. Default is 0. |
[type] | Optional payment timing: 0 = end of period, 1 = beginning of period. Default is 0. |
[guess] | Optional estimated interest rate used by Excel to start its calculation. Default is 10%. |
1. nper – Total Number of Periods
This represents the total number of payments.
For example, if a loan is repaid monthly over 5 years:
=5*12
The total number of payments is 60.
2. pmt – Regular Payment
This represents the fixed payment made or received during each period.
For example, if you pay ₹11,000 every month, the payment amount is entered as a negative value when the loan amount is positive.
3. pv – Present Value
This is the current value of the loan or investment.
For example, if you borrow ₹5,00,000, the present value is ₹5,00,000.
4. fv – Future Value
This is an optional argument. It represents the amount remaining after the final payment.
For a fully repaid loan, the future value is normally 0.
5. type – Payment Timing
This argument specifies when payments happen.
| Type | Meaning |
|---|---|
| 0 | Payment at the end of each period |
| 1 | Payment at the beginning of each period |
If omitted, Excel assumes 0.
6. guess – Estimated Interest Rate
This is an optional starting estimate for Excel’s calculation.
Excel uses an iterative calculation to find the interest rate. If it cannot find a result, trying a different guess may help.
For most ordinary loan calculations, you can leave this argument blank.
Important Concept: RATE Function Returns a Periodic Interest Rate
This is one of the most important concepts to understand.
Suppose RATE calculates an interest rate of 1% per month.
That does not mean the annual interest rate is 1%.
The nominal annual interest rate is:
=1%*12
Result: 12% per year.
However, if interest is compounded monthly, the effective annual rate is higher:
[
(1+1%)^{12}-1
]
Result: approximately 12.68%.
Remember:
- Monthly RATE result = monthly interest rate.
- Nominal annual rate = monthly rate × 12.
- Effective annual rate = compounded annual return.
Understanding Positive and Negative Values in RATE Function
Excel follows a cash-flow sign convention.
For a normal loan:
- Loan amount received is positive.
- Loan installments paid are negative.
For example, if you borrow ₹5,00,000 and pay ₹11,122.22 every month, enter the loan amount as positive and the monthly payment as negative.
Using the correct cash-flow signs helps Excel calculate the interest rate correctly.
Practical Examples of RATE Function in Excel
Question 1: Calculate the Monthly Interest Rate of a Loan
Problem Statement
A person borrows ₹5,00,000 and agrees to pay ₹11,122.22 every month for 5 years. Calculate the monthly interest rate.
Given Data
| Particulars | Value |
|---|---|
| Loan Amount | ₹5,00,000 |
| Monthly Payment | ₹11,122.22 |
| Loan Tenure | 5 Years |
| Payment Frequency | Monthly |
Excel Formula
=RATE(5*12,-11122.22,500000)
Expected Result
Approximately 1% per month.
Explanation
5*12calculates 60 monthly payments.-11122.22represents the monthly payment going out.500000represents the loan amount received.
Excel estimates the monthly interest rate that makes these payments equivalent to the loan amount.
Concept Covered
RATE Function can calculate the interest rate when the loan amount, payment amount, and total number of payments are known.
Question 2: Calculate the Annual Interest Rate of a Car Loan
Problem Statement
A customer takes a car loan of ₹8,00,000 and pays approximately ₹19,908.45 every month for 4 years. Calculate the nominal annual interest rate.
Given Data
| Particulars | Value |
|---|---|
| Loan Amount | ₹8,00,000 |
| Monthly Payment | ₹19,908.45 |
| Loan Tenure | 4 Years |
| Payment Frequency | Monthly |
Excel Formula
=RATE(4*12,-19908.45,800000)*12
Expected Result
Approximately 9% per year.
Explanation
4*12calculates 48 monthly payments.-19908.45represents the monthly installment.800000represents the loan amount.- Multiplying the RATE result by 12 converts the monthly rate into a nominal annual rate.
Concept Covered
When payments are monthly, RATE returns a monthly interest rate. Multiply it by 12 to calculate the nominal annual interest rate.
Question 3: Calculate the Interest Rate of an Investment
Problem Statement
A person invests ₹1,00,000 today and receives ₹1,21,000 after 2 years. There are no regular payments. Calculate the annual interest rate.
Given Data
| Particulars | Value |
|---|---|
| Initial Investment | ₹1,00,000 |
| Future Value | ₹1,21,000 |
| Investment Period | 2 Years |
| Regular Payment | ₹0 |
Excel Formula
=RATE(2,0,-100000,121000)
Expected Result
10% per year.
Explanation
2represents two annual periods.0means there are no regular payments.-100000represents the initial investment.121000represents the future amount received.
Excel calculates the annual rate that grows ₹1,00,000 into ₹1,21,000 over two years.
Concept Covered
RATE can calculate the interest rate for a single initial investment even when there are no regular payments.
Question 4: Calculate the Quarterly Interest Rate of a Business Loan
Problem Statement
A business borrows ₹10,00,000 and pays approximately ₹97,342.86 every quarter for 3 years. Calculate the quarterly interest rate.
Given Data
| Particulars | Value |
|---|---|
| Loan Amount | ₹10,00,000 |
| Quarterly Payment | ₹97,342.86 |
| Loan Tenure | 3 Years |
| Payment Frequency | Quarterly |
Excel Formula
=RATE(3*4,-97342.86,1000000)
Expected Result
Approximately 2.5% per quarter.
Explanation
3*4calculates 12 quarterly payments.-97342.86represents each quarterly payment.1000000represents the amount borrowed.
Excel calculates the interest rate for each quarter.
Concept Covered
For quarterly payments, RATE Function returns the quarterly interest rate. Do not treat it as the annual rate.
Question 5: Calculate the Annual Interest Rate for Monthly Savings
Problem Statement
A person deposits ₹5,000 at the end of every month for 5 years. The final savings amount is approximately ₹4,08,348.45. Calculate the nominal annual interest rate.
Given Data
| Particulars | Value |
|---|---|
| Monthly Deposit | ₹5,000 |
| Final Savings | ₹4,08,348.45 |
| Saving Period | 5 Years |
| Payment Frequency | Monthly |
Excel Formula
=RATE(5*12,-5000,0,408348.45)*12
Expected Result
Approximately 12% per year.
Explanation
5*12calculates 60 monthly deposits.-5000represents each monthly deposit.0means there is no initial investment.408348.45represents the final savings amount.*12converts the monthly rate into a nominal annual rate.
Concept Covered
RATE can also calculate the interest rate earned on regular savings when the final amount is known.
Question 6: Calculate Interest Rate for Payments at the Beginning of Each Month
Problem Statement
A person borrows ₹2,00,000 and pays approximately ₹6,571.02 at the beginning of every month for 3 years. Calculate the monthly interest rate.
Given Data
| Particulars | Value |
|---|---|
| Loan Amount | ₹2,00,000 |
| Monthly Payment | ₹6,571.02 |
| Loan Tenure | 3 Years |
| Payment Timing | Beginning of Month |
Excel Formula
=RATE(3*12,-6571.02,200000,0,1)
Expected Result
Approximately 1% per month.
Explanation
3*12calculates 36 monthly payments.-6571.02represents the monthly payment.200000represents the loan amount.0means there is no remaining balance.1indicates payments happen at the beginning of each month.
Concept Covered
The type argument affects the interest rate calculation because payment timing changes how long each payment remains invested or outstanding.
Question 7: Calculate Interest Rate Using Cell References
Problem Statement
A finance department stores loan details in Excel. Calculate the monthly and nominal annual interest rates using cell references.
Given Data
| Cell | Particulars | Value |
|---|---|---|
| B2 | Loan Amount | ₹6,00,000 |
| B3 | Monthly Payment | ₹13,316.67 |
| B4 | Loan Tenure (Years) | 5 |
Excel Formula – Monthly Interest Rate
=RATE(B4*12,-B3,B2)
Expected Result
Approximately 1% per month.
Excel Formula – Nominal Annual Interest Rate
=RATE(B4*12,-B3,B2)*12
Expected Result
Approximately 12% per year.
Explanation
B4*12calculates the total monthly payments.-B3represents the monthly payment.B2represents the loan amount.- Multiplying the result by 12 converts the monthly rate into a nominal annual rate.
Concept Covered
Cell references make financial calculations flexible. You can change the loan amount, payment, or tenure without rewriting the formula.
Student Practice Tasks
Try calculating the interest rates yourself using the RATE function.
- A person borrows ₹3,00,000 and pays ₹9,846.27 every month for 3 years. Calculate the monthly interest rate.
- A customer takes a car loan of ₹6,00,000 and pays ₹12,749.68 monthly for 5 years. Calculate the nominal annual interest rate.
- A person invests ₹50,000 today and receives ₹60,500 after 2 years. Calculate the annual interest rate.
- A business borrows ₹8,00,000 and pays ₹78,286.10 every quarter for 3 years. Calculate the quarterly interest rate.
- A person deposits ₹2,000 every month for 3 years and receives approximately ₹83,515. Calculate the nominal annual interest rate.
Final Student Challenge: Build an Interest Rate Calculator
A finance department wants to calculate the interest rate of a loan when the loan amount, monthly payment, and tenure are known.
Given Data
| Particulars | Value |
|---|---|
| Loan Amount | ₹5,00,000 |
| Monthly Payment | ₹11,122.22 |
| Loan Tenure | 5 Years |
Your Task
- Enter the loan amount in B2.
- Enter the monthly payment in B3.
- Enter the loan tenure in B4.
- Calculate the monthly interest rate.
- Calculate the nominal annual interest rate.
Excel Formula – Monthly Interest Rate
=RATE(B4*12,-B3,B2)
Excel Formula – Nominal Annual Interest Rate
=RATE(B4*12,-B3,B2)*12
Expected Result
- Monthly interest rate: Approximately 1%
- Nominal annual interest rate: Approximately 12%
Format the result cells as Percentage so the interest rates are easy to read.
Key Takeaways
- RATE calculates the interest rate per payment period.
nperrepresents the total number of payment periods.pmtrepresents the regular payment.pvrepresents the present value or loan amount.fvrepresents the amount remaining after the final payment.typecontrols payment timing.guessprovides an optional starting estimate for the calculation.- For monthly payments, RATE returns a monthly rate.
- Multiply a monthly RATE result by 12 to calculate the nominal annual rate.
- Use the correct positive and negative cash-flow signs.
Frequently Asked Questions
1. What is the RATE function in Excel?
The RATE function calculates the interest rate per payment period for a loan or investment based on regular payments and other financial details.
2. What is the syntax of the RATE function?
=RATE(nper,pmt,pv,[fv],[type],[guess])
3. Does RATE function in Execl return a monthly or annual interest rate?
It returns the interest rate per payment period. If payments are monthly, it returns a monthly rate. If payments are yearly, it returns an annual rate.
4. How can I calculate the annual interest rate using RATE?
For monthly payments, multiply the RATE result by 12 to calculate the nominal annual interest rate.
5. Why does RATE function require negative payment values?
Excel follows a cash-flow sign convention. For a normal loan, the amount received is positive and the installments paid are negative.
6. What is the purpose of the guess argument?
The guess argument gives Excel an estimated starting interest rate for its iterative calculation. If omitted, Excel uses 10%.
7. What happens if RATE function cannot calculate the interest rate?
RATE may return #NUM! if it cannot find a result within its calculation process. You can try a different guess value and check whether the input values and cash-flow signs are correct.
8. Can RATE function calculate the interest rate of a loan?
Yes. If you know the loan amount, regular payment, and total number of payments, RATE can estimate the interest rate.
9. Can RATE function calculate investment returns?
Yes. RATE can calculate the periodic rate for an investment when its initial value, regular payments, and final value are known.
10. What is the difference between RATE and PMT?
PMT calculates the regular payment amount when the interest rate is known. RATE calculates the interest rate when the payment amount and other financial details are known.
11. What is the difference between nominal and effective annual interest rates?
The nominal annual rate is the periodic rate multiplied by the number of periods in a year. The effective annual rate includes the effect of compounding.
12. Is RATE Function available in Microsoft 365?
Yes. RATE is a standard Excel financial function available in Microsoft 365 and commonly used desktop versions of Excel.

