Do you really need that circular reference?

If you have ever built a financial model, you would have most likely come across a situation of circular reference. What is your view? Should you use a circular reference or should you avoid it? And in either case how can you be sure about the accuracy of results? This is what we are precisely trying to address in this piece.

For starters, issue of circular reference arises when two or more cells in a spreadsheet are mutually dependent on each other.

In my career, I have come across several users -- sometimes my team members, sometimes my clients and, at other times, participants in my training program -- who tell me that they have line items which can be modeled only using circular reference. But as I see it, most users who feel compelled to use circular reference do it because of their over dependence on spreadsheet over basic high school mathematics.

Revisit your high school algebra

Most of us in finance have learnt algebra and know how to solve equation by substitution. And a lot of circular reference problems can be avoided by applying the same. Let me explain this with an example of a recent discussion with a client about her model.

The client had a fixed budget for acquiring a company. However, her budget covered not just the purchase consideration but also the transaction cost. Therefore, the amount she could allocate towards purchase consideration was total budget minus transaction cost. Her problem was that while purchase consideration was dependent on transaction cost, transaction cost was charged as a percentage of purchase consideration. In her view, this created a circular reference.

You can understand it better by looking at the following equations the client had used in her model.

Purchase consideration = Total outflow – transaction cost                      (Eq. 1)

Transaction cost = Purchase consideration * Rate of brokerage              (Eq. 2)      

 But let us look at the two equations and try to solve it using the substitution method. If we substitute Eq.2 in Eq.1 we would get the following equation:

Purchase consideration = Total outflow – Purchase consideration * Rate of brokerage

Solving it further, we can easily come up with this equation which is free of circular reference:

Purchase consideration = Total outflow/(1+Rate of brokerage)

If I recollect correctly, this used to be a standard exam question to us during first year in junior college. However, for some reason I notice that several users have become so over dependent on spreadsheets that they ignore the basics.

Then what is the purpose of circular reference?

As we argued, circular reference are not ideal for solving inter-dependent equations. But it can be very useful for calculations involving trial and error. For example, in the screen shot below, I have shown how to calculate IRR using circular reference (Note: MS Excel has inbuilt IRR function and you do not need to use this approach)

As you can see in the above screen shot we are using trial and error to adjust IRR if NPV in the previous trial is not zero.

(Personally, even in such cases I would recommend exploring existing excel functions or, if you are good with VBA, writing a function of your own rather than use circular reference)

A word of caution though

While I certainly recommend using your mathematical skills to come up with refined equations that avoid circular reference, there are situations where the effort may not be worth the benefit. A classical example is interest income (which is depend on cash, and cash is in turn dependent on income). If we tried to come up with alternative equation, we would be dealing with way too many variables. But even in such cases, we would recommend against using circular reference. Rather, one may perhaps try an alternative equation that may only be an approximation but with an insignificant degree of error. For example, we may model interest income as a percentage of opening balance of investment, at least in cases where investment income is not a key line item. While this may not give us an accurate result, the difference is least likely to be material.