How to Calculate Internal Rate of Return (IRR) in Excel and Google Sheets

Step 1: Enter Cash Flows for Both Investments

Open MS Excel or Google Sheets. Put a heading in cell A2: “(1) Year.” In B2, put the heading ” (1) Cash Flow.” The “1” is to remind you that these are the columns for the first investment. Then put in the year numbers in A2 to A7, then enter the cash flows in cells B2 to B6. The 0-year will be for the initial investment put in. Do the same for the second scenario, the startup investment, in columns C and D, as here:

Step 2: Use the IRR Function

In cell B9, type “=IRR(B2:B7)” for the first scenario. Then input “=IRR(D2:D6)” in cell D9 for the second scenario. The formula is the same for MS Excel and Google Sheets. In this case, you haven’t entered a “guess” for the IRR in the formula (you would put this after the cell range and a comma). If no parameters are entered, Excel starts testing IRR values differently for the entered series of cash flows and stops as soon as a rate is selected that brings the NPV to zero. If Excel or Sheets doesn’t find any rate reducing the NPV to zero, it could show the error “#NUM.” Should that occur, you’ll need to use an alternative.

Optional alternative: If you have a reasonable estimate of the IRR, you can add a “guess” value within the parentheses of the IRR function. (If you don’t, then use 0.1 or 10% initially.) This might help the calculation converge faster or avoid errors if multiple solutions are possible. For instance, if you expect a 15% return for the real estate scenario (1), the formula would be “=IRR(B2:B7, 0.15).” If you expect an 8% return for the startup scenario (2), you would type the formula “=IRR(D2:D6, 0.08).”

Step 3: Results and Interpretation

After applying the IRR function in Google Sheets or Excel, we get the following results:

Scenario 1 (real estate investment): The calculated IRR is 18%. This means that, on average, the real estate investment is expected to generate an annual return of 18% over its five-year lifespan.

Scenario 2 (startup investment): The calculated IRR is 10%. This suggests that the startup business venture is projected to yield an average annual return of 10% over four years.

In this scenario, the real estate investment (1) appears more attractive given its IRR because it offers a higher potential return than the startup (2). However, the IRR is just one element when making an investment decision. You should also consider the following:

• Risk: The real estate investment might carry different risks (e.g., property market volatility, tenant issues) than the startup (e.g., sector-specific competition, macroeconomic context, market acceptance).
• Investment time horizon: The real estate investment has a longer time horizon (five years) than the startup (four years), which could affect your preference.
• Other metrics: The NPV and payback period can help you decide about an investment’s profitability and whether it’s worthwhile.

Calculating MIRR in Excel and Google Sheets

When a company uses different borrowing rates or rates of reinvestment, the MIRR applies. Calculating MIRR in Google Sheets and Excel involves a few extra steps than IRR since you need to put in the finance rate (cost of borrowing) and the reinvestment rate. Here’s how to do it:

Step 1: Determine the Finance and Reinvestment Rates

We’ll use the same cash flows from the two scenarios above, and we will have different finance and reinvestment rates given the distinct characteristics of these investments. We’re calculating the IRR of the investment as in the previous example. However, we’ll be looking at how the company will borrow money to plow back into the investment (negative cash flows) at a rate different from the rate of reinvesting part of the positive cash flow.

Scenario 1 (real estate investment):

• Finance rate: 6.5% (mortgage rate)
• Reinvestment rate: 7% (conservative estimate for rental income reinvestment)

Scenario 2 (startup business investment):

• Finance rate: 9% (higher given the increased risk of startups)
• Reinvestment rate: 10% (potential for higher returns in a growing business)

Step 2: Use the MIRR Function

In cell B10, type “=MIRR(B2:B7, 0.065, 0.07)” for the first scenario. You convert the percentage rates for financing and reinvesting into decimals. The first number in the formula, after the cell range, is the finance rate; the second is the reinvestment rate.

Then type “=MIRR(D2:D6, 0.09, 0.10)” in cell D10 for the second scenario. Again, convert the rates into decimals and put them in the correct place after the cell range in the formula. Here is what you should see:

Step 3: Results and Interpretation

Let’s look at both scenarios:

• Scenario 1: The calculated MIRR is 16%, slightly lower than the IRR of 18%. This suggests that while the investment still offers a solid potential return, the actual annualized return might be closer to 16% when considering the reinvestment of profits at a more conservative rate of 7%. This still indicates a favorable investment but provides a more realistic expectation of the return compared with the 18% IRR.
• Scenario 2: Interestingly, the MIRR is 10%, which matches the IRR in this case. This implies that the assumed reinvestment rate of 10% for the business venture doesn’t alter the expected annualized return. This could be because the business might not generate large excess cash flows for reinvestment in its early stages.

Here are further points to consider:

