IDSB Banner

Using Circular References with Quantrix

Often financial professionals utilize circular references in their financial models. This modeler note shows you how to control the circular reference calculation.

A simple circular reference example is computing state and federal tax. To compute the state tax you start with federal taxable income. But how do you know your federal taxable income until you've calculated your state tax? How can you calculate your state tax until you know your taxable income? This is when circular references are needed.

Quantrix indicates a circular reference is present by placing a yellow circle next to the matrix name in the model browser. Additionally, each formula that is involved in a circular reference has a yellow notification diamond along with wording indicating that a circular reference is involved.

 How do you control the calculation event once the system has identified circular reference? This is handled through the use of the calculation settings panel: Choose Tools --> Calculation --> Calculation Settings from the Quantrix menu. There are two settings, 'Maximum Iterations' and 'Value change less than'. When calculation is initiated in the model, the system will calculate until it reaches an answer (calculation value change is less than what is set) or until it reaches the number of maximum iterations set. In this example, the maximum iterations is set to 1 so you can see how the circular reference is resolved step-by-step.

To see the circular reference in action, change the taxable income to $40,000. Note that the total tax is $15,522, but that isn’t quite the final answer. You need to calculate the model a few more times to zero-in on the correct value. Press F9 (shortcut key to calculate the model) and you will see the model change the total tax line to a new calculated value. Keep pressing F9 until the tax lines no longer change. Quantrix used the circular reference functionality to zero-in on the answer of $12,222. Of course if you set the maximum iterations to a higher number, only one calculation event would be needed to resolve this circular reference. Circular references are an effective way to derive answers from calculations that depend on each other.

>>  Circular Reference Model

About Quantrix - Contact - Site Map - Refer A Friend - Support
Solutions - Products - Services - Community - Partners - Webinars - Learning - Store
© Copyright 2014 Subx Inc.-All rights reserved -+1.207.775.0808 -