OmniDocHow to calculate the internal rate of return using the IRR formula in Excel
It explains how to fill in the parameters of the IRR function in Excel, in what order the cash flows should be arranged, and how to read the results. It also provides methods for checking NPV and annualizing monthly data, which is suitable for readers who do investment or project calculations.
Key points of this article
- Cash flows are arranged in chronological order. The initial investment is filled with negative numbers, and subsequent net inflows are filled with positive numbers. The order cannot be messed up.
- The formula is written as =IRR(area), the result is the rate of return for each period, and the annual rate of return is obtained when the period is year
- Use NPV to substitute the results for verification. Both are close to zero, indicating that the calculation is basically reliable.
- The monthly cash flow is the monthly rate of return, and the annualized rate is (1+monthly interest rate)^12-1
- Use XIRR instead when the dates are irregular, and compare with the cost of funds before drawing a conclusion
If you are doing calculations for investment, equipment purchase or project approval, there will often be a series of cash flows in the table: a sum of money is invested at the beginning, and then some is recovered every year or every month. At this time, "how much can this project earn?" needs to be answered using the internal rate of return (IRR). Excel provides the IRR function. Many people will search for the irr formula in Excel, but they often get wrong results or cannot understand the meaning of the numbers.
This article is for readers who already have cash flow data and want to calculate IRR on their own. We will explain how to fill in each parameter of the function, in what order the cash flows should be arranged, how to read the results, and how to check with NPV to avoid calculation errors.
How to use Excel’s IRR function to calculate the internal rate of return? One sentence conclusion
One sentence conclusion: Put the cash flows of each period into consecutive cells in chronological order, and use =IRR(area) to find the rate of return for each period; if the period is years, you will get the annualized result, if the period is months, you need to convert.
| your situation | Recommended practices | reason |
|---|---|---|
| Cash flows occur at regular intervals (annual or monthly) | Using the IRR function | IRR assumes that the intervals between periods are the same, and the result is the rate of return during that period |
| Cash flow dates are irregular, for example, some transactions are separated by half a year, and certain transactions are separated by a quarter. | Use the XIRR function and fill in the date | IRR does not read the date, and the result will deviate from the actual value if the intervals are inconsistent. |
| The data is monthly cash flow and I want to compare annual income. | First use IRR to get the monthly rate of return, and then annualize it | Monthly yield cannot be directly compared to annual interest rate |
| Cash flows are all positive or all negative | Check the data first, don’t apply formulas directly | When there is no positive or negative change, the IRR has no solution and usually returns #NUM! |
| The results are far different from expectations | Substitute NPV into the results to check | Checking can reveal sequence errors or missing periods. |
Before you start: Confirm these points first
- Symbol of cash flow. Inputs (expenditures) are recorded as negative numbers, and withdrawals (inflows) are recorded as positive numbers. IRR relies on positive and negative changes to find the rate of return. If the sign is reversed, the result will be meaningless.
- Is the period consistent? Each grid should represent the same length of time. If the first few years have full-year data and a later period only has half-year data, the data should be split first, or XIRR should be used instead.
- Whether period 0 is included. The initial investment is usually placed in the starting cell, representing time point 0. If you miss this amount, the function will not calculate the rate of return you want.
- Amount is before tax or after tax. IRR is only calculated on the numbers you enter. Whether you enter the gross amount before tax or the net amount after deducting taxes and operating costs determines what the result represents.
- What is the benchmark? IRR needs to be compared with the cost of capital or your own required rate of return. It is difficult to judge whether it is good or bad by a percentage alone.
Steps to use the IRR function to calculate a set of cash flows in Excel
- Write the period number in column A, such as 0, 1, 2, 3, 4; fill in the corresponding net cash flow in column B, and fill in the negative number for the initial investment.
- Confirm that the data in column B is a continuous area, with no blank lines or text descriptions in the middle.
- Enter =IRR(B2:B6) in a blank cell and replace the range with your actual range.
- Press Enter to get a decimal, for example 0.149.
- Set the format of the cell to percentage so that it can be displayed for direct reading and comparison.
- If the cash flow is monthly, treat the result as the monthly rate of return, and enter =(1+this cell)^12-1 in another cell to get the annualized value.
- Check with NPV: Enter =B2+NPV (the cell where the result is located, B3:B6) in another cell, the result should be close to 0.
For example: a certain amount of money is invested first in a project, and the amount of money flowing in changes year by year in the next four years. After following the above steps, substitute the obtained IRR back into the NPV formula. The result is close to 0, indicating that the calculation is basically correct. You can first practice with a set of simple numbers with known results, make sure you understand each step, and then switch to your own data.
Check each item after completion
After getting the results, do not use them directly for decision-making, but check them one by one first.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Whether the result is a number | The cell displays a numeric value instead of #NUM! | Check whether cash flow is entirely positive or entirely negative; You can also add a second parameter guess, such as =IRR(B2:B6, 0.1), which gives an approximate initial value |
| NPV check | After substituting the results, the NPV is close to zero | When not close, check whether the area is misaligned and if the serial number is missing |
| Whether the units during this period are unified | All data is in years or months | After unifying the units, recalculate and do not mix them |
| Whether the citation scope is complete | The area includes initial investment through to the final phase | Select the area with the mouse box and check the cell address |
| Whether the results are reasonable | Compare with project expectations and similar projects | If abnormally high or negative, check the positive and negative signs and the unit of amount later |
❓ Frequently Asked Questions (FAQ)
1. Why is the IRR result #NUM!?
A common reason is that cash flow is either entirely positive or entirely negative, with no positive or negative changes in between. It may also be that the guessed value is too far from the actual result, causing the calculation to fail to converge. First, check the symbol, then try giving the guess argument a close number, such as 0.1.
2. Does a higher IRR necessarily mean a better project?
Not necessarily. IRR is a ratio and does not reflect the scale of the project. When comparing two projects, you should also consider the investment amount, payback period, and the cost of capital.
3. What to do if the dates of cash flow are irregular?
When switching to the XIRR function, you need to provide the corresponding date for each cash flow at the same time. IRR is calculated only by period number and does not read the date.
4. What does a negative result mean?
This shows that based on this set of cash flows, the total amount recovered is less than the investment, so under this assumption, the project will incur losses. Specific judgments still depend on capital costs, taxes, and whether the data is complete.
5. How to enter cash flow data in PDF reports?
If the table text in the PDF can be selected, copy each item into Excel and then compare the amount and the pros and minus signs against the original table. If the scanned report cannot select text, OCR recognition must be used first, and the recognition results must be checked item by item, as machine recognition may be error.
📝 Summary
Arrange cash flow by time, distinguish between positive and negative signs, and use = IRR (region) to get the rate of return; Remember to annualize monthly data and verify results with NPV. Mastering IRR formulas and Excel calculation methods is key, not just filling in formulas.
