Circular Reference in LBO Model
Undergraduate student here working on replicating the illustrative LBO Model in Rosenbaum and Pearl's Investment Banking book as practice. I'm having issues with the circular references - the specific section laid out below (I'm sure most of you know what I'm referring to, but just want to be clear)
Net Income on Income Statement --> first line of Operating Activities on Cash Flow Statement Operating & Investing Activities on Cash Flow Statement --> Cash Available for Debt Repayment on Debt Schedule Cash Available for Debt Repayment on Debt Schedule --> Affects Beginning and Ending Balance of Term Loan B on Debt Schedule Beginning and Ending Balance of TLB on Debt Schedule --> Interest Expense (Interest Rate * Average Balance of Term Loan During Period) on Debt Schedule Interest Expense on Debt Schedule --> Interest Expense on Income Statement
I've gotten to calculating the interest expenses back to the income statement, but whenever I complete the circular reference, I'm getting the circular reference error that stops Excel from calculating. (Debt repayment schedule is extremely accelerated as of right now due to inflated cash available for debt repayment - because of the inflated Net Income that does not reflect interest expenses yet).
Is there a certain order of steps to build out the model in order to have the formulas in the model calculated correctly? Or do I need to avoid the circular reference some way?
Thanks, thought it'd be smart to get familiar with issues like this during a college winter break instead of at 2:30 am at some point next year.
You're doing it right if you get circularity issues due to interest expenses. This is exactly what should happen.
First, check that circularities are enabled in your Excel (Excel 2007>Office Button>Options> Formulas Tab> Set Iterations to 100). You can then create a circularity toggle in your model to flush out errors. Multiply the interest expense line by a fixed input cell which can take values 0 or 1. "Break" the circularity by inputting 0 and then 1.
Another circularity issue you might come across someday is when using an unlevered/relevered beta in DCFs. While most textbooks just use the market cap as a proxy for Equity to delever/relever the beta, a more proper way of solving it should use the Equity Value which results from the DCF, which itself depends on the WACC and thus the beta.
Labore enim voluptate et explicabo et. Illo quam provident ut omnis. Assumenda in cum totam sequi suscipit dignissimos delectus.
Autem ipsum qui deserunt ut omnis recusandae. Optio ipsam sunt sint.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...