Calculating IRR in Excel

The internal rate of return (IRR) is a way to find what discount rate would cause the net present value (NPV) of a project to be 🌺$0—in other words, to find the highest-yielding proje💦ct or investment.

To calculate IRR in Excel, you can use the Insert Function command to add the IRR function. The syntax for the three different IRR functiไons in Excel are:

=IRR(values, [guess])

=MIRR(values, finance_rate, reinvest_rate)

=XIRR(values, dates, [guess])

“Gues💫s” is an optional argument, a number you think is close to the IRR. Usually, analysts ꦛleave it blank.

Using the Functions in Excel: IRR

Here is a simple example of an IRR analysis with 澳洲幸运5开奖号码历史查询:cash flows that are known and consistent (one year apart). Assume a company is assessing the profitability of Project X. P𒁏roject X requires $250,000 in funding and is expected to generate $100,000 in after-tax cash flows in the first year and grow by $50,000 for each of the next four years.

The IRR function looks like this:

=IRR(values, [guess])

The initial 澳洲幸运5开奖号码历史查询:investment is always negative because it represents an outflow. You are spending something now and anticipౠating a return later. Each subsequent cash flow could be positive or negative—it depends on the estimates of what the project delivers in the future.

Most companies compare the 澳洲幸运🥃5开奖号码历史查询:weighted average cost of ca⛄pital (WACC) with the IRR. In this case, the IRR is 57%. If a company assumed a WACC of 10% for this project, it would add value and could be consi🦩dered a worthwhile investment.

Using the Functions in Excel: MIRR

The 澳洲幸运5开奖号码历史查询:modified internal rat💦e of return (MIRR) is use🍌d when the💞 company expects to borrow and invest. You can also use it to help you calculate when there is a finance rate, such as if the initial outlay for the project requires the company to take out a loan.

Here, assume the company received a great rate of 1% for the $250,000 loan; however, it’s better to use a risk-free rate of return of 2% rather than 57% because it’🧔s a much more conservative guess.

The function in Excel looks like this:

=MIRR(values, finance_rate, reinvest_rate)

All values are required in this function.

Assuming a WACC of 10%, since the o𓂃utcome is 33%, this project adds value🍨.

Using the Functions in Excel: XIRR

The extended internal rate of return (XIRR) function in Excel assumes irregular payment dates rather than estimates for annual periods. XIRR might be used in projects or investments like♛ mutual funds, which are redeemed and reinvested in at various intervals.

The XIRR function looks like this:

=XIRR(values, dates, [guess])

“Guess” is your best guess at🐲 the return. This is an optionaওl field and doesn’t change the result if it is left out.

In this function, the ꦐdates must be 🧸formatted as a function themselves, or the XIRR function won’t work. For example, April 16, 2023, would be entered in the cell like this:

=DATE(2023,4,16)

Again, th♉is project adds value if the company assumes a WACC of 10% because🤡 it yields 60.72%.

The Bottom Line

Using Excel to calculate the internal rate of return requires the IRR function and reference cells that list the cash flows for a series of periods, plus an optional guess for the rate of return. Excel makes it easier to calculate the IRR of multiple potential investments, allowing you to compare them and choose the one that provides the best return.