IRR function in Excel - Usage and examples
In Excel financial functions, users cannot ignore IRR - the function that returns the internal rate of return for a series of cash flows. So how does the function structure and usage of the IRR function? Please read the article below.
What is the internal rate of return?
In corporate finance, the Internal Rate of Return (IRR) is a ratio of profits used in capital budgeting to measure and compare return on investment. More specifically, the discount rate at which the net present value of the investment (negative cash flow) is equal to the net present value of the investment (positive cash flow).
Internal rate of return is often used to assess the necessity of an investment or project. The higher the internal rate of return for a project, the more desirable it is to execute the project. Assuming all projects require the same amount of investment, the project with the highest IRR level will be considered best and executed first.
IRR calculation formula:
(NPV = sumlimits_ {t = 1} ^ n {frac {{{C_t}}} {{{{(1 + r)} ^ t}}}} - {C_0} = 0)
Inside:
- t: is the time to calculate cash flow.
- n: is the total time of project implementation.
- r: is the discount rate (IRR).
- ({{C_t}}): is the net cash flow at time t.
- ({{C_0}}): is the initial cost to implement the project.
Specific example: If an investment project can be put in place by the sequence of cash flow.
Year (n)
Cash flow ({{C_n}})
0
-4000
first
1200
2
1410
3
1875
4
1050
The IRR is then given by the formula:
(NPV = - 4000 + frac {{1200}} {{{{(1 + r)} ^ 1}}} + frac {{1410}} {{{{(1 + r)} ^ 2}}} + frac {{1875}} {{{{(1 + r)} ^ 3}}} + frac {{1050}} {{{{(1 + r)} ^ 4}}} = 0)
In this case, after 4 years, the return rate r = 14.3%.
Function structure and usage
Grasping the needs of users, Excel has created a calculation formula for the quick IRR function.
Function structure: = IRR (values, [guess] ).
Inside:
- IRR: The function name used to calculate the internal rate of return.
- Values: Required value. An array or reference to cells containing cash flow data ({{C_n}}).
- Guess: An estimated% that is close to the result of IRR (). The optional value if ignoring the default value is 10%.
Note:
- The value must contain at least one negative value, which is the initial cost of the project.
- Value values must be entered in the correct chronological order of cash flow to give the correct results.
- If Value contains one or more non-numeric data, the function will ignore those values.
- The IRR function returns the #NUM! Error value . If you do not have at least one negative value in the Value string or you find no meaningful results after 20 attempts.
- In case the IRR function returns the #NUM! or if the result does not match the expected value, try again with a different guess value .
- The IRR function is closely related to the NPV function, because the rate that the IRR function returns is the interest rate that makes NPV = 0.
Illustration
With the above example, if you use a manual calculation formula, it will take a lot of time to solve this problem. But if you use Excel, the result is just after 1s, you can even calculate the rate of return after 1 year, 2 years, 3 years .
You enter the above data sheet into the worksheet:
Excel will calculate the rate of return after any year you need.
In addition, you can enter the number in the IRR function, type any cell in the Excel worksheet = IRR ({- 4000,1200,1410,1875,1050}, 10%). The result is still 14.3%.
Good luck!
You should read it
- Function Address - The function returns the address of a cell in Excel (usage, examples, examples)
- OR function in Excel, how to use the OR function, and examples
- PMT function in Excel - Usage and examples
- DCOUNT function in Excel - Usage and practical examples
- FIND function in Excel - Usage and examples
- Match function in Excel - Usage and illustrative examples
- VLOOKUP function to use and specific examples
- ROUNDUP function in Excel - Usage and examples
May be interested
- DSUM function in Excel, how to use DSUM function and examplesdsum function in excel, how to use dsum function and examples. the dsum function in excel is a fairly common function with a fairly simple usage. but if you do not know how to use the dsum function, you can refer to the following article to understand h
- How to use the MAXA function in Excel, detailed examplesthe maxa function in excel helps find the largest value in a data set, including numbers, logical values, and text containing numbers. this is different from the max function, which only counts numbers. let's learn the syntax and usage of the maxa function with practical examples.
- RANK function - Rank function in Excel - Usage and examplesthe rank function arranges equal numbers of the same rank. however, the duplication will affect the ranking of subsequent numbers. for example, you have the sequence number: 1,2,2,3,4 and are arranged in ascending order, so the order of the number 1 in the series is 5.
- DCount function in Excel - How to use the DCount function and examples using the DCount functiondcount function in excel - how to use the dcount function and examples using the dcount function you have a large list of students who want to get a high total score, but don't want to spend a lot of time. in this article, introduce to you the dcount function in excel. help c
- The search function satisfies many conditions in Excel - Usage and examplesthe search function satisfies many conditions in excel - usage and examples. in the process of processing data in excel many times you need to search for data that satisfies many conditions to extract data, so you are looking for a function that satisfies many things.
- INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT functionindirect function in excel - how to use indirect function and examples using indirect function. you want to refer to a range of data without changing the formula in the cell. the following article introduces you to the indirect function in excel to help you reference
- Multiple if function - Usage and examplesthe following article gives you a detailed guide on how to use the multiple if function with examples. the if function is simplified in the sense that the function tests a value with a given condition, if it satisfies the condition that returns 1 value, if it does not satisfy the condition.
- SUM function in Excel, sum function and examplessum is a basic excel function that allows users to quickly and accurately calculate the sum of columns or cells in an excel spreadsheet. for an overview of the concept, formula and how to use the sum function in excel, readers can refer to our article below. with many illustrative examples from simple to complex, readers can understand and quickly apply this function to practical work.
- Exponential functions in Excel - Usage and examplesexponential functions in excel - usage and examples. are you looking for exponential functions in excel to use instead of the exponential operator '^'? so let's learn the exponential function in excel that the article shares below.
- MATCH function in Excel, usage and examplesthe match function in excel helps determine the position of a value in a list or range of cells. this is a useful tool when working with large data sets and can be combined with the index function to retrieve more precise information.