EXCEL - Modeling Interest Coverage into initial Debt / Loan Amount
Hey all,
I am looking at a model from a more senior employee at my company, and am trying to be able to model the same way. This model has to do with a value add real estate property. The big problem I have is that the Sources/Uses contains a "interest coverage" element in which there is a negative levered cash flow for a couple years until the properties NOI is able to cover the debt service. Due to this, the loan acquired has a interest coverage reserve of a couple million to pay off these negative levered cash flow lines for the couple of years needed. This line of levered cash flows is summed to find the total number for the interest coverage needed in the loan that will be acquired. My problem is the circular understanding of this, you need the loan itself to calculate the debt service payments. However, you can't calculate the loan amount (because you need interest coverage) without knowing the net levered cash flow. Anyone have a general concept on how to do this? I know how to use solver to calculate loan fees and total sources/uses in a circular reference way for total loan amount, but have no idea how to calculate this.
I am little confused as to your question, are you asking how the negative operating cash flow creates circularity with sizing the loan or how to calculate/model negative operating cash flow so that its impact is included in the loan amount? You pretty much just explained it.
One of the three constraints a lender will use to size your loan is the percentage of total costs (known as % LTC or loan to cost ratio). So if you project it will cost you $100 to complete your value add work, a bank will typically loan you 65% to 75% of that (so in this case your loan is between $65 to $75 depending on your credit worthiness, guarantees, relationship with lender, etc.). The other two are debt coverage ratio and loan to value ratio (DSCR and % LTV).
The circularity is what you referred to above - the negative operating cash flow is considered a cost, the same as your acquisition price, construction cost, etc. and the lender will limit your loan amount by a percentage of the total cost. The total interest you will pay is based on your loan amount so that creates the circle.
Thanks for response. So I understand that it creates circularity, but I am trying to figure out how to actually model it. I need to know how to calculate/model negative operating cash flow so that is impact is included in the loan amount. For example, this acquisition was a bridge loan for about 3 years to cover CAPEX, then a re-fi with a permanent loan. During the bridge loan period, I want to calculate the negative cash flows into the total uses (as another item as you mentioned below acquisition cost and legal fees etc). This "interest reserve" element in total uses will effect loan amount and equity needed (but the negative interest reserve amount is dependent on the loan itself). Do I just model / link all the cells BEFORE "interest reserve", then put that item in, and it will iterate and adjust?
Enable iterative calculations in excel. You will need to fund the negative NOI and interest that you unable to pay with an interest / operating reserve.
As an alternative, you could structure an unlevered model and then layer in the debt with a solver for the LTC including the interest reserve. There's a good example of this on Adventures in CRE: https://www.adventuresincre.com/construction-draw-schedule-calculate-lo…
I've found that this is a nightmare to work with if you are changing assumptions frequently as it requires to you run the solver anytime a draw schedule changes or a cost changes.
Correct - you will need to track several elements within the model with the goal being to calculate the total interest paid on outstanding project costs via debt before it is covered by net operating income. The elements you will need are primarily in two categories:
Cash flow section
Net operating income
Less: Interest rate payment
Cash flow after debt service
Sources/Uses - different sources and uses of capital
Total project costs excluding financing
Construction Loan (beginning balance, draws, interest, ending balance)
Equity Balance (beginning balance, contributions, distributions and ending balance)
Step by Step
Step 1. Cash flow: I am assuming you have already built in your structure to track your lease up and the corresponding net operating income. Add two lines below, "Debt service" and "CF after debt service" which is NOI less debt service - we'll add these later.
Step 2. Total project costs excluding financing: schedule this out by each period.
Step 3. Equity Balance section: I typically start with Equity Balance since you will pay with all the equity before drawing from the construction loan. Add hard code for equity assumption (say 35% of project costs before financing) and in a separate cell calculate total equity required.
Beginning balance is the previous period's ending balance, ending balance is the sum of the Beginning balance, contributions and distributions for the period.
Distributions will reference cash flow after debt service and will include your refinance/sale scenario (won't cover this here for simplicity).
For Contributions add a dynamic formula to draw out the total equity balance by linking it to Total project costs before financing and the total equity required that you calculated before.
=IF(Beginning Balance = 0, MIN(Total PC during period, Total Equity required - Beginning Balance of period),0)
Step 4: Debt Balance section: Beginning and Ending balances are same as Step 3. Draws you will take Project Costs for the period less Contributions (from the equity section) for the period.
For Interest take your interest rate assumption and apply it to the Beginning Balance for the period.
Step 5: So now go back to your Cash Flow section, pull the Debt Service you just calculated and subtract it from the NOI to get the CF after debt. There should be several sections before NOI starts kicking in where you are paying interest which will be negative.
Now go back to your Project Cost before CF section and add two lines - "Negative CF" and "Interest". Pull the Negative CF from your Cash Flow section and the Interest from your Construction Loan section to calculate the Total Project Costs.
Final Step: Go back to the Equity Balance section. Change your 35% reference from Total Project Costs before Financing to the Total Project Costs. This will create the circular as the Equity and Debt are sizing themselves off of the Project Costs which include financing.
This is one of the most difficult concepts to understand and model so let me know if that is not clear enough, I will attempt to upload a picture of a mini-model I put together demonstrating what it should look like.
Thank you very much for the detailed response. I think I understand it enough now, will try to model one tomorrow. Much appreciated.
Following up since it would not let me post a link since I'm too new:
http://tinypic.com/r/2mxlxfs/9</a">Example Demonstrating Above
Any chance you can reload this image of the interest coverage example? I came across it before and meant to save, it was very helpful visually
Yeah that's the right idea. One thing thought I would not label it "Discounted Cash Flow". You are not discounting any cash flow in that pro forma.
Ratione qui quas consequatur et. Ipsam alias laudantium voluptate quia voluptatibus sunt et. Ea expedita facilis et quo enim quo mollitia cupiditate. Voluptatem quisquam facilis debitis cupiditate.
Molestiae et atque temporibus cupiditate molestiae autem ducimus. Molestiae et beatae cum.
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...