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.
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 ->...
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 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
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.
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
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?
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.
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?
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, 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, 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.
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.
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!
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/
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?
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
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.
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 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!
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.
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.
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
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
See the Notes section of the Video Description for corrections/clarifications.
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
These "watch me mode"l videos are amazing! This one and the multifamily. More of this!!!!!
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
None of your videos are any bit boring. All awe-inspiring stuff. You have changed covers (thumbnail) of all the notebooks. Thumbs up.
I have no idea why I'm watching this but it's curiously interesting. [And I have no background in real estate or financial modeling]
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.
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 ->...
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 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
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.
they just built shit lol
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
your laughter at 1:07:07 is awesome. God bless you. Thank you for this amazing knowledge share.
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.
Hi Spencer, I'm wondering how we could assume breakeven as Month25? Could you elaborate more on this? Thank you
Nothing but the best in these videos. Thank you
Thank you. Quick question for you - why do you use a negative sign in front of total before interest to calculate the unlevered cash flow?
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?
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.
This is so great! Thank you Spencer, I love your videos.
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?
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, 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
What if rent growth rate differ in upcoming years instead of fixed rent growth rate, then how it can be adapted in the formula.
I am getting a cross-reference error while adding the formula for construction interest. Since the cells are all interlinked. Kindly suggest
Dude you’re a hero
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.
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.
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!
I have no idea why I am watching this
Thanks
your channel is amazing! Thank you for sharing your knowledge!
Excellent as per usual.
Thank you,
As always.
How does he quickly change the font color to blue?
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/
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?
@@MnMsg2Wrld I ran into the same issue and this issue was addressed in the video description (a simple subtraction error was made)
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
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.
Very helpful. Thanks a lot!
You're so badass bro
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!
Also, I made the corrections in the bio but still not getting same balance.
How do you know that break even is month 25?
Why do you input loan to cost as a formula instead of having it as an input? With the circular reference involved it does not come out to be the same.
A cell entry to create an amount based on a daily % rate for that months entry? What would be a formulary for that?
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!
Cell G88
so you want to ask, why considered CAGR where LP invest with IRR
why the month is assumed to month 90? is there any explanation ?
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.
fantastic.
Great video！thank you ！
+1 banana
Any small apartment building new construction modeling Iink that you can post here?
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.
Enable iterative calculations under formulas in Excel settings
Great video
Thanks Bryden
I got a PE case study who can help me tomorrow ?
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
How did you name the cell NRA?
Around the 26:48 minute mark, the construction interested changes from 1,723,501 to 0. Can you please comment on this change?
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.
Thank you. These are tutorials are fantastic and I cannot overstate how helpful they have been.
The link is invalid...
Fixed. Thanks for the heads up!
Hard to read screen
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.
haha any WSO fan here?
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