Calculates the Internal Rate of Return (IRR) of an investment using a sequence of periodic cash flows (time-dimensioned) or dated cash flows.

IRR chooses the appropriate timescale based on the cash flow time period. For example, daily, monthly, quarterly, etc. Cash flows at the week timescale are treated as if they happened on the first day of the week. And cash flows at the day timescale are treated as if they happened on that day.

Syntax

IRR with time dimension

IRR (Cash flows[, Estimate])

IRR with dates

IRR (Cash flows, Dates, Transaction list[, Estimate])

Arguments

IRR with time dimension

ArgumentData typeDescription
Cash flows (required)Number

A line item showing cash inflows and outflows.

This line item must have a time dimension.

Estimate (optional)Number

An estimate discount rate of the IRR.

Note: Enter the rate as a decimal. For example, 0.1 for 10%.

Defaults to 0.1 if omitted.

IRR with dates

ArgumentData typeDescription
Cash flows (required)Number

A line item showing cash inflows and outflows.

This line item must have the list used for the Transaction list argument as a dimension.

Dates (required)DateThe dates associated with each value of the Cash flows argument.
Transaction list (required)ListA list of transaction identifiers, which must be a common dimension of the Cash flows and Dates argument.
Estimate (optional)Number

An estimate discount rate of the IRR.

Note: Enter the rate as a decimal. For example, 0.1 for 10%.

Defaults to 0.1 if omitted.

IRR returns a number.

Calculation engine functionality differences

BehaviorClassicPolaris
First cash flow selection
(Dates variant only)		Processes cash flows sequentially based on the order of items in the Transaction list. If transactions are out of order, some cash flows may be misindexed, leading to unexpected calculation results.Automatically evaluates cash flows in the correct order based on the Dates line item, regardless of items in the Transaction list.
Scaling factor (kT)
(Time dimension variant only)		Determined strictly by the timescale of the Cash flows line item, regardless of the actual structure of that time dimension.

Calculated dynamically as the average number of leaf periods in the Cash flows timescale per fiscal year.

Note: If using the Weeks: General calendar type (no fiscal years), weekly cash flows are converted to daily transactions using a fixed 365 scaling factor, matching Classic.

Estimate is NaNResult is undefined.Returns NaN.
All cash flows are zeroReturns Not a Number (NaN) if all cash flow values are strictly zero.Returns 0 if all cash flow values are tolerantly equal to zero.
Blank values
(Dates variant only)		Evaluates blank dates as 12/31/1899 (one day before the start of the Anaplan era).Returns NaN, regardless of cash flow values.
Target line item dimensionalityCan be dimensioned by Transaction list.

The target line item can’t be dimensioned by Transaction list.

For the time dimension variant, the target line item also can’t contain a time dimension.

Transaction list is empty
(Dates variant only)		Returns NaN.Returns 0 by default. If the Estimate argument evaluates to NaN, it returns NaN.

Additional information

IRR has two syntaxes. One uses cash flows with a time dimension. The other uses pairs of dates and cash flows, like Excel’s XIRR. 

IRR calculation with time dimension

0=NPV=∑i=0nCi(1+ρ)ikT\hspace{10 mm} 0 = \text{NPV} = \displaystyle\sum_{i=0}^{n}\dfrac{C_i}{(1+\rho)^{ik_T}}

IRR calculation with dates

0=NPV=∑i=0nCi(1+ρ)di/365\hspace{10 mm} 0 = \text{NPV} = \displaystyle\sum_{i = 0}^{n}\dfrac{C_i}{(1+\rho)^{d_i/{365}}}

where

  • n is the total number of cash flows.
  • Ci is the cash flow at time i.
  • ρ is the discount rate.
  • kT is the scaling factor based on ‌time granularity.
  • di is the number of days between the i-th transaction and the first transaction.

IRR scales automatically based on the given time granularity and selects the right scaling factor, kT. You can ‌calculate IRR at your investment’s original timescale or compare IRR values across different time granularities without any extra calculations. See how the scaling factor is calculated for each case.

Calendar typeClassicPolaris
Fiscal years

Based on the timescale of the Cash flows time dimension:

  • 1 for years
  • 2 for half years
  • 4 for quarters
  • 12 for months
  • 365 for weeks
  • 365 for days
Calculated dynamically as the average number of leaf periods in the Cash flows timescale per fiscal year.
General weeks

365 for weeks.

Weekly transactions are treated like daily transactions that happen on the first day of the week.

365 for weeks.

Weekly transactions are treated like daily transactions that happen on the first day of the week.

Using IRR with production lists

Using a production list like the Users list as a Transaction list can give unexpected results as these lists change over time.

Constraints 

  • In Polaris, the dates variant can’t be used if the target module has a dimension that is related to Transaction list.
  • IRR accepts any cash flow sequence. But if the Cash flows argument doesn’t have a positive and negative value, the equation has no solution. In that case, IRR returns NaN, unless ‌you use Polaris and all cash flows are 0.

Excel equivalent

These are the differences between ‌Excel’s IRR and Anaplan’s IRR:

  • Excel’s IRR function assumes cash flows are equally spaced and fixes the time scaling factor kT = 1, regardless of the actual time intervals between them. This function matches Anaplan’s time dimension variant of IRR when the Cash flows line item ‌has its timescale set to Years, and only in that case.
  • Excel’s XIRR function accepts actual dates and always uses kT ​= 1/365, assuming daily compounding. It always returns an annualized IRR​ of the sequence of cash flows. This makes it easier to compare the IRR of different cash flow sequences, but requires the user to perform extra calculations to derive the appropriate transaction dates when the cash flows are given at a time granularity other than days.
  • Anaplan’s IRR function returns an annual equivalent rate by automatically choosing the appropriate kT based on the timescale of the Cash flows line item, for example, daily, monthly, quarterly. Refer to the table above to see how the scaling factor kT is determined for each case. This enables the users to compare the resulting IRR values without further conversions.

Polaris has a stricter verification threshold to ensure the reliability and comparability of IRR results in financial analysis. In theory, Polaris may return NaN for IRR more often than Classic or Excel. But in practice, this rarely happens, and only when very small changes in the IRR result in unusually large changes in the NPV, typically when the IRR is very close to −1. This approach helps improve the trustworthiness and reliability of Polaris compared to Classic or Excel.

Examples