• Sensitivity analysis: You can experiment with different finance and reinvestment rates to see how they affect the MIRR and better understand the investment’s potential outcomes.
• Risk: MIRR, like IRR, doesn’t account for risk. Always consider the risk profile of each investment alongside the potential returns.

Calculating XIRR in Excel and Google Sheets

When your cash flows don’t occur at regular intervals (e.g., monthly or annually), the XIRR function is the better tool for calculating the IRR. To use this function, Excel and Google Sheets need both the cash flow amounts and the dates on which those cash flows are paid. Since this wasn’t the case in the above examples, we’ll adjust our scenarios to below:

Step 1: Enter Cash Flows and Dates

The cash flows for the real estate and startup investments aren’t disbursed at the same time each year, as was the case when calculating IRR and MIRR above. Instead, they occur at different periods. We use the XIRR function below to solve this calculation. Instead, you’ll enter the dates and cash flow amounts as above.

For investments with cash flows received or cashed at different times for a firm with varying rates of borrowing and reinvestments, Excel and Google Sheets don’t have functions that can be used, though they are probably more likely to occur. But to keep things simple, we can certainly work with the above.

Step 2: Use the XIRR Function

In cell B9, type “=XIRR(B2:B7, A2:A7)” for the first scenario. This provides the range of dates and the cash flows from each column. The formulas are the same for Google Sheets and Excel.

Next, input “=XIRR(D2:D6, C2:C6)” in Cell D9 for the second scenario. The formula is the same for Excel and Google Sheets. In this case, you haven’t entered a “guess” for the XIRR in the formula. If no parameters are entered, Excel and Google Sheets start testing IRR values differently for the entered series of cash flows and stop as soon as a rate is selected that brings the NPV to zero. If Excel or Google Sheets doesn’t find any rate reducing the NPV to zero, it shows the error “#NUM.”

Optional alternative: If you have a reasonable estimate of the IRR, you can add a “guess” value within the parentheses of the IRR function. (Typically, 0.1 or 10% is a good start.) This might help the calculation converge faster or avoid errors if multiple solutions are possible. For instance, if you expect a 10% return for the real estate scenario (1), the formula would be “=XIRR(B2:B7, 0.10).” If you expect an 8% return for the startup scenario (2), you would type the formula “=XIRR(D2:D6, 0.08).”

Below is the XIRR calculated with this range of data for both scenarios:

Step 3: Results and Interpretation

After applying the XIRR function in both Google Sheets or Excel, we obtained the following annualized rates of return:

• Scenario 1 (real estate investment): The calculated XIRR is 35%. This suggests that the real estate investment, with its irregular cash flow timings, is expected to generate an impressive average annual return of 35% over its holding period.
• Scenario 2 (startup business investment): The calculated XIRR is 10%. This indicates that despite the irregular cash flow pattern, the startup business venture is projected to yield an average annual return of 10% over its investment period.

As such, the real estate investment (Scenario 1) demonstrates a significantly higher XIRR than the business venture (Scenario 2). This means that the real estate investment is expected to outperform the startup in annualized return despite the uneven timing of cash flows.

Here are further considerations:

• Risk: While the XIRR of 35% for the real estate investment is appealing, it’s important to consider the risks with this type of investment. The real estate market can be volatile, especially in changing interest rate environments, and unexpected expenses or vacancies may affect your actual returns.
• Time horizon: The two investments have slightly different time horizons. While the real estate investment spans about three years, the startup venture spans around two years. This difference in time frame should be taken into account when comparing the XIRR values.

Investment decisions shouldn’t be based on XIRR or any other single element. Remember to account for your risk tolerance, liquidity and diversification needs, and portfolio goals when deciding on your investments.

Explain Like I’m Five

Internal rate of return measures the profitability of an investment. Investors compare the expected internal rate of return of different projects when they are deciding on the best projects to put their money into.

Calculating the internal rate of return requires a complex calculation that accounts for expenses, profits, and how long it takes to generate returns. Spreadsheet software like Excel and Google Sheets make the calculation easy with the built-in IRR, XIRR, and MIRR functions.

The Bottom Line

Using the IRR formula in a spreadsheet application is a potent way to assess the profitability and feasibility of investments. Using the IRR function, you can easily calculate the internal rate of return based on a series of cash flows.

The result provides an annual rate of return, which can be compared with the required rate of return or cost of capital to determine the project’s viability. Excel and Google Sheets also have XIRR and MIRR functions for projects with irregular cash flows or timing. However, it’s crucial to also understand the limitations of IRR, such as its assumptions about reinvestment rates and its potential to give misleading results in specific scenarios. When using IRR in Excel or Google Sheets, it’s wise to consider other financial metrics, such as NPV, and carefully evaluate the assumptions and risks associated with the investment as you make your decision.