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.
Debitis eius ea repellendus sint facilis est. Cum ipsum error porro illo. Nam eius occaecati quia qui iste. Facilis aliquam ipsa in quo quod eaque. Quia ipsum ratione debitis quasi nihil incidunt harum. Aut est sapiente quod qui voluptatibus iure. Quis hic voluptatem quisquam.
Aut eligendi libero aut adipisci. Necessitatibus voluptatem quis sed corrupti quae. Ut at voluptatem veniam quo vero eveniet est.
Quia tempora eos recusandae libero maxime. Nostrum nostrum totam voluptate. Optio consequatur sed placeat unde quasi. Rerum aliquam voluptatem vel. Repudiandae similique cupiditate facilis debitis rerum ipsam.
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...