This method can destroy models and is dangerous to use. Use the "Enable Iterative Calculation" button in Excel (Ihe iteration button)
Because there are big problems with the macro iteration button method and the copy and paste macro method, this chapter describes how to create a flexible and transparent function using one of the last two methods - the algebraic approach or the isolated function. The table shows five different techniques along with comments regarding the problems with the method. Some of the techniques that can be used to solve circular references such as the problem of interest expense on the average debt balance are listed below. For many advanced modelling issues, creating your own user defined functions opens a whole new world of solving problems that otherwise use things like tedious copy and paste macros.
While the problem of more accurately measuring interest expense may be trivial to most people and is certainly not a key driver of virtually any analysis (it is fine to use the opening balance in general), solving the problem illustrates elegant ways in which functions can be used to solve problems in financial models. This is an important issue to some people because in a model with items computed on an annual basis, cash flow after interest expense affects debt balance, but the debt balance affects interest expense which in turn affects cash flow.
In those cases you might need to modify your comparison macro to run via timer or, perhaps, simply use the circular comparison formula described earlier.Chapter 12: Advanced Issues in Corporate and Acquisition Modelling Involving Resolving Circular References Associated with Interest Expense and Debt Repayments Circular References in Corporate and Acquisition Models One issue that not too many people in the world care about is coming up with an elegant way to compute the interest expense on the average of opening and closing debt balance rather than simply the opening balance in financial models without using the iteration button in excel and without using a macro. It will not be triggered if the value in B1 is updated via macro or if it is updated by virtue of being linked to a source external to the worksheet. Note that this event handler will be triggered if cell B1 is changed explicitly (someone enters a new value in B1) or implicitly (the value in B1 is updated when the worksheet is recalculated). If it is greater, then events are temporarily turned off and the value in B2 is updated. Private Sub Worksheet_Change(ByVal Target As Range)Įvery time cell B1 changes, the macro compares B1 to B2.
The following should be placed in the worksheet's code window: If your data is not being updated by a macro, then you could create an event handler to do the updating.
If B1 is greater, then the macro can update the value in B2. If your worksheet data is already being updated via macro, then you could simply modify the macro to recalculate the worksheet and then compare what is in B1 to B2. If you cannot use iterative calculations for some reason (perhaps because of other formulas in your worksheet), then you may want to use a macro to do the updating of cell B2. The result is that B2 is updated only if the value in B1 is not an error and it is greater than what is currently in B2. If you think that, at some point, B1 might contain an error, then you could modify your formula in cell B2 just a bit: The formula will work just fine, provided cell B1 never displays an error.
If you are using Excel 2007, the steps are a bit different-choose Tools | Options | Calculation tab and make sure the Iteration check box is selected. (A good, safe value might be something like 1000.)
Eric would like to have cell B2 show the maximum value that B1 has ever reached. This sum updates once per minute as the data on which it is based is updated. Say cell B1 is the total value at the current time. Eric has a worksheet that updates portfolio values every minute.