Calculating IRR in Excel

The internal rate of return (IRR) is a way to find what discou♏nt rate 🍌would cause the net present value (NPV) of a project to be $0—in other words, to find the highest-yielding project 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 functions in Excel are:

=IRR(values, [guess])

=MIRR(values, finance_rate, reinvest_rate)

=XIRR(values, dates, [guess])

“Guess” is an optional argument, a number you thinkꦗ is close to the IRR. Usually, analysts leave it b𒅌lank.

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 apa🐲rt). Assume a company is assessing th🐲e profitability of Project X. Project 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 anticipating a return later. Each subsequent cash flow co🎃uld be posi𝓡tive or negative—it depends on the estimates of what the project delivers in the future.

Most companies compare the 澳洲幸运5官方开奖结果💎体彩网:weighted average cost o♔f capital (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 considered ꧙a worthwhile investment.

Using the Functions in Excel: MIRR

The 澳洲幸运5官方开奖结果体彩网:modifiღed internal rate of return (MIR𝄹R) is used 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 require𒁃s 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 outcome 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. XꦯIRR 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 optional 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, this 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.