EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Financial Functions in Excel Excel RATE Formula
Secondary Sidebar
Excel Functions
  • Financial Functions in Excel
    • PPMT Function in Excel
    • NPER Function in Excel
    • NPV Formula in Excel
    • MIRR Formula in Excel
    • Excel RATE Formula
    • Excel PV Formula
    • Excel PRICE Function
    • MIRR Excel Function
    • PV Excel Function
    • YIELD Excel Function
    • NPV Function in Excel
    • RATE Function in Excel
    • IRR Function in Excel
    • FV Function in Excel
    • PMT Function in Excel
    • XIRR In Excel
    • IPMT in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Excel for Finance Course Certification
  • Excel Data Analysis Training
  • Excel for Marketing Training

Excel RATE Formula

By Madhuri ThakurMadhuri Thakur

Rate Formula in Excel

Excel RATE Formula (Table of Contents)

  • RATE Formula in Excel
  • How to Use RATE formula in Excel?

RATE Formula in Excel

This RATE formula provides the interest rate of a period per annuity. RATE function repeatedly calculates to find the rate for that period. RATE function can be used to find an interest rate of a period and then can be multiplied to find the annual interest rate. So this formula can be used to derive the interest rate. The formula of this function is as follows.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,412 ratings)

RATE Formula Syntax

=RATE(nper, pmt, pv, [fv], [type], [guess])

Below is the explanation of the given arguments:

  • nper – No. of Payment periods for an annuity. (Required)
  • pmt – Payment to be made for each period; this will be fixed. (Required)
  • pv – Present Value, Total Amount that a series of future Payment worth now. (Required)
  • fv – Future Value, Goal amount to have after the subsequent Instalment; if we exclude fv, we can take 0 instead. (Optional)
  • type – This examines how the function will consider the payment due date, so we have to put this in binary 0 or 1; 0 means that payment due at period end. (Optional)
  • guess – This argument is used for our guess of the interest rate. It will provide the rate function with a start before reaching 20 iterations. There were two possibilities for this argument when it excluded is assumes our guess as 10%; otherwise, it will attempt other values for input(Optional).

How to Use RATE formula in Excel?

We will now learn how to write the RATE formula to get the lookup value. Let’s understand this formula with some examples.

You can download this RATE Formula Excel Template here – RATE Formula Excel Template

Example #1

  • For this example, suppose you want to buy a car, and the price of that car is INR 3,00,000.00. And you

went to a banker and asked for a car loan; you were said that you have to pay INR 10,000.00 per

month for 3 years (36 Months)

  • Here is the situation where we can use our RATE function to find the Interest rate for the given

condition.

  • Now, as per the below image, you can see that we have mentioned the details we have, Now initially

we will find the rate of interest per month and then for a year by multiplying it by 12.

Rate Formula Example 1-1

Now you can see from the below image you can see that we have applied the formula for RATE is as

follows:

=RATE(B5, -B3, B2)

Here the arguments :

nper = B5 (Total no. Of Instalments)

pmt= -B3 (Payment for each Instalment)

pv= B2 (Loan Amount)

Rate Formula Example 1-2

  • Here you can notice that the pmt (B3) is taken negative because the calculation is from the borrower’s

perspective, so this is the monthly installment amount which is the cash outflow for the individual

(Borrower) must to pay every month. So the borrower is losing this amount every month; that’s the

cause why it’s negative.

  • Now here if we calculate this loan from the bank’s perspective, as the bank is giving this loan on a monthly basis to the borrower, the (B2) loan amount will be in negative because on every Instalment the bank has to pay that less to the borrower monthly, so as per the perspective we have to change the negative sign in the formula.
  • Now you can see that by using this function, we have found the rate of interest monthly (In our case, it’s 1.02%).

Rate Formula Example 1-3

  • And by multiplying it by 12, we can have the rate of interest annually (In our case, it’s 12.25%).

Rate Formula Example 1-4

Example #2

  • Similar to the above example for the practice, we are going to see another example where we have to find the interest rate. As per the below image is the data we have to find the interest rate.

Rate Formula Example 2-1

  • Here we have 20 Lacs to pay with 50 thousand per month for 5 years (60 Instalments).
  • So the arguments are as follows:

nper = 20 Lacs

pmt = 50,000

pv = 60 Instalments (5 years)

Example 2-2

  • As per the above image, we can see that the RATE function has been applied and found the monthly rate of interest is 1.44%
  • Now by multiplying it by 12, we will get the annual rate of interest which is 27% for the given an example.

Example 2-3

Example #3

  • For this example, suppose we know the rate of interest and nos of installment, but we want to find out the amount of the monthly installment.
  • Here for this example, we have taken the following data as per the below image:

Example 3-1

  • We have taken a loan of 3 Lacs at 15% of interest rate (Annually), here we know the interest rate, but we need to find the pmt: Amount Payable for each Instalment.
  • Here as we can see from the image below, the formula for pmt is as follows :

=pmt(B4/12, B3, -B2)

  • We can notice a thing here that we have taken the B4/12 because the rate of interest provided to us is per annum (annually), so by dividing it with 12, we are making it monthly.

Example 3-2

  • So as we can see in the below image, the monthly installment amount will be $14,545.99, to be exact.

Example 3-3

  • Here as per the above example, we have found one of our argument for the RATE function.

Things to Remember about RATE Formula

#NUM! Error:

This error can occur when the result of the rate does not converge within 0.0000001 for 20 iterations. It can happen when we don’t mention cash flow convection by assigning the negative sign to the appropriate argument in the formula; as per the above example, we have seen that when we calculate the rate for the borrower side, the monthly installment amount getting negative sign because of monthly cash outflow for the borrower. So if we didn’t apply that negative sign to the monthly cash outflow, we would encounter this error.

  • So here we can put guess argument to work because it will allow us to start as it has to converge within 20 iterations and provide us with the closer or even the correct answer.
  • All arguments should be numeric; otherwise, it will encounter the #value error.
  • With the RATE function, when you’re calculating the interest rate for the given data, we will find the rates of interest per month. So to convert it to annual interest, we have to multiply it by 12.

Recommended Articles

This has been a guide to the RATE Formula in Excel. Here we discussed using the RATE Formula in Excel and practical examples, and a downloadable excel template. You can also go through our other suggested articles –

  1. RATE Function in Excel
  2. NPER in Excel
  3. Calculate Compound Interest in Excel
  4. Advanced Formulas in Excel
Popular Course in this category
Excel for Finance Training (18 Courses, 7+ Projects)
  18 Online Courses |  7 Hands-on Projects |  85+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
3 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download RATE Formula Excel Template

EDUCBA

Download RATE Formula Excel Template

EDUCBA

डाउनलोड RATE Formula Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more