You've successfully subscribed to The Finbox Blog
Great! Next, complete checkout for full access to The Finbox Blog
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.
Internal Rate Of Return (IRR) Explained: Formula, Excel Calculation, Free Template, And More

Internal Rate Of Return (IRR) Explained: Formula, Excel Calculation, Free Template, And More

. 5 min read

In this article

What Is The IRR (Internal Rate Of Return)?

The IRR (Internal Rate of Return) is the compounded annual return an investor can expect over an investment’s lifetime. Financial analysts and investors use it to determine the potential of an investment and evaluate the intrinsic value of a business, investment securities, and more. Although it may seem a bit difficult at first, the IRR is a very straightforward concept. Let's make things clear with an example.

Alex owns a shop in the city center and wants to buy a $10.000 truck that could add $2.000 a year in profit to the business. The truck has an estimated useful life of ten (10) years, after which it must be replaced. Alex could get the funds from a bank with a 4% interest rate.

How does he know if this is a profitable investment? He can calculate the internal rate of return of the truck's investment and check if that's higher than the 4% interest rate! In this article, we'll discuss everything you need to know about it.

How To Calculate IRR: Formula Explained

Now that we have an idea of the IRR’s meaning, we can move forward and discuss how to calculate the IRR with the right formula to analyze the profitability of a project or an investment. Calculating the IRR manually is not easy. The only way to do that is to try several calculations to get the discount rate that makes the Net Present Value equal to zero (0).

As discussed in the NPV guide, the net present value is the value of the future cash flows of an investment discounted to the present. If you're not familiar with the concept, I suggest reading our NPV guide before continuing with this one.

So the IRR is the discount rate at which the NPV is equal to zero (0). Although it may seem a bit difficult at first, the IRR is a very straightforward concept, and it will become crystal clear once you've read this guide. Let's get back to Alex:

As discussed above, he can invest in a $10.000 truck, which could add $2.000 yearly profit to the business. He could get the fund required from a bank for a certain interest rate. Well, the IRR is the interest rate at which interest expenses would be equal to the investment's revenue. In other words, the IRR is the maximum interest rate at which Kevin can borrow the funds and make a profit from the investment. Any interest rate greater than the IRR would mean a loss for the business.

How To Calculate Internal Rate Of Return (IRR) In Excel [+ Free Template]


You can easily undertake an IRR calculation in Excel using the IRR or the XIRR functions. The IRR function assumes that all the cash flow occurs at regular and equal periods, while, with the XIRR function, analysts can specify precise dates for each cash flow and calculate the internal rate of return of potentially irregularly spaced cash flows.

When possible, it's preferable to use the XIRR function since it is much more accurate—let's analyze the example below:

XIRR vs IRR in excel


Download The Template

We can observe that the two functions return different values. That's because the IRR function assumes all periods are equal, while XIRR handles the real intervals, which in some years are 366 instead of 365.

Let's discuss other differences and analyze how to apply each function to a real example.

How To Use XIRR Function In Excel

XIRR function excel google sheets


As depicted in the picture above, using the XIRR function in Excel is very straightforward. You just need to set a series of cash flows and dates. Here's the XIRR function breakdown:

XIRR(cashflow_amounts, cashflow_dates)

How To Use IRR Function In Excel

IRR function excel


As depicted above, the IRR function is slightly different—you don't need to select a range of dates since the IRR function assumes that all the cash flow occurs at regular and equal periods. Here's the IRR function breakdown:

IRR(series of cash flow)

Differences Between IRR and XIRR functions

  1. The IRR function assumes that all the cash flow occurs at regular and equal periods, while, with the XIRR function, analysts can specify precise dates for each cash flow and calculate the internal rate of return of potentially irregularly spaced cash flows.
  2. Using the IRR function, you don't need to select a range of dates since it assumes that all the cash flow occurs at regular and equal periods.
  3. More often than not, the two functions return different values because the IRR function assumes all periods are equal, while XIRR handles the real intervals, which are usually different for each month/year.

IRR Example Calculation—Interpreting Internal Rate Of Return

As discussed above, Alex owns a shop in the city center and wants to buy a $10.000 truck that could add $2.000 a year in profit to the business. The truck has an estimated useful life of 10 years, after which it must be replaced. Alex could get the funds from a bank with a 4% interest rate.

An internal rate of return calculation can help investors determine whether to take an investment or not given a series of projected cash flows. As shown before, the IRR of 15.10% is greater than the 4% interest rate, making the truck a profitable investment for the business.

IRR vs. NPV

The Net Present Value is the value of the future cash flows of an investment discounted to the present, while the IRR (Internal Rate of Return) is the compounded annual return an investor can expect over the investment’s lifetime. IRR is also the discount rate at which the NPV is equal to zero (0). Let's analyze the difference between NPV and IRR with the previous example:

NPV vs IRR excel


If Alex gets a loan at a 4% interest rate, he will realize a significant gain on the investment because its compounded annual return (IRR) is 15.09%. As you can see, the NPV is greater than zero (0), which means that the investment will add value to the business.

Another way to interpret the NPV is to consider it as the investor's profit after interest expenses. In other words, adding the NPV to the initial investment represents the price at which the investor would obtain zero (0) profit. Any price above that number would mean a loss.  

As depicted in the picture below, a cost of capital equal to the investment's internal rate of return means zero (0) profit for the investor.

IRR vs NPV real example


Did you notice that the net present value is different using the NPV and the XNPV function? Can you spot the difference between the two functions? Learn everything you need to know in this NPV guide!