Spencer Burton Hi there, anyone know why in the actual excel file the LTC % in cell C25 changed to a formula instead of an input. During the video I saw when 60% was typed in cell C25 but the actual file says C25/C22 which is just loan/total project cost. This is confusing me to me as there would be no way to know before hand that dividing the loan by total project cost would yield 60% as the LTC. And if we divided an empty cell by total project cost it gives back 100%. Can someone help please. Everything else is fine
excellent content , i could not believe that the exercise file had an option pay what ever you want to and even free . May God reward you for this deed
These modelling exercises you're doing are superb ! One thing I wondered about was the timing of the TI and LC payments stipulated in the problems assumptions. It would seem to have made more sense to start the tenant improvements 6 months before the tenant moves in while waiting to pay the leasing commissions the day of move in rather than the other way around.
Hi Spencer Firstly thanks for this walkthrough model - very useful. I had a question on the S&U part of the downloaded excel (v1.3) file, specifically cell D25 (LTC) - this should be a 60% hardcode assumption. Why is this taking the loan / total project costs? This creates a circularity with cell C25? Let me know if I am missing something
Great video!!! Extremely useful "Watch me" series. Seems there is circular reference in the calculation of the construction interest, perhaps you can fix it.
Hi, thanks for providing much needed knowledge, could u please resolve this issue, as i have prepared the spreadsheet as mentioned in the video by u, but when i go back to specific cell containing formula of calculation, it disappear the result and show 0 or dash instead of the outcome. Although when i downloaded ur sheet too, same thing happening, hope if u resolve this error, thanks again.
Incredibly useful, not sure how you only have that many views, everyone in the field should use this if not for a little review and refresher. But thank you for putting this together.
Thank you very much for this. I just have a question. I don't understand why to use the IF formula and then to use the * method. Potentially I could use a IF formula for Vacancy - right?
Could have made expense reimbursement much simpler - just set an if sttaement =if(x>0) to the operating expenses instead of linking it to mutiple lines . feel free to correct if im wrong thanks for a great video
Hello to all, I have a question regarding the structure of the exercise. Wouldn't be correct if the the loan was calculated after the unlevered CF? I mean that shouldn't the loan be calculated by adding the unlevered cashflow's shortfalls and not the total costs? The purpose of the loan isn't to cover the lack of funding ? I mean that the structure that you demonstrate is i)Costs -> ii)Loan structure->iii)revenue->iv) Unlevered cf ->... Whereas the way i propose is i)Costs -> ii)revenue->iii) Unlevered cf ->iv)Loan structure-> v) levered cf ->...
Hi Spencer, Thank you for the video and notes - it is beyond helpful! Quick question: I'm curious to know how you would calculate Asset Management Fees (% p.a. gross asset value) in this model. I have a very similar example at hand, and asset management and acquisition fees were the only difference. Hope to hear from you soon. Thanks, L
Hi Spencer - I would be interested to see how you would take on a phased high rise development on (2 phases)..for example, I have concluded the necessary Capex of the entire development, estimated the construction cost per gross construction areas. But, now to phase it so it affords the investor time to recover from the construction debt (hard costs) from the proceeds and closing of the mother bond is another story? How would you take this on?
Would you do a video about how someone would have done this in 1977? Did people just write all the stuff out? It would be cooler to figure out how people did it in 1877. Pre calculator; this stuff is crazy.
Thanks for the great video Spencer. Deeply appreciated if you could answer two quick questions, 1) is it correct to have the construction interest even after the end of construction period (from month 25 to 27)? 2) For the debt services on row 52, is it correct to take the total loan on cell 25 instead of the loan balance in the particular month (row 29)?
Glad it was helpful. And yes, it's common for construction interest to accrue beyond the construction period during the lease-up period where there is insufficient operating cash flow to cover the interest expense.
@@adventuresincre Understood. Thank you very much for the prompt reply. Could you please kindly answer the 2nd question as well? Thanks a lot in advance.
@@jeonjay5710 - As it relates to Q2. That's a good catch. The current equation assumes the loan has fully drawn upon operating breakeven, and is correct when that is the case. But since there's a quirk in this particular case where the asset hits breakeven before the loan is fully drawn, the current equation misses two short months (months 28 and 29). I'll update the source file to calculate debt service from the row 29 rather than cell C25.
Hi Spenser - So helpful and thank you - one question I have and I'm hoping its simple... If I want to increase rent annually on a $ basis, how would i change my model? Starting at $43 sq ft and going up $1 annually for example. Thank you in advance
Hi Spencer! The Equity 42,941,653 (C26) is 400k less than GP+LP contributions 43,341,653 (D84). Could you please comment on this. Thx EDIT: NVM, found the 400k. Operating Loss of 400k (month 25-27) did not flow up/capitalized into Sources.
Hi Spencer, Thanks for the tutorial. Just one question on the required return for LP based on 12% rate. Why is the formula (1+annual rate )^(1/n) -1, n being the number of months. Thanks!
in minute 13.27 can you explain how to lock in the column/row? I can use Fn F4 but it's not clear how to input the formula with shortcut. Your help would be greatly appreciated.
Got my spreadsheet off course at 23:10. Once I plug in the construction interest, all my calculations become off by a small amount, with my Month 8 balance being $5,356, Month 9 being $17,407, and the rest of the numbers being messed up. Not sure why this is the case. Been trying to work it out for hours -- all my other calculations are identical.
Hey, I am having difficulty getting the same loan balance around the 22:20 minute mark. Redid the spreadsheet twice now and still having same issue. Please advise, thanks!
Hi, thank you so much for the video. I have a question, regarding the debt, why are we linking to break even month instead of operation start month? I believe we should link it to Operation start month because we are paying interest anyway although we have not reached the break-even point yet. Anyone comment please! Thanks in advance.
My calculations start messing up when calculating Const. Interest around 22:50. I've played around with doing iterative and non iterative calculations and things aren't adding up correctly. Can anyone please advise?
same problem. Not sure if we are supposed to get some deviation based on the order Excel calculates between different version of the spreadsheet. Anyone have a clue?
Greetings to all, i need your help if you are kind enough. i can see that the model includes interest payments for the loan but i can't see where it counts for the principal of loan . can you please highlight what i am not getting? Thank you
Great question! We created a free Excel add-in that adds numerous keyboard shortcuts, including the ability to automatically turn cell font blue. It's called 'Excel 4 CRE': www.adventuresincre.com/custom-excel-addin-for-real-estate/
I´d say that there is an error in Row 25 (Loan) calculation because you have taken TOTAL COST BEFORE INTEREST to substract EQUITY, instead of using TOTAL PROJECT COST (Incl.INT.).
Shushu - you are correct. I actually corrected the error later in the video but wasn't clear about it. You can find a further explanation of this, and a few other clarifications, in the video description. Thanks for commenting!
It looks like Construction Interest (row 21) has circular reference errors, my model won't calculate these formulas because of this. I want to make sure I'm calculating all this correctly. By any chance would you be able to walk through how to fix the ref error? Or what the calc should be?
Shouldn't the monthly loan pull from the "total before interest" instead on the "total project costs"? It seems to be double counting the interest the way you currently have it since we are getting the interest from the loan.
Because project is getting stabilised only on month 30 . add to that the assumption for hold period - 5 years or 60 months. Total timeline - 90 months.
Thanks for pointing this out. I'm not sure what I'd done, but the formula in C21 that sums all Construction Interest for the entire development period [=SUM(G21:CR21)] got zeroed out somehow. I didn't realize this had happened until the 1:00:00 minute mark, when I saw my Levered Cash Flow line had equity contributions beyond when I'd expect. Adding the formul in C21 back, corrected the issue.
Appreciate the feedback. The video was recorded at 1080p, but sometimes (depending on your internet speed or video player settings), the video will display at a lower resolution. If you're having a tough time seeing the screen, try clicking the 'Gear' icon in the lower right hand corner of the video, and setting the "Quality" to at least 720p.
Hi Spenser - So helpful and thank you - one question I have and I'm hoping its simple... If I want to increase rent annually on a $ basis, how would i change my model? Starting at $43 sq ft and going up $1 annually for example. Thank you in advance
Thats helpful but I'm still confused - Can you show me as you did with the last sentence how the math or the formula would work if i cahnged the increase to $2 dollars... I guess what I'm missing is where do i link the Ann Dollar Increase cell to that equation.?
Hi Spenser - So helpful and thank you - one question I have and I'm hoping its simple... If I want to increase rent annually on a $ basis, how would i change my model? Starting at $43 sq ft and going up $1 annually for example. Thank you in advance
