5g: Source Structure - Circularity

Description
Circularity is the bane of advanced financial modeling. Traditional methods involve:

The 5g method is similar to the VBA UDF method except we don't use VBA; thus, we never get the "SECURITY RISK Microsoft has blocked macros from running..." warning.

Learning Objectives
At the end of this course, learners will know:

Prerequisites
Microsoft's free add-in: Advanced Formula Environment (AFE)

Discussion

This is derived from the 5g basic source structure template. To learn more about the numbers in white, go back to that class. 

Circularity is a complex discussion. A common example is calculating interest on the average between a period's opening debt and closing debt. But the closing debt is the sum of opening debt and interest on the debt. As we add interest, the closing debt gets bigger and the average debt increases which makes the interest of average debt increase. This is an example of circularity. Interest = Opening Debt + AVERAGE(Opening Debt, Opening Debt + Interest) * Interest Rate.

This sort of equation 

The Template

Below is BXL's 5g circularity template. It can be downloaded using AFE from this URL: 

https://gist.github.com/CHatmaker/3e6cba6414fadfabfa3725255da2ba74

NOTE! The circled numbers are NOT part of the code. There are added to mark sections for discussion.

Iterate is a name I gave this LET() step. We could name it anything. I chose this name because this LET() step defines a iterative (recursive) sub function (also known as a Thunk) within the host function: AverageDebtInterestλ

Iterate's first argument is fn. fn is short for function but it is just a name. We could name it anything. I chose fn to remind us that what we put into this argument will be a function's name.

Iterate's remaining arguments are the values this function will need. We will change them as needed for our new function.

This section holds the calculations that Iterate will perform. We will change these to whatever our new function will need.

Delta is the name I use to test for our function's end condition. In this example, we are comparing the interest expense calculated in section 2 to the average debt times the interest rate.

In this step we test if Delta is small enough to consider our calculation is close enough to accept. If so, we return the Interest Expense. If not, see step 5.

This is the part that is most confusing. If Delta is too large, we must repeat this sub function until it is close enough. The name of the sub function is put in fn (see section 1). The name is specifed in step 6

In this step, we identify the name of the function that we must call as Iterate. See step 1. Iterate will call Iterate until Delta is small enough to accept. 

NOTE that the first time we call Iterate, we set AverageDebt to 0; thus, on the first iteration of Iterate, InterestExpense will be zero (0 * Interest Rate = 0). See step 2. We calculate AverageDebt 3 lines down. By the time we reach step 3, AverageDebt * InterestRate will have a value but InterestExpense will still be zero and Delta will be relatively large and step 5 will call Iterate again. 

The second time we call Iterate and pass it AverageDebt, AverageDebt will have some value and thus, InterestExpense will have some value and the delta between InterestExpense and AverageDebt * InterestRate will be smaller.

Each time we call Iterate, the delta shrinks. It typically takes about 8 iterations for function like this to achieve a small enough delta to stop calling itself and return a value. In this case the value is InterestExpense.

Summary

Using this template we can more easily create functions that solve circular references.

Assessment - COMING SOON

Did you get the important points of this section? Take this quiz: 

1) Question 1?

a) Choice a.

b) Choice b.

c) Choice c.


Assessment Answers

1) a  

2) c

3) e

4) a

5) b

6) c

7) d, g, f, a, h, b, i, c, e

8) d

9) c


Navigation