1. Calculation functions
  2. All functions
  3. Financial functions
  4. IRR

The IRR function calculates the internal rate of return for a series of positive and negative transactions. It can be used either with all transactions over a timescale, or with specified transactions on certain dates.

The internal rate of return is the annual rate of return an investment is expected to generate. You could use the IRR function to assess a number of investments and find the most effective investment.

Syntax

The IRR function has two different syntaxes. The syntax that applies depends on whether you use more or less than two arguments with the function.

IRR with time scale

IRR(Cash flow [, Estimate])

IRR with dates

IRR(Cash flow, Dates, Transactions [, Estimate])

Arguments

IRR with time scale

ArgumentData typeDescription
Cash flow (required)Number

A line item that contains a series of positive and negative values that represent cash inflow and outflow.

The line item used for this argument must have a time scale.

EstimateNumber

An estimate of the IRR. This argument uses percentage format, so 0.1 is equal to 10%.

This argument is optional and helps the IRR function to calculate a result more quickly.

IRR with dates

ArgumentData typeDescription
Cash flow (required)Number

A series of positive and negative values that represent cash inflow and outflow.

Must have the list used for the Transactions argument as a dimension.

Dates (required)DateThe date associated with each value of the Cash flow argument.
Transactions (required)ListA list of transactions, which must be a common dimension of the Cash flow and Dates argument.
Estimate of rateNumber

An estimate of the IRR. This argument uses percentage format, so 0.1 is equal to 10%.

This argument is optional and helps the IRR function to calculate a result more quickly.

The IRR function returns a number.

Additional information

How IRR is calculated

IRR is the iterative solution to this equation:

0=Σi=1npi(1+IRR)di/3650 = \Sigma_{i = 1}^{n} \dfrac{p_i}{(1+IRR) ^{d}i/365}

In this equation:

  • N is the number of payments in and out from the start of the first period
  • Pi is the payment in the ith period
  • di is the number of days from the start of the first period

Use IRR with the Users list

You can reference the Users list with the IRR function. However, you cannot reference specific users within the Users list as this is production data, which can change and make your formula invalid.

Constraints 

The Cashflow argument must contain at least one positive and one negative value.

Calculation engine functionality differences

Most financial functions are unavailable in Polaris, but you can use FV, IPMT, NPER, PPMT, and PV. Learn more about the differences between Anaplan calculation engines.

Excel equivalent

Related Anaplan functions

Examples

Example of IRR with time scale

This example uses two modules. The first module, Annual cash flow, contains Cash flow line item with a Time Scale of Year. The Cash flow line item contains both a positive and negative value.


FY20FY21FY22
Cash flow-100,0000130,000

The second module contains a single Percent format line item, which contains a formula that uses IRR with the Cash flow line item from the first module. As the IRR function returns a single value, the result does not need a time dimension.



IRR of annual cash flow

IRR('Annual cash flow'.Cash flow)

14.0%

Example of IRR with dates

In this example, two modules are used. One source module, Plant Transaction Data, is dimensioned by the Transactions and Plants lists. Transactions is on rows, and Plants is on pages, with Plant 1 selected.

The Plant Transaction Data module contains the values of transactions, dates of transactions, and a description for some transactions.


DescriptionCash flowDate
Transaction 01Land purchase-100,0001/1/2011
Transaction 02Turbine purchase-850,0001/1/2012
Transaction 03Energy generation revenue200,0001/3/2013
Transaction 04Energy generation revenue200,0001/3/2014
Transaction 05Energy generation revenue200,0001/3/2015
Transaction 06Energy generation revenue200,0001/3/2016
Transaction 07Energy generation revenue200,0001/3/2017
Transaction 08Energy generation revenue200,0001/3/2018
Transaction 09Energy generation revenue200,0001/3/2019
Transaction 10Energy generation revenue200,0001/3/2020
Transaction 11Energy generation revenue200,0001/3/2021
Transaction 12Energy generation revenue200,0001/3/2022
Transaction 13Energy generation revenue200,0001/3/2023
Transaction 14Energy generation revenue200,0001/3/2024
Transaction 15Energy generation revenue200,0001/3/2025
Transaction 16Final energy generation revenue200,0001/3/2026
Total
1,850,000

The second module uses the data from the Plant Transaction Data module with the IRR function to calculate the internal rate of return for each plant. The column for Plant 1 contains the internal rate of return for data displayed in the Plant Transaction Data module.


Plant 1Plant 2Plant 3Plant 4

Internal Return Rate for Plant

IRR('Plant Transaction Data'.Cash flow, 'Plant Transactions Data'.Date, Transactions, 0.1)

18.1%12.2%13.8%9.2%

Disclaimer

We may update our documentation occasionally, but will only do so in a way that does not negatively affect the features and functionality of the Anaplan service.