Solving IRR in Microsoft Excel

An IRR calculation with cash flows at irregular intervals can be modeled in Microsoft Excel. See the following figure.

The Excel Goal Seek or Solver tools can be used to iteratively solve for the IRR. See the following figure.

Many techniques have been developed to reduce the number of iterations necessary to solve for the IRR, with Microsoft Excel using a proprietary method developed by Frontline Systems.

A limitation in the model exists wherein the power function returns an error when a return less than -100 percent is raised exponentially. As a result, Excel cannot calculate IRRs less than 100 percent using this model.

This is shown in the following figure.

Open