How to use the IRR function in Excel

In this article, we will learn How to use the IRR function in Excel.

What is the interest rate and how to calculate interest rate for a loan amount

You are already aware about the present value and future value of an amount. For example a loan of $10,000 is to be paid on the monthly payment of $190. This change occurs on the periodic payment and a specific percentage. This specific percentage is called the interest rate (nominal rate). Higher the rate, higher will the future value of the amount. Lend money on more interest rate and borrow money on less interest. In the above case, we considered the present value (whole amount) for 1 time. But what if the payment is done every month in place of the whole amount at 1 time, in this case if payments are made on equal or regular intervals then we use the IRR function in Excel. Use the XIRR function when the payment is done on irregular intervals.

IRR function Excel

IRR function returns the rate of interest of the Cash flow. It takes the argument as an array of Cash flow at regular intervals unlike XIRR function.

IRR function Syntax:

=IRR(values, [guess])

values : Array of Cash flow at regular intervals

[guess] : [optional] Expected IRR. Default is .1 (10%)

Example :

All of these might be confusing to understand. Let's understand how to use the function using an example. Here we have some cash flow for Mr J. Since Mr J is confused what percentage of interest is paid over the given period.

Use the formula in D2 cell

=IRR(B2:B9)

Use the array as shown in the image above. Press Enter

11.91% is the rate of return of the Cash Flow array.

As we took the first amount as negative and others positive so it comes as negative.

Notes:

The function provides the #VALUE! error if any argument to the function is non - numeric.
The function returns #NUM! error if the credit amount & debit amount signs are not correct. Excel understands negative as amount received and positive as amount paid.
IRR function calculates results by iteration and can have zero or more solutions. If the successive results of rate do not converge to within 0.0000001 after 20 iterations, The function returns the #NUM! error value.

Hope this article about How to use the IRR function in Excel is explanatory. Find more articles on financial values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles :

How to use the MIRR function in excel : returns the Modified interest rate of return for the financial data having Investment, finance rate & reinvestment_rate using the MIRR function in Excel.

How to use the XIRR function in excel : returns the Interest rate of return for irregular interval using the XIRR function in Excel

Excel PV vs FV function : find Present Value using PV function and future value using FV function in Excel.

How to use the RECEIVED function in excel : calculates the amount which is received at maturity for a bond with an initial investment (security) and a discount rate, there are no periodic interest payments using the RECEIVED function in excel.

How to use the NPER function in excel : NPER function to calculate periods on payments in Excel.

How to use the PRICE function in excel : returns the price per $100 face value of a security that pays periodic interest using the PRICE function in Excel.

Popular Articles :

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.