IRR Formula

To calculate Internal Rate of Return using the IRR formula in Excel, you need a series of periodic cash flows like that shown in the figure below.

IRR Calculation
The IRR can then be calculated using the following formula, with 0.1 being the initial guess at the rate:

=IRR(values,guess)
=IRR(B2:B6,0.1)

The IRR formula requires at least one negative and one positive value. Normally, the negative value at t=0 represents the initial investment. The future cash flows can be negative or positive, but they need to be periodic (occuring at t=1, t=2, t=3, etc). If you have non-periodic cash flows, you can use the XIRR formula.

Download an IRR Calculator for Excel.

4 comments:

Tips For Excel said...

top tip. Get asked this all the time in my job.

Unknown said...

how to calculate IRR without using the formula given in excel?

Muhammed eLaithy said...

For Example; suppose that there is a project costs $10 million today, and will provide a $15 million payoff three years from now, we use the FV of a single-sum formula and solve for r to compute the IRR.

IRR = (FV/PV)1/N -1 = (15 million/10 million)1/3 - 1 = (1.5) 1/3 - 1 = (1.1447) - 1 = 0.1447, or 14.47%

In this case, as long as our hurdle rate is less than 14.47%, we green light the project.

Anonymous said...

The above assumes no payment received until the end of year 3... To be clear, the formula should be ((FV/PV)^1/N)-1

The math to figure out IRR with yearly cash flows is a guessing game to bring NPV to 0.00 by changing the discount rate.