Skip to main content
Documentation
Documentation
Adaptive Planning
Last Updated: 2025-04-18
Concept: IRR Formula Function

Concept: IRR Formula Function

IRR Definition and Method

IRR is a formula function that calculates the internal rate of return on investments. Example: assets, loans, properties, and so on. The IRR is a discount rate that makes the net present value (NPV) of all cash flows equal to zero.
Use cases for calculating IRR:
  • You take out a loan and receive a lump sum of cash. You know you will make consistent payments over a specified amount of time. Example: The loan amount is $100,000, to be paid monthly over 3 years (36 periods) at an amount of $3500 per month. The resulting IRR is 1.31%.
  • You start a business requiring upfront capital in exchange for expected future cash flows. Given the present value, the number of periods, and the expected cashflows, the IRR formula provides the rate of return.
IRR is not a straightforward formula. IRR is a trial and error goal-seek function that iterates to converge on the appropriate value. Adaptive uses the Newton–Raphson method to calculate IRR. You can learn more about this method on the internet.

IRR Requirements for Adaptive Planning

To use the IRR function in your model, the life of the investment must not exceed the time range of the version. The reason for this limitation is that the Adaptive Planning IRR function requires that you sequentially enter all cashflows that are related to the investment.
The components required for IRR formulas include:
  • Cashflow account or row.
  • Time range that specifies the periods to reference, which could be a series of accounts or rows. The best practice is to specify the time range of a single cashflow account, instead of selecting individual accounts or rows as a time period argument.
  • IRR estimate, which is your best guess to what the IRR should be. The estimate guides the convergence toward the correct IRR when there are more than 1 correct points.

Formula Arguments with the IRR Function

IRR formulas have arguments that you separate by commas. For IRR, arguments can be accounts, numbers, or function-specific variables. You can set up IRR formulas with single arguments or multiple arguments.
Argument
Example
Single
IRR(Argument 1)
Multiple
IRR(Argument 1, Argument 2, Argument 3)

Single Argument IRR Formula

Single arguments require a single cashflow with a time range. You don’t need to enter an estimated IRR because single argument IRR formulas assume that the estimated IRR is 0.1.
Example: IRR(ROW.Cashflow[time=this:this+36])
Explanation of the formula’s components:
  • In the parenthesis after the IRR function, the formula refers to a row, called Cashflow, in a modeled sheet.
  • We use a time modifier for the cashflow row, which specifies to reference the period of the row plus 36 time periods.
  • We don’t need to enter an IRR estimate. In single argument IRR formulas, the IRR estimate is always 0.1.

Specifying the IRR Estimate

To specify your own IRR estimate, you can add an argument. You must always add the IRR estimate as the last argument in the formula.
Example: IRR(ROW.Cashflow[time=this:this+36], 0.2)
Explanation of the formulas components.
  • We added our own IRR estimate, separated from the first argument with a comma.
  • The formula now has 2 arguments, with the final one being the IRR estimate of 0.2.

Multiple Arguments for IRR Formulas

You can also create multiple arguments for several cashflow inputs, rather than 1. In general, for IRR formulas, you want to include all the related cashflows. In these cases, be sure to add the IRR estimate as the final argument. Otherwise, the last cashflow you reference acts like the IRR estimate.
Example: IRR(ROW.CashFlow1, ROWCashFlow2, ROW.CashFlow3, 0.1)
Explanation of the formula components:
  • Multiple arguments contain multiple cashflows. In this case, the calculation covers 3 periods, represented by the 3 separate cashflow rows.
  • For the 4th argument we enter 0.1 as the IRR estimate. If you fail to enter an estimate for multiple arguments, the last cashflow value becomes the IRR estimate.

The IRR Estimate Variable

There can be more than 1 mathematically correct IRR. The IRR function attempts to find a discount rate that results in a Net Present Value (NPV) of 0. These equations typically produce a non-linear graph. The graph generally has a peak with a mathematically correct value on both sides of that peak. The IRR estimate helps the function converge on the mathematically correct value that is most appropriate.
We use 0.1 for the IRR estimate in single argument formulas. 0.1 is a good guess for the internal rate of a return, assuming it’s a good investment. However, this will not always be the best assumption for your formula, especially when the anticipated IRR is negative.
When the formula returns a 0 or a nonsensical value, try a different estimated IRR to help the formula converge on the appropriate value.

Differences between Workday and Excel IRR

Behavior or Feature
Adaptive Planning
Excel
Number of iterations
Workday interates 600 times. When Workday can’t find a result, we return a 0. You can then try a new IRR estimate.
Excel iterates 20 times until it finds a result accurate within a .00001 percent. When Excel can’t find a result within this accuracy range, it returns #NUM! error. You can then try a new IRR estimate.
Assumption for IRR Estimate
0.1 for single arguments. User-specified for multiple arguments.
0.1 for single arguments. User-specified for multiple arguments.
IRR Method
Newton–Raphson Method.
Excel doesn’t explicitly state which method they use.*
* You might get different results in Excel. We have seen cases where given the same variables, Adaptive Planning produces a value of 0, and Excel produces a non-0 value. We can confirm that the numbers produced in Adaptive Planning are mathematically correct using the Newton-Raphson Method, but we cannot confirm the method Excel is using, nor the mathematical correctness of the results.