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
- How to create a template for PowerPoint presentation slidesoften the process of making slides you often use the template available. but in some cases you want to be unique from everyone by creating your own presentation templates. the following article helps you create your own template in powerpoint.
- How to recover unsaved, unsaved Word files when the power goes out suddenlyto get rid of lost data or unsaved or untimely saved articles, use word's data recovery feature. the following article guides you how to recover unsaved, unsaved word files when a sudden power outage.
- Synthesis of the most beautiful Vietnamese font for graphic design or text decorationwhen designing graphics or text decoration, sometimes you will be confused when you do not know which font to choose beautifully or simply the fonts you have chosen have the error of missing accents and cannot display vietnamese. today tipsmake.com would like to introduce to you the most beautiful vietnamese fonts for graphic design
- Software for converting images to the most standard Wordyou have an image file and want to convert to word format to facilitate data editing but do not know how to do it. in this article, tipsmake.com would like to send you detailed instructions on how to convert from image file to word format in the most standard and accurate way.
- How to round whole numbers in Excelhow to round whole numbers in excel in excel there are many mathematical functions used to round numbers. today software tips will guide you some functions that can be used to round whole numbers in excel.
- 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.