IRR Function in Excel – Internal Rate of Return

Whenever a business is willing to invest in a project, it makes sure if it is a profitable investment or not. Let us say project A has 10% IRR whereas Project B has 12% IRR. A business would always invest in a project that has more IRR i.e Project B. In Financial Analysis, IRR is important to understand before investing in any project. In this tutorial, we would cover the function called the IFF formula to find the internal rate of return in excel.

Here we go 😎

When to Use IRR Function of Excel

The word IRR represents the “Internal Rate of Return”. The IRR formula in excel calculates the internal rate of return (IRR) for an initial investment. IRR refers to that rate of return where the present value of the series of cash flows equals the initial investment amount. It is that value where the NPV is zero. This function helps us to compare between different investments or projects.

IRR Function comes in the category of Financial Functions of Excel.

Syntax and Arguments

=IRR(values,[guess])

The below points contain a description of function arguments for the IRR Formula.

• values – This is the investment and a series of cash flows after investing money. The first value would specify the initial investment while the next values would specify the cash flow after 1 year, 2 years and so on.

• [guess] – This is an optional function argument. The [guess] is our guess about the IRR. If we do not specify the guess, Function assumes it as 0.10 or 10%.

Must to Know Points About IRR Function of Excel

The following points about IRR Function should be kept in mind before using the actual Function.

• IRR is the percentage of Discount rate making the net present value zero.

• Initial Investment is negative as it the cash outflow. After which, the cash flows will either be negative or positive depending on the outflow or inflow of cash.

• The Future cash inflows will have less value at present. In other words, we can say that $1000 in 2023 would not be worth same as $1000 in 2021. The future cash is discounted at a rate to get its present value. The percentage is called as Discount rate.

• Net present value is the sum of negative investment and present value of positive/negative cash flows in the coming years.

• The input parameter must contain at least one negative and one positive value to get the result of IRR Function or else it returns a #NUM! error.

• Any text, logical value, empty cells will be ignored when passed as the function arguments.

• The NPV and IRR both play an important role in comparing the Projects for investing money. A positive NPV would mean that the rate of return is more than the discount rate.

Examples to Learn the IRR Function of Excel

In this section of the blog, we will perform some examples to learn the usage of the IRR Function of Excel.

Ex.1 – Simplest Example for IRR Formula

Let us say a company invested an amount of $5000 in a project that returns a sum of $2000 each year for the coming three years. We want to know the Internal Rate of Return for this investment.

The first cash flow is negative as the investment is the cash deduction whereas the rest are positive due to cash received.

Use this Formula to get the value of IRR.

=IRR(B2:B5)

As a result, the formula has given 10% or 0.10 as IRR.

Explanation – We have passed the range B2:B5 as the values argument in the IRR Function. B2 is the initial investment, B3 is the cash flow for the first year, B4 is the cash flow for the second year while B5 is the cash received for the third year. Thus, the discount rate that makes the net present value of these cash flows zero is 10%

Result Verification for IRR Function of Excel

In the above example, we will calculate the present values of cash flows at the internal rate of return of 10%.

Use this formula to get the present value.

=B2/POWER(1+$E$3,A2)

As a result, this formula has given the present value for the investment (which is the same as 5000).

Explanation – The formula to get the value of present value in general is –

Present value – Future value/(1+r)ⁿ

where r is the discount rate ( IRR in this case ) and n is the number of years.

B2 is the future value. POWER Function puts the time (A2) as the power of (1+E3) to give the results.

Now, use the SUM Excel function to get the net present value (sum of investment and the present value of all cashflows)

=SUM(C2:C5)

As a result, we can see that when we took the Discount rate equal to IRR, the NPV comes out to be zero.

Ex.2 – Comparing Project A and Project B using the IRR Function of Excel

In this example let us say, there are two projects.

Project A – Initial Investment of 1000. Zero profit earned in a first and second year whereas a profit of 1520.9 earned in the third year at a discount rate of 10%.

Project B – Initial Investment of 1000. 250, 50 and 1520.9 profit earned in a first, second and third year respectively at a discount rate of 15%

The initial investment in cells B4 and C4 is negative as the investment is the cash outflow.

Use this NPV Formula in cells B8 and C8 to get the net present value of these two projects.

=NPV(B2,B4:B7)

The Net Present Value of Project B is higher than the Net Present Value of Project A.

Now, we will calculate the IRR (the discount rate that makes the NPV zero). Use this formula

=IRR(B4:B7)

As a result, the formula has returned the IRR of two projects as 15 % and 25.519 %.

Explanation – We have passed the range B4:B7 to the IRR formula, the function considers B4 to be an initial investment, B5, B6, and B7 as the cash flow for the first, second and third year. The Discount rate that will make NPV of the cash flows and investment zero is 15 %

Now we will use this IRR (B9 and C9) as the Discount percentage in cells B2 and C2

Now we see that, when we replace the discount rate with the IRR, this makes NPV zero.

IRR of project B is more than the IRR of project A, thus Project A is more beneficial to invest money in.

Thank you for reading 😉