What is the RATE Function?
The RATE function is a Financial functions,FunctionsThe RATE function is categorized under Financial functions. The function will calculate the interest rate per period of a year. So, it is the interest rate required to that will calculate the interest rate required to pay off a loan or reach a specified amount on an investment for a given period.
For a financial analystFinancial Analyst Job DescriptionSee a real Financial Analyst Job Description including all the skills, experience, and education required to be the successful candidate for the job. Perform financial forecasting, reporting, and operational metrics tracking, analyze financial data, create financial models, the RATE function can be useful to calculate the interest rate on zero coupon bonds.
Formula
=RATE(nper, pmt, pv, [fv], [type], [guess])
The RATE function uses the following arguments:
- Nper (required argument) – It is the number of periods over which the loan or investment is to be paid.
- Pmt (required argument) – It is the payment made for each period. It cannot be changed over its life. PMT includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.
- PV (required argument) – It is the present value that is the total amount that a series of future payments is worth now.
- FV (optional argument) – It is the future value or a cash balance that we want to attain after the last payment is made. If we omit fv, it is assumed to be 0 (the future value of a loan, for example, is 0). If fv is omitted, we must include the pmt argument.
- Type (optional argument) – The number 0 or 1 and indicates when payments are due. If type is set to 0 or omitted, payments are due at the end of the period. If set to 1, payments are due at the start of the period.
- Guess (optional argument) – We guess what the rate would be.
- When omitted, it takes 10%.
- If RATE does not converge, we need to try different values for guess. The function usually converges if guess is between 0 and 1.
How to use the RATE Function in Excel?
As a worksheet function, the RATE function can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us consider an example:
Example – Using the RATE function
Suppose we are given the following data:
We can find out the rate using the RATE function. We will use the formula below:
We get the result below:
Few things to remember about the RATE Function:
- #NUM! error – If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value. It might be because we failed to use the cash flow convention of negative numbers to represent outgoing payments and positive numbers to represent incoming payments. Alternatively, we may need to provide an initial ‘guess’ to the function.
- #VALUE! error – Occurs if any of the given arguments are non-numeric.
- When calculating monthly or quarterly payments, we need to convert the interest rate or the number of periods to months or quarters. For this, we need to ensure that the nper argument is expressed in the correct units, i.e., months = 12 * years, quarters = 4 * years.
Additional resources
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
- Advanced Excel Class
- Top Excel Formulas to KnowAdvanced Excel Formulas Must KnowThese advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Advanced Excel functions you must know. Learn the top 10 Excel formulas every world-class financial analyst uses on a regular basis. These skills will improve your spreadsheet work in any career
- Types of Graphs in ExcelTypes of GraphsTop 10 types of graphs for data presentation you must use - examples, tips, formatting, how to use these different graphs for effective communication and in presentations. Download the Excel template with bar chart, line chart, pie chart, histogram, waterfall, scatterplot, combo graph (bar and line), gauge chart,
- Certified Financial AnalystCertified Financial AnalystCFI Financial Modeling & Valuation Analyst program is your path to become a certified financial analyst. With 12 required courses on topics ranging from accounting and finance fundamentals to financial modeling, valuation and advanced Excel skills, the CFI financial analyst certification will help