How to Calculate IRR in Excel: 4 Best Methods in 2023

If you want to make informed decisions toward systematic investment plans (SIP) or invest in business projects as a venture capitalist, you must learn how to calculate IRR in Excel.

There are many ways to calculate the return on investment (ROI) for investments made in financial or business projects. While a fixed rate-based calculation is quite easy, it’s not always the choice of tool for ROI calculation in big businesses or investment banking.

Let’s say you’ll be investing a sum of money in an investment plan for the next 20 years. The plan says that you’ll get a return every quarter. Now, whether it’s smart to invest in such a plan or not can be decided from the IRR value of the SIP. The higher the IRR more lucrative the investment plan. The same is true for all other capital-intensive investments in businesses, bank savings accounts, mutual funds, 401(k), etc.

So that you don’t rely on others when it comes to investing hard-earned money, I help you learn what is IRR in Excel, its several modified formulas, and how to calculate IRR in Excel in various effortless ways below.

What Is the Internal Rate of Return (IRR)?

IRR or internal rate of return is a common metric used by financial planners to calculate or predict the future profitability of a potential business project or other types of investments. In financial terms, it’s popularly known as the economic rate of return or discounted cash flow rate of return.

IRR rate or discount rate of investment makes all the net present value of all kinds of cash flows like outflows and inflows equal to zero. Financial professionals call this internal rate of return because this calculation doesn’t consider other factors that might positively or negatively impact the business in the coming years.

For instance, IRR doesn’t take the cost of capital, inflation rate over time, financial risks, etc., into account when predicting the profitability of a business project or financial investment.

In a nutshell, whenever you decide to invest for a prolonged period in any commercial project, mutual fund, or retirement investment plan, find out the IRR of those investment options. Then pick the one that gives the maximum IRR among all the options.

Also read: Best Excel Budget Templates

What Are the IRR Functions in Microsoft Excel?

Thanks to Microsoft, you don’t need to create multiple formulas or go through complex calculations to get the IRR of an investment plan. Excel has three dedicated internal rate of return calculation functions. Learn about these functions here:

1. IRR for Simple Internal Rate of Return

The IRR syntax in Excel calculates the interest rate of an investment in a business project or mutual fund or retirement planning fund. The investment must include at least one positive and negative value. Also, the cash flow into the fund should occur at regular intervals. Here’s how the formula looks:

=IRR(values or cell ranges, [guess argument])

• Values is a required input for the IRR function. It could be a series of numbers, an array of numbers, or a reference cell range consist cash flows.
• Guess argument is optional. If you don’t supply a Guess value, then Excel considers it 0.1, 10%, or 10 basis points internal rate of return required by the investment plan. If you do provide a Guess argument, Excel will run the calculations for up to 20 iterative sessions to find an IRR value close to the Guess value.

2. XIRR for Unequal Timing of Returns

If you need to factor in the schedule of cash flows, then you must use the XIRR function. The syntax is as below:

=XIRR(values or cell ranges, dates, [guess argument])

3. MIRR for Reinvestment of Project Funds

MIRR gives highly reliable IRR values as compared to the simple IRR function. IRR doesn’t consider the cost of capital that you invest. Hence, the final value of IRR isn’t accurate. Instead, you can use the MIRR function to include the cost of a capital factor in the IRR calculation. Hence, the syntax becomes as shown below:

=MIRR(values or cell ranges, finance_rate, reinvest_rate)

• Values is the cash flow statement consisting of at least one each of positive and negative cash flows.
• Finance_rate is the interest you pay to the lender against the money borrowed for a business project.
• Reinvest_rate is the interest rate your business or investment fund receives on cash flows upon reinvestment.

Also read: How to Use Excel Subtraction Formula

How to Calculate IRR in Excel: Using the IRR Syntax

Before you can start calculating IRR for several investment plans or projects, you must organize the data in Excel. Here is how it’s done:

• Open an Excel worksheet and create three column headers: Period, Description, and Cashflows.
• Populate the data you want to be calculated under the respective column headers.

• The first investment should be a negative value since you’re paying a mutual fund or business entrepreneur to utilize the money.
• If you’ll be getting regular income after one month or one year, then those incomes should be added as positive values.

• Now, use the following formula in a cell where you want Excel to calculate the IRR value:

=IRR(C2:C8)

• Hit Enter to get the ROI on a potential investment.

This IRR calculation is based on a default Guess argument of 10%. Let’s say you’re looking for a 12% return. Here’s how how you need to enter the guess value:

