Circular references in LBO models

Can I set up a function about interest rate? for example, since debt principal payment is determined by EBIT and interest expense, I can make x= interest expense, and y=principal payment=a(EBIT-x) . Also make D0 debt balance at year=0 and D1 debt balance at year1, r=interest rate. So x=(D0+D1)r/2=(D0+D0-y)r/2=(D0+a(EBIT-x))*r/2. This is a equation about x. So can we solve x aka interest payment this way? if so, why is it a circular reference?

Iteration. Turn it on - find a setting that works for you (# of tries / precision). (Tools-Options-Calculation iirc, not at the office now and my home computer has '07)

Figured it out 10 minutes after posting aha

I've got iterations enabled... just made a rookie mistake

And my bank is still on 03 as well. Not sure if any BB's have actually made the transition to 07+

iteration would work for this scenario. also fyi you can always use solver (or Goal Seek) to solve linear/convex programming problems, where you need to find inputs that satisfy a certain known outcome.

Manual calculation, F9.

didnt work, it still shows zero for some reason

Your iterations aren't on...

Tools Options Alt+i

set iterations to about 100 usually does it.

Iterations. Very basic setting requirement for modeling. Also make sure to have your basic add-ins for calculations of IRRs and such, although this may differ according to formulas.

Shouldn't a good model avoid circularity?

Sometimes you have to go through and delete problem cells then undo the deletes and it will work. Can be very frustrating. Have spent hours trying to make the circular reference work properly. Try saving then deleting cells like interest expense and other items in the cash flow statement that are tied to cash then crtl+z until it is back to normal.

Nilugee-Jackson:

Thanks for your answers. I've looked at some models, that's actually the case (they take the average balance instead of just the prev. ending balance). I didn't know we were supposed to do it like that.

The logic follows how you would calc DSO and DPO. You take the average of the beg and end balance because interest is being accrued over time. If you use one or the other, you are going to over or under represent actual interest.

Change iterations from automatic to manual.

This works well in lbo models, at least the ones I do...

Replace the offending cells with constants, e.g. the number 5 and hit F9 to refresh until you get numbers replacing the #div's.

Then, go in and replace them with the actual formulas, one-by-one, hitting F9 after each one. Everything should update correctly.

Sometimes with an lbo model I'll just replace the interest expense (which is the circular part obviously) with a constant, get everything looking right and then paste in the formulas and refresh to fix.

And always remember to save down/version up before hitting that sexy f9 button when you are changing something big or checking stuff. woo!

I remember in training they taught you to build a circ toggle to replace interest expense cells with hard values (zero) to fix your div'ed out model. I don't know anyone that actually builds the toggle, though. It's funny, it's obviously more efficient but I always just cut the interest expense, paste it to the side, replace with zeros, F9, then paste it back in. And I know the former pre-MBAs here did it too because it's hilarious - I see 0's to the side of the interest expense lines (outside the print area) in older models every time without fail.

Use this formula, if you have fasttrack it will automatically put them in.

If(iserror(a1/a2),0,a1/a2) will allow circs without your model reffing out.

It's been a little while, but I remember discovering an easier way with the circ interest calc in an lbo model. Often times it works to simply highlight all interest calcs, delete and then ctrl-z. Should refresh correctly.