Circular Reference - IRR

ntnminh
ntnminh
in Modeling

Hi All,

I've encountered Circular Reference in a calculation that involves IRR formula, for which I think is an illogical treatment of Anaplan. Please could you give some advice:

The calculation is as following:

Relation 1: Operating Results (ORt) = Operating Income (OIt) x Adjustment Factor (AFt)

Relation 2: Adjustment Factor (AFt) depends on Rate of Return of previous period (IRR t-1). 

IF IRRt-1 < 15% THEN AFt = 50%

IF 15% <= IRRt-1 < 25% THEN AFt = 50% - (50%-12.5%)* (IRRt-1 - 15%)/(25%-15%)

IF IRRt-1 >= 25% THEN AFt = 12.5%

Relation 3: Rate of Return (IRRt) = IRR (Operating Results (ORt))

 

As such, Operating Results of, say 2025, is calculated with Adjustment Factor of 2025, which is calculated with IRR of 2024 (which in turns based on Operating Results 2024)

This is completely normal relation and it is demonstrated well with Excel.

But Anaplan is giving a Circular Reference each time I tried to link the Relation 3 (after building the Relation 1 & 2)

 

I am looking forward for any advice or experience sharing related to the matter.

Thanks & Cheers,

Minh

Tagged:

Answers

  • nathan_rudman
    nathan_rudman

    use PREVIOUS() to avoid circular references (in most cases).

     

    In your case you would probably have to do PREVIOUS(IRR)

  • ntnminh
    ntnminh

    Hi Nathan,

    Thank you for your reply. 

    I did use PREVIOUS() in Relation 2, since AFt is linked to IRR t-1, which is the only place where the calculation needs to refer to past period.

    I think it has something to do with the nature of function IRR programing in Anaplan. Other than that, I dont find any reason why there's a Circular Reference in this situation.

    Cheers,

    Minh

Categories