=IRR(C2:C8,30%)

• In the above table, you see that the last cash flow is negative. Thus, the cash flow must change from positive to negative or negative to positive.

• In this scenario, use a Guess to find the accurate result since IRR will produce two results.

• The Guess argument should be the investment ROI you’re expecting.

How to Calculate IRR in Excel: Using the XIRR Syntax

When you get returns on different dates after the initial investment, using IRR syntax will give flawed results. In this scenario, you must use the XIRR syntax. Here are the steps you can try on your own Excel worksheet:

• Create a column header for the Date of Returns to the right side of the Cashflows column.
• Now, enter the date of investment on the top and then enter the expected date of interest or return credits.

• Once done, use the following XIRR syntax in any cell to get the internal rate of return for investment with an irregular return payment schedule:

=XIRR(C2:C8,D2:D8)

How to Calculate IRR in Excel: Using the MIRR Syntax

To make the IRR value more realistic, you can use the MIRR formula. Here, you can include two mandatory factors: cost of capital and reinvestment rate. Find below the steps to use MIRR in Excel:

• In the current worksheet, add two rows of information as shown in the image.

• These are the Cost of capital and Reinvestment rate.

• In their respective cells, enter the values as well.
• Now, use the following formula to get a realistic internal rate of return:

=MIRR(C2:C8,G2,G3)

How to Calculate IRR in Excel: Using Goal Seek Excel

The IRR and its modified formulas perform 20 iterative calculations before displaying a result. If you need greater accuracy than this, you can use the Excel Goal Seek function to calculate IRR. In this method, Excel performs up to 32,000 iterative calculations before generating a return value. Here are the instructions to use Goal Seek and calculate IRR in Excel:

• Set IRR to 11% or anything else manually.

• Calculate the NPV below IRR using this formula:

=NPV(C9,C3:C8)+C2

• Now, click the Data tab and then select the What-If Analysis in the Forecast command group on the Excel ribbon menu.
• Select Goal Seek from the context menu that pops up.
• Here, the Set cell should be the NPV value. Then, the To value should be 0.

• Set By changing cell to the IRR value.
• You just need to enter the cell references as shown in the image.

• Now, click OK to calculate IRR via Goal Seek.
• According to the current dataset, the NPV will be 0.00 if IRR is 12%.

In the Goal Seek method, you don’t actually need to calculate IRR from cash flow and investment amount. Instead, you set an expected IRR in the IRR cell and use it to calculate NPV using cash flow and investment amount. Then, you use the iterative calculation capability of Excel to predict when NPV becomes 0.00 at which IRR value.

Tips to Calculate IRR on Excel Correctly

Remember these tips and facts when you go to calculate IRR on Excel to get reliable IRR percentages:

• The argument for the Values part must contain an income (positive cash flow) and outlay (negative cash flow) for the function to work accurately.
• The IRR function will only process numbers in the Values argument. It’ll ignore any other elements like logical values, empty cells, and texts.
• There must be regular cash flow intervals like weekly, monthly, quarterly, annually, etc. It’s not mandatory that the cash flows are equal in dollar value.
• Ensure you prepare the cash flow table in chronological order. For example, if you’re paying $100 each month, then arrange the cash flow values and their dates from January, February, March, etc.
• In simple IRR calculations, it’s not mandatory to enter a Guess argument.
• When using a Guess value, the IRR formula will show a value closest to the Guess argument. If it shows #NUM! Error, change the Guess argument value.

What Are the Limitations of IRR in Excel?

Before you can apply the IRR value calculated on Excel in real-world situations, you should also know the inherent limitations of the internal rate of return formula in Excel:

• IRR is an expression of ROI in percentage. It doesn’t consider the absolute value. Thus, it may show a higher percentage of return for a project of low dollar value. However, venture capitalists and individual investors look for projects of higher dollar value though the ROI percent is lesser than small projects.
• IRR formula returns a value by considering that the project will reinvest its capital or cash flow at the same rate as the calculated IRR. This isn’t always possible because return rates fluctuate a lot in the capital market.
• If the investment plan or project involves alternating positive and negative cash flows, then you might get more than one IRR for the same project.

Conclusion

Now you know how to calculate IRR on Excel using various dedicated IRR syntaxes. Also, you learned how to use Excel Goal Seek to calculate IRR in Excel. If I missed any other method, don’t forget to mention that in the comment box below.

Next up, how to use Excel IF-THEN formula.