Debt Coverage Ratio
Hi Monkeys,
I was given an assignment for a commercial real estate finance interview on the West Coast. The assignment was to determine if the capitalization request seemed like a good investment opportunity. I've been able calculate the the Cap Rate and Loan to Value, but I am having difficultly with the Debt Coverage Ratio. Hopefully someone will be able to catch what I'm doing wrong.
Capital Request - 50,000,000
In Place NOI - 4,000,000
Cap Rate - 8%
Since the Property Value wasn't provided, I estimated the property value by projecting the NOI growth for 5 years with a growth rate of 3%. I used the Cap Rate of 8% as my discount factor and took the PV of all the NOI. Then I proceeded to take the Reversion Value of my year 5 Cash Flow ($2,922,157.24 /(.08-.03)). I then took the Sum of the PV of NOI and PV of the Reversion Value to get the property value of $78,246,705.65. Lastly, I calculated the Loan to Value which was 63.9%.
-Growth Rate - 3%
-Discount Rate - 8%
-Property Value - $78,246,705.65
-LTV - 63.9%
When I was calculating the Debt Coverage Ratio, I initially used the following function and formula for the monthly senior debt service PMT(12%, 5*12, -42,500,000) and got the monthly payment of $5,105,688.17. I used a similar formula for the monthly mezzanine debt service (THIS IS WRONG). Looking at the financial summary that I had received for the capitalization request, I noticed that I had similar numbers to the debt service so I thought that the calculation was correct. In addition, when I proceeded to calculate the Before Tax Cash Flow and it was positive (the number on the excel sheet are negative because I modified the actual numbers).
As I was rechecking my work, I noticed that I miscalculated the monthly PMT and that it should have been PMT(12%/12, 5*12, -42,500,000). After I recalculated the senior and Mezzanine Debt Service, I found that the NOI would not be able to cover the Annual Debt Service. So now I'm wondering if I did anything wrong. The financial summary for the senior loan and & mezz pref equity specify a term of 5 years and amortization of 30. Given that the NOI cannot cover the Debt Service, I now think that the loan looks really bad unless I calculated something wrong here.
Total Debt - 50,000,000
Senior - 42,500,000
Rate Senior - 12%
Mezzanine - 7,500,000
Rate Mezzanine - 4.33%
Term - 5
Ammortization - 30
If any of you monkeys can help a chimp out, it would be much appreciated!
PS
*Numbers are not actual deal numbers, I modified them from the actual terms
*I have attached an excel sheet with my work so far
| Attachment | Size |
|---|---|
| WSO Loan Assessment.xls 34.5 KB | 34.5 KB |
Okay the senior loan is probably 4.33% and the mezz loan is probably 12%. The mezz is subordinate to senior debt and thus is much riskier. Anyways I would assume its interest only but since I don't see it anywhere we'll ignore it.
Other mistakes you made: 1. to calculate monthly debt service you need to divide the interest rate by 12 2. for number of periods it's a 30 year amortization. So the number of periods is 360 not 60. How it works is that after 5 years of paying either interest only or interest or principal you have a "bullet payment" where you repay the outstanding principal.
When these corrections are made the #s will work it out. so the NOI > Debt service.
I'm also looking at your property value tab and I'm not sure why you discounted your NOI by your cap rate? These are all nominal NOI payments there's no reason why you would discount it? When you're looking at the value for LTV it's the value of the property now so Year 0's NOI/Cap Rate. I'm guessing you made enough edits to the numbers that they no longer make sense.
You're using the word cap rate incorrectly. If you were given a cap rate of 8% and an inplace NOI of 4M your LTV would be 100%. The way you are using it in your excel sheet is a discount rate or required rate of return.
Also your per annum debt service should look like this: =PMT(B4/12,B712,-B2)12
Also I highly suspect your mezz rate and senior rate are flipped because at 12% interest rate your going to have negative leverage at that loan amount.
Thanks Wellplayed and SHB for your responses!
The Senior and Mezzanine Interest Rates were flipped. I changed the number of periods of 360 and the NOI > Debt Service. So happy to have that figured out!
"When you're looking at the value for LTV it's the value of the property now so Year 0's NOI/Cap Rate. I'm guessing you made enough edits to the numbers that they no longer make sense."
Since Cap Rate or Property Value wasn't provided, I thought I could use the lease amount as a proxy for property value. This probably creates a circular reference and makes all estimates a bit off.
I think what I should have done was look for comparable cap rates for properties that produce an NOI similar to $ 4M. Take the NOI divide by comparable cap rate and this will provide the property value. And then you can proceed to calculate the Loan to Value amount with the property value you just calculated.
Is there anything else I should include for a more robust analysis?
You're making progress. In terms of comparable cap rates you need to look at similar asset types not the NOI the property generates. A multifamily property and a warehouse both generating 4MM NOI will have very different cap rates.
I've been able to find similar property types by asset class (office buildings) in the area; I've been looking at buildings with the similar square footage around 100,000-300,000 square feet, but have been unable to find any information about NOI. I've been looking at loopnet.com and showcase.com for property but they only provide information about leasing, nothing about the property's income.
I was thinking that if I was able to find price per square foot for office building in the area, I would be able to figure out the potential rental revenue. But I still would be unable to find property value. I'm going to continue to try to search for office buildings for sale in the area and hopefully will turn up.
Thanks for the help Wellplayed!
look up the research reports for CBRE, CW, JLL and Colliers (NGKF if in NYC metro area) for some basic comps.
I found a report from Colliers. Thanks SHB!
I was wondering if it would be a conflict of interest for a firm to invest in both the Senior and Mezzanine debt of a real estate deal. If that is the case, I think I'm going to have to email the interviewer with an updated analysis of the deal because I assumed the investment would be for both Senior and Mezzanine Debt.
Man I hope that isn't the case.
Blanditiis consequatur ullam sit. Rerum voluptas quo aut fugiat aut nihil. Tempora maxime et illum nam dolorem. Dolorum et minus blanditiis amet necessitatibus soluta. Quia adipisci commodi assumenda necessitatibus id molestias blanditiis. Porro natus ullam quo corrupti eveniet officiis facilis.
Eos ducimus est soluta dolor qui. Voluptas vero est quo magnam. Soluta quae voluptatibus cumque non qui ex doloribus. Doloremque aut qui quo officia aut architecto ut itaque.
Ut eaque expedita enim est accusantium iste. Et praesentium nisi delectus maiores. Sunt magni voluptas qui reprehenderit delectus modi. Voluptas culpa sit vel itaque. Distinctio commodi quia labore expedita repudiandae voluptatem.
Iusto molestiae facilis fuga adipisci accusantium. Aliquam consectetur repellat necessitatibus officiis. Officiis hic nihil qui neque. Est reprehenderit perspiciatis ratione sed voluptatum ad. Et occaecati quisquam quo veniam voluptates laboriosam assumenda. Dolor et sed ut corrupti et. Laudantium delectus vel voluptatem voluptatem veritatis vel ea.
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...