Build an IRR Matrix for Real Estate in Excel
Vložit
- čas přidán 29. 06. 2024
- How to build a simple tool in Excel - the IRR Matrix - to analyze the internal rate of return of a real estate investment in each year of the projected hold period. The IRR Matrix is one tool for helping to assess the ideal hold period for an investment. I should note that this module has since been updated to version 2.0, and now allows for development and value-add scenarios, as well as monthly periods. You can find a walk-through of version 2.0 here: • IRR Matrix Module in R...
00:00 - Introduction
01:10 - What is an IRR Matrix?
02:42 - Using the Provided Workbook
03:58 - Setting Up Cash Flow Scenarios
05:17 - Creating an Unlevered IRR Matrix
07:25 - Analyzing Unlevered IRR Results
09:14 - Building a Levered IRR Matrix
10:53 - Evaluating Levered IRR Outcomes
12:23 - Conclusion and Summary
14:00 - Additional Questions and Contact Information
To download the Excel Workbook used in this video, visit: www.adventuresincre.com/using...
To learn more about the author, visit: www.spencerburton.org
Become a real estate financial modeling pro: www.adventuresincre.com/accel...
After nearly five years, I decided to update this module to accommodate non-acquisition scenarios (i.e. development and value-add) as well as monthly periods. You can find a walk-through/tutorial of the latest version of this model here: czcams.com/video/QGGZVphaoVs/video.html
Thanks, this is so awesome. It just compounds the fact that the world is run on Excel. Great tutorial
Great job, Spencer. Clever and efficient. At one time I regarded myself as a world expert in Excel, but now at 83 with a few rental homes and personal investments to analyze I have gotten over 8 layers of nested If-ands. Just now I am trying to reconcile my instinct to have things "real nice" on my rental homes with the best way to make money. You have saved me a lot of time from APOD to answers. THANKS! Randy Wright in Plano
I love these videos, the best so far what Ive found on the net.
Thanks!! I was trying to create a similar template. 3 cups of coffee later I had a headache. Thanks for the template and explanation
Impressive, clean and obviously very practiced modeling skills at a high level. Have seen your work over the years online and now focusing on the partnership / IRR hurdle modeling for a development deal in Texas. Thank you very much and hope to see more later! Best, Riley
Amazing video, Spencer! Thank you.
Great tutorial, thanks for sharing. This is super interesting
You are awesome. Thank you!
Great at teaching this, thank you sir
These videos are great
Your presentation is excellent. This model appears to work perfectly for single tenant net lease properties. A question I have is there a template exactly like this available, or do I have to build one of these for every deal I want to assess?
thank you kind Sir
Really helpful.
Hi Spencer, great video... how do i format the cell to show "X" after the number? (i.e, 2.0X). I'm building a model that factors in a refinance in a year prior to sale. How do I have this IRR Matrix reflect the proceeds received from a refi, and than show the loan payout as higher after the date of refinance?
Thanks for the video, Spencer. I have a question re the initial investment/deposit.
As using equity from another property is basically another loan, do you consider the equity used in this case as an initial investment when calculating IRR?
Hey!
I really enjoyed video, informative! Quick question why are you using the following years NOI to asses the property value for the year before?
First... one way to value an asset is by suming up the discounted cash flows.
I believe this is a variation of a concept called the "Income Comp" approach to figuring out a property's value.
In this case the future cash flows are the net operating income.
great video! could you elaborate on how you determined your annual loan payoff? not really understanding why 0.04/12 (is this a 12 year loan? or months?) for the rate, or 360-(year)*12, or why the yearly debt service is being divided by 12??? your help is greatly appreciated! :)
actually, if you could just explain how you got the nper, that would be really helpful! is this a loan that compounds everyday?? :S
The .04/12 is because the annual interest rate on the loan is 4% and there is 12 months in a year, so each monthly payment is .003333. The number of periods (nper) is 360 because most commercial loans amortize over 30 years (360 months).
Hi Spencer, I really enjoyed the video and really out to learn a bit more on real estate investments. If you were doing a similar scenario for a real estate development. However, for this development one is considering a to develop rental units without any plans of selling. How do you come up with an IRR appraisal for such a development?
Just extend the years out to 100 years or what Ever. Most investors sell after 5 to 10 years.
Great video. I can't find where to download the model with the formulas. Please advise
www.adventuresincre.com/using-an-irr-matrix-to-analyze-hold-period/ and scroll down
Great video and IRR explanation; is this excel matrix template available for download?
hardysselfstorage - Appreciate the kind words. Yes, the model is available to download. I've included a link in the video description.
I had to follow along to complete the spreadsheet, which was fine.
Hey Spencer! For year 11, my NOI will be determined by how much revenue and expenses I use for that full year. That ends up making my holding period 11 years, not 10. So for property value, is it not NOI (year 10)/market CAP?
your property is valued off of the year ahead using year 10 assumptions the forward year NOI is what u need to plug in above Cap rate
The current value of an asset is the sum of all future discounted cash flows. So you sold it at year 10, you need the future cash flows which is year 11 in this case.
Hi spencer. thanks for the video. its super advanced stuff. bit lost!
What is an equity multiple?
Hi Hugues, thanks for watching. Equity multiple is simply the ratio of total equity distributed to total equity contributed in an investment (i.e. the multiple by which an investor's equity is expected to grow). It's a metric, generally complementary to IRR, that helps an investor assess the viability of a prospective investment. My co-contributor Michael Belasco discusses this further in his post on the limitations of IRR and using equity multiple: www.adventuresincre.com/the-limitations-of-irr-when-evaluating-real-estate-investments/
Spencer
Why did you use the same cash flow streams for the equity multiple on both levered and unlevered basis?
@@haroldk9369 I am guessing he made a mistake if you check the formula, you will need to change the cells. cheers
I have been utilizing your model and it has been incredibly helpful, so thanks! One question I have is should the IRR model include a projected amount for seller costs based on your estimated exit price? In reality you are going to sell the property and out of that price you will deduct fees for commissions, etc. Should selling fees be added as a line item below "loan payoff" and then added into the matrix model? It would seem this gives you a true picture of the distributable cash you walk away with and therefore a more accurate IRR? Thanks for any help, this has really been bugging me.
Scott - Yes, selling costs should be included. That can be done by either adding a selling costs line, as you suggest, or netting out the costs in the sales price line. Thanks for the comment!
@@adventuresincre Thanks for confirming that. This video series is absolutely fantastic and I'm really thankful for you having done it! Keep up the great work!
How are the levered and unlevered equity multiples the same?
there's an error in the syntax. cell c20 should be: =SUMIF($C$18:$M$18,">0")/-SUMIF($C$18:$M$18,"
Hi there Spencer, I dont seem to be abale to find the template? What am i missing ?
www.adventuresincre.com/using-an-irr-matrix-to-analyze-hold-period/
NOI = Revenues - Expenses right??? To calculate Unlevered IRR do you take Tax and Depreciation or only NOI??? Thanks!
Anibal Mejia - Correct, Net Operating Income = Effective Gross Income - Operating Expenses. Unlevered IRR is a function of unlevered cash flow. NOI is an input of unlevered cash flow, but not the only input. Simply stated, unlevered cash flow is the sum of all investment inflows and outflows in each period of your hold.. Unlevered cash flow is generally a before tax and depreciation figure, thus unlevered IRR is generally a before tax and depreciation metric Hope that helps!.
Hi Spencer, great video, I was wondering if you can help me with an excel real estate model I am working on. If possible can I email you directly?
How would you build this if you didn't know future NOI
I'm going to guess that if you are unsure what NOI is you could probably make an assumption of what it is. For example, being that you have the cap rates already, I could imagine one way is to also assume what the property value is. However, I think we could agree that this has a high chance of leading to a very misleading number.
FYI - Levered Equity Multiple should be 4.14. Appears to be the same 2.05X in both unlevered and levered... typo
ace
What confused me with this video is understanding the logic behind holding it for a longer time horizon despite the highest returns being in year 2.
In both cases, unlevered and levered IRR had the highest values. How can we explain the justification for holding the unlevered IRR of 9.17% on at year 10 as the ideal in comparison to year 2's IRR of 9.41%? Also, how could we justify levered IRR of 20.32% as ideal despite year 2's IRR of 24.47%?
Invest with Vincent - really great question! There are a variety of reasons for why an investor might prefer to hold longer, even if selling sooner would result in a higher IRR. The main reason from a quantitative standpoint is what we call reinvestment risk.
While the two-year IRR may appear attractive, for it to make sense over the 10-year IRR the investor must have confidence that she can find an investment at the end of year 2 that will meet or exceed the potential of simply holding the existing investment 10 years. Also, remember that there is a cost - both in time and in terms of money - to the reinvestment. Thus, the combination of the uncertainty, plus cost and time of the reinvestment, would likely make the 20.32% IRR of 10-year IRR in this case more attractive than the 24.47% IRR of the 2-year investment given the reinvestment risk on the latter.