Excel IRR Formula
MS Excel provides several inbuilt functions, including the IRR function, which returns the internal rate of the return for given cash flow data. This cash flow data should be regular, like monthly, yearly, etc. In this article, we will learn about Excel IRR Formula.
It is one of the Financial functions members use to calculate the investment’s interest rate (in negative value) and the generated revenue (as positive).
Syntax of the IRR Function:
IRR () – It will return an internal rate of the return depending on the output from the given parameter. It will return the value in the percentage term.
The Argument in the Function:
- Values: It is a mandatory field in which a user wants to calculate the internal rate of the return. Initially, all investments will be negative, and all generated revenue will be positive.
- Guess: It is an optional field provided by the user, which he thinks is closer to the internal rate of return. If a user is not providing any value, the function will take the default value, 0.1 or 10%.
How to Use Excel IRR Formula?
Excel IRR Formula is very simple and easy. Let’s understand how to use the Excel IRR Formula with some examples.
Example 1
A user has some investment and income generation details from a company where he wants to calculate the internal rate of return. The company owner invested one lakh as the initial investment on top of that cash flow generated by the company given in the table. So, a user wants to calculate the internal rate of the company’s return for the given 5-year data.
Let’s see how the ‘IRR’ function can solve his problem of calculating the company’s internal rate for the 5-year data.
- Open MS Excel and go to sheet1, where the user wants to calculate the company’s internal rate for the 5-year data in the table.
- Create a header column with IRR Output in Column C, in which we will function the result.
- Merge cell from B2 to B7. Now apply the IRR function in cell C2.
- Now select the value from the B2 cell to B7.
- Click on the Enter key. The result will be shown to the user in the percentage term.
Summary of Example 1:
The user wants to Calculate the Internal Rate of the Return for a company, where 5-year investment and income details were given. So, as a result, we can see the result is 12%. In 5-year, time frame 12 % is the rate of the return for the company.
Example 2
A user has some investment and income generation details from a company where he wants to calculate the internal rate of the return after 3 years and 7 years of the initial amount invested. The company owner invested 1.5 as the initial investment because whatever cash flow the company generates is in the table. So, a user wants to calculate the company’s internal rate after 3 years and 7 years of the initial amount invested (2012-2019).
Let’s see how the ‘IRR’ function can solve this problem to calculate the company’s internal rate for the 7-year data.
- Open MS Excel, and go to the sheet where the user wants to calculate the Internal Rate of the returned of the company for the given 5-year data in the table.
- Create a Header Column with IRR Output in the E column, which we will function result.
- Now apply IRR Formula in cell E2 for a 3-year calculation.
- Now select the value from cell C2 to C5.
- After applying this Formula, the IRR Output is given below.
- Now apply the IRR formula in cell E6 for a 7-year calculation.
- Now select the value from the C2 cell to C9.
- Click on the Enter key. The result will be shown to the user in the percentage term.
Summary of Example 2:
The user wants to calculate the internal rate of the return for a company after 3 years and 7 years of initial 1.5 lakh investment, where 7-year investment and income details were given.
So, we can see the result -25%, which is the initial rate of return in a 3-year time frame, and 11 % is the initial rate of return in 7 years for the company.
Things to Remember About Excel IRR Formula
- The value Parameter in the function is a mandatory field, which a user wants to calculate the internal rate of the return.
- Initially, all investments will be negative, and all generated revenue will be positive.
- If a user does not provide any value for Guess, the function will take the default value, 0.1 or 10%. IRR function will try max 20s time. If it is not found after that, it will return a #NUM error. Then a user can try with some different number.
- The IRR” function will return the value in the percentage term.
- If a user provides any text, blank or logical, the function will ignore the value.
- All values provided in the function should be in sequential order in time.
Recommended Articles
This has been a guide to Excel IRR Formula. Here we discussed How to use Excel IRR Formula, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
