I. Intro
The IRR function is commonly used to calculate the internal rate of return (IRR) based on a series of cash flows that occur at regular intervals.
II. Explanation
- Formula: =IRR(values,[guess])
- Parameters:
- values (required): This includes arrays or ranges of income or expenditure related to an investment.
- guess (optional): The estimated value of the internal rate of return.
- Example: =IRR(A2:A25,1%)
- Note:
- The IRR and NPV (net present value) functions are closely related. The rate of return calculated by the IRR function calculates is the interest rate when the net present value is 0.
- The formula below demonstrates the relationship between the NPV and IRR functions.
- NPV(IRR(B1:B6),B1:B6) is equal to 3.60E-08 (within the precise requirements calculated by the IRR function, the value 3.60E-08 can be the effective value of 0).
III. Steps
Use the IRR function
- Select a cell and enter =IRR.
- Enter formula parameters =IRR(B2:B5) into the cell.
- Press Enter to display the result in the cell, which is -46%.
Delete the IRR function
Select the cell with the IRR function applied, and press Delete to clear the formula from the selected cell.
IV. Scenarios
- Finance essentials: Use the IRR function to quickly calculate the internal rate of return for an investment
The internal rate of return is different every year. In order to easily and quickly calculate the rate of return, we can use the IRR function.
- Formulas used in the figure below:
- =IRR(B2:B6)
- =IRR(B2:B7)
- Formula parameter explanation: If we want to calculate the internal rate of return after four years, we simply need to pull the "net income from the first year" to the "net income from the fourth year". We can do the same for the internal rate of return after five years.