EXCEL - Modeling Interest Coverage into initial Debt / Loan AmountSubscribe
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.