Quick Model Template - Small Apartment Complexes
Investor · Round Rock, TX · Member since 2010 · 8k+ posts · 4k+ votes
I used Josh's new file vault this evening to upload a beginner model for evaluating small apartment complexes. I would appreciate feedback if people want to take a look:
Pro Forma Template - Small Apartments
Hopefully this will be helpful for people wanting to create their own, customized model. As time goes on perhaps we can modify this one so that it will have all of the bells and whistles needed based on forum discussions. We can revision letter control it so it covers all of the analysis people would like to see.
Feel free to run with it if you like...I hope it proves useful in your analysis of small apartment complexes.
Uploaded version 2. It could still use a little work but for a free spreadsheet this will do almsot everything that you need. Thanks again for the upload Bryan. If anyone else wants to discuss what should be added to this file let me know.
I only took a quick look so far, but it appears to be solid. Thanks for sharing, Bryan!
You're welcome...hopefully some of our spreadsheet wizards can take it and run with it to make improvements for people wanting to analyze deals.
Bryan,
Great Spreadsheet. I would expand the financial inputs areas as it was difficult for me to enter expense data. Also, it may make sense to have a tab for entering rent per unit to define the unit mix and rent psf.
You can "unfreeze" the panes in Excel to get a better view for the inputs tab.
Yeah...the unit mix is assumed to be homogeneous. It is for small apartment complexes and thus that is a limitation for this particular model. Feel free to add the functionality and repost.
For those that want a model to start with for larger transactions less all of the clutter of the one above I uploaded this tonight:
Simple Waterfall Model - Private Equity
Each transaction is very different, but this should give people an idea of how these are done. Enjoy!
Hey Bryan,
You have a good model here, thanks for posting.
I have been going through it and have made some changes to the formatting, formulas etc. which I would like to share with you and get your feedback, however, before I do could you please explain what was the intent behind the CapEx section on the Financial Inputs tab? It first appeared that the spreadsheet attempts to breakout the depreciation for the Capex purchases in the beginning of the investment. This is why it shows years in service, useful life, etc. However, on the cashflow tab it is reducing cash by the calculation each year, which tells me that the intent was not to calc depreciation but the estimated cost of these items over the life of the investment. But if we are assuming that these are capitalized and not expensed shouldn't we be depreciating these items as well? What am I missing here?
Thanks,
Phil
The model has been through many battles Phil. I am quite certain there are inaccuracies in places because I was learning while I was constructing things. I don't buy stuff this small anymore so I don't really use the model anymore.
I am probably the board's worst on the rules for expensing or capitalizing things. Maybe there should be an option to either expense everything or capitalize everything so that you get the most aggressive and least aggressive snapshot of things. Note that there is an option to toggle the depreciation on or off on the inputs page and I generally don't even account for the shields in current models.
Alternatively you can break each line item out by the proper GAAP accounting if you know the rules. If you wanted to get really fancy you could have an option to accelerate things with a chattel appraisal or some such. I don't know the rules well enough to do this and it likely doesn't add a whole lot of value. It would be nice to have though if you are clever enough to do it.
Perhaps you can bump the revision and include a Word document which documents the changes. That way people can see what changed and one of our other clever board members can take it and run with it if they want to improve things even more.
I share your thoughts. This is the kind of spreadsheet that could be extremely detailed may not be adding that much more value in depending on its use. I will upload my version and if anyone wants to change it further can do so.
Uploaded version 2. It could still use a little work but for a free spreadsheet this will do almsot everything that you need. Thanks again for the upload Bryan. If anyone else wants to discuss what should be added to this file let me know.
Very nice Phil...here is a link to the new sheet for future thread readers:
Please continue to request things that are missing and/or point out inaccuracies in the model. Over time we should be able to make this thing something very nice for the BP family.
@ Bryan - Great spreadsheet!
@ Phil - Nice work with the changes. The spreadsheet looks really good now. Much more user friendly.
Okay Phil (and/or others)...here are some additional requirements for version 3:
1. The sheet shall allow the user to toggle the depreciation assumption to depreciate over the life of the property OR shall depreciate in the most aggressive manner possible for the type of property for each line item. In other words the toggle will be between most aggressive and least aggressive. Note that the sheet should retain the ability to turn off accounting for depreciation altogether in addition to implementing this additional functionality.
2. The sheet shall implement a what-if analysis to demonstrate how rents and vacancy impact the key metrics of choice. I would suggest cash flow at key years, IRR, or MIRR.
Phil claimed he can model anything in a separate post so this should be a snap for him. Others are welcome to play along too.
Please provide additional feedback on how we can improve the model everyone.
I am assuming it had something to do with the planned maintenance today Phil...is it working now?
Try uploading now . . . we corrected the issue.
For version 4...here are some legacy comments from one of my buddies that used to work at Goldman Sachs and now runs a real estate fund:
//Begin Comments
I just had a chance to take a look at the model you sent over. Below are a few comments on the mechanics of the model in general (not on anything specific to this deal). Suggestions are just based on my experience so take what you like and ignore the rest – all meant to be helpful suggestions and not critical of the current work.
-Why is S&P appreciation rate relevant at all? I would take this out since it doesn’t add anything to the analysis.
-Same question for CF Reinvestment rate/MIRR? I don’t consider the reinvestment rate relevant to a deal since it has nothing to do with the property being evaluated. I personally never use MIRR.
-Vertical lay out in “financial summary†tab is tough to follow and a bit uncommon. I actually think you can make a few changes and get rid of this tab completely or make a more informative “summary†tab
-Not a huge fan of locking worksheets – limits flexibility and are a pain in the butt to audit
-I would put the full “underwriting cash flow†on one page and focus on non-tax issues first. i.e. a pre-tax analysis. No need to have an income statement and CF statement unless you just like to have the exhibits. The one page UW Cash Flow is typically laid out as follows.
-Items from top to bottom are i) revenue items, expense items, etc as you have shown to get to property NOI ii) then lines for purchase price and associated expenses iii) sales proceeds based on an exit cap and associated expenses iv) then a total for “Unlevered Deal NCF†(you can run an unlevered IRR on this as well as unlevered cash-on-cash metrics)
-Then add the leverage lines (typical lines are beginning loan balance, draws, amortization, other pay downs, ending balance, interest) easy to make an adjustment for multiple loans. There really isn’t a need to have a separate loan amortization page unless you just like to have that as a separate exhibit. You can get to a leveraged cash flow line here where you can run levered IRR, cash yield on equity, equity profit multiple etc.
Tax issues can then be considered below the line where you can factor in non-cash expenses like depreciation and look at the after tax numbers
-It’s also beneficial to run numbers on a “monthly†basis with an annual rollup as a summary. Monthly detail is just as easy but provides for greater flexibility and a better representation of the cash flow management issues you may run into. Also, once you own the property and are dealing with actual results it makes it easy to adjust for historical actual numbers on a monthly basis to monitor how the investment is performing.
-Sensitivity tables are an extremely useful and simple way to show how a range of assumptions on rent, exit cap, etc. can affect returns. Very helpful in understanding “down side†scenarios and the true effect of a departure from business plan. If you’re not familiar with data tables they are really easy to use.
//End Comments
I never really implemented anything or changed the model based on these comments, but they may prove useful if anyone wants to run with them. I understand the model, but I can see it being confusing in some places for people just using it that don't want to pick through the machinery.
I invited another spreadsheet wizard via PM yesterday so hopefully he'll chime in some too. With several cooks in the kitchen we may be able to get something precise without being overly complicated to use.
Here is an example model to go with my last post from the Goldman guy:
For V4 we may be able to borrow some of the functionality from the model and incorporate some of his comments if anyone feels like it will add value.
I have just uploaded version 3. I will look through the suggestions from your GS guy and see what we can do for the next version. Check out the table in this one, I really rather like it.
http://www.biggerpockets.com/files/user/OracleofMN/file/Pro-Forma-Template-Small-Apartments-v3-xls
Additions:
1. A Depreciation Assumption Toggle - You can now choose between None, Building Only, Chattel - Look at the notes in the first tab for a better description.
2. A tab/table for What-If analysis - The current scenario includes MIRR calucations at various rent levels and vacancy rates. Also see the description for for more details.
Give me some feedback on whether or not this is what you were looking for.
If i had more time I would've added these but it will have to wait for version 4.
- A toggle for different depreciation methods - Straight-Line vs. Declining Balance as well as a toggle for Section 179 deductions
- Other Scenario/What-If Analysis - Interest Rate Fluctuations effect on return and other
Cool deal...I'll take a look later today. Check out the GS guy's what-if tables. That may inspire some changes for V4. What would be REALLY cool is to see everything in one big table that varies many key items. That may be cumbersome to read though...not sure.
My responses to the GS comments:
I liked the comparison to the S&P500. My partner and I always try to compare to the S&P because as a rule of thumb if we are not beating the S&P by a certain margin then why not just park our money in an S&P index fund and collect our 10% return with no effort. I know this is a simple comparison but easy to forget.
I would generally agree with him here as I never really use the MIRR and even when I was going through the CFA tests there was never any mention of MIRR only IRR. However, I do like it because it is more conservative (as long as your reinvestment rate is below IRR). So, as long as you know what you are looking at I think they are both meaningful.
I agree. The summary tab as it currently stands is not really a “Summary†but a large part of the model. This should be broken up or as your friend stated removed completely.
I am indifferent. It is really up to the user of the spreadsheet. There are no passwords.
I like what I see here. It makes more sense from the investment perspective (as opposed to operationally). I will go through his spreadsheet and see how hard it is to incorporate some of these changes.
Agreed. I added one in v3 with the intention of adding more.
I already have some more ideas for the next revision Phil...I'll let you work on the current one before I toss too much else out there though ;-)
Here are some other things I though of today...feel free to incorporate or leave for later revisions. As long as we continue to use the revision page we can tell what changed. It really may make sense to leave legacy revisions on the page too so that everyone can see what changed since the initial sheet.
1. Implement something to track ex post data. This will be useful for posters that want to develop a pro forma and make assumptions and later compare their actual operations data on the site to see how good the feedback from the forum is at predicting how things will go in the real world
2. I'll pick through David Geltner's book to see if we are missing any major items in our model. I don't think we are, but there could be some nice-to-haves missing
3. As a long term goal it would be nice to merge this model with the earlier one I posted that allows for waterfalls or more extravagant financing structures that involve multiple layers of equity or debt stakeholders. The model can show the project's return along with the return from the perspective of each stakeholder...that would be really fancy!
4. Line item expenses that deviate largely from the ones here:
Operating Expenses Study
should be flagged. Note that we can input these on the "Typical Values" cells in the income statement or anywhere else it makes sense. I would suggest highlighting them in red or calling attention to them in some manner so that the user knows they are deviating from the study appreciably.
5. A really cool feature to have would be an option to enter multiple loan scenarios to determine which financing product is the best given the sensitivity of the what-if analysis. The user could pick the what-if variables and see which loan product has the most risk. I am not really sure how to do this, but it would be nice to have. Floater products tied to MTA, LIBOR, or some other common index could be stress tested using legacy data to see how the cash flow will react over time and to choose which loan product minimizes risk and maximizes return
Good luck with that! Even a small subset of the items above would be great to have. You could make a whole revision out of each one ;-)
I think it's a great model, lovely to use. I know my Pro Forma isn't nearly as extensive or annotated, but I never use mine for much more than getting an estimate of a cap rate and a debt service ratio :P
My only point of note is that inflation isn't added in. I mean, it's technically a non-issue considering you can call it real dollars and whatnot, but if you are like me, I raise rents 3% per year to account for inflation, and money doesn't always move as fast ;)
When I saw "quick and dirty" I didn't expect something as full featured as what I got. Makes me miss the days of working in Excel spreadsheets with macros for classes. Good times...
Bryan,
Thanks for the additioanl suggestions. When I get time this week I will try to incorporate some of these changes. Some of these ideas may require some major reworking of the spreadsheet and therefore will take some time. Let me know as you come up with more and I will keep a list of open items that we can try to get to over time.
Max,
The model does incorporate inflation... there is an assumption for annual rent inflation as well as for expenses. I beleive it was set at 1.5% in the most recent version. You can also set property appreciation although the value in the current model is caluclated unsing a 10% cap rate. This can be toggled on the inputs screen.
-Phil
The model does incorporate inflation... there is an assumption for annual rent inflation as well as for expenses. I beleive it was set at 1.5% in the most recent version. You can also set property appreciation although the value in the current model is caluclated unsing a 10% cap rate. This can be toggled on the inputs screen.
-Phil
My apologies, I just looked into what my issue was, and I wasn't reading it correctly. I assumed that the capital expenses on the first page, over the years, were just inflated from year 1 and inflation wasn't calculated, rather than having them input otherwise.
One of the pitfalls of not really looking at the equations everywhere.