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:
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.
Accountant · MN · Member since 2008 · 142 posts · 25 votes
15y
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.
Investor · Round Rock, TX · Member since 2010 · 8k+ posts · 4k+ votes
15y
@Max:
Inflation increases expenses as well. The only thing that would be fixed is the PI portion of your payment and that isn't even the case if you have a floater for a loan.
Maybe that is something that can be toggled on or off in the later revisions if we can agree it adds value. Escalating rents is more aggressive IMO and people should be able to tune their assumptions so it may make sense to have it.
I'll leave it up to you guys to decide how to implement it going forward. We can keep things as-is or make a toggle option. I am a fan of the toggle option.
Investor · Round Rock, TX · Member since 2010 · 8k+ posts · 4k+ votes
15y
@Phil...no rush. We all appreciate your time and attention. It would be better for things to take longer and for them to be well-thought-out than to try to rush something to get it out the door.
What would be really nice is to USE THE MODEL on someone's project and track stuff over time. That will flush out the real bugs and the shortcomings if any exist. We can point new members to this thread and they can provide feedback so that we can get the best possible model over time.
Accountant · MN · Member since 2008 · 142 posts · 25 votes
15y
So, in an effort to keep this file from getting to large and unwieldy I am going to try to consolidate and organize the tabs.
When complete I hope to have to following:
1. Financial Summary Tab - Shows results of the model, relevant graphs etc.
2. Inputs Tab - All inputs that run the model will go into here
3. Loan Amort, Income Stmt., Cashflow - will still all remain separate tabs and will be left in as exhibits
4. Underwriting CF Analysis - this is coming from the GS SS that Bryan's friend provided. I like it as a return calculator. It will replace what is currently at the bottom of the financial summary tab, which provides good information but is not very readable and has a lot of extraneous information that I do not think adds much value.
5. What-If/Scenario Analysis - This will include various sensitivity and scenario analysis (data tables etc. Right now this is a separate tab but I am thinking of combining it with the financial summary tab in order to cut down on the number of tabs.
6. Actuals Tab - This is where a user can drop in actual results as they come in over time
7. Actual vs. Model Comparison - Compares how far off actual results were compared to the original model
If anyone has any comments or concerns let me know.
Investor · Round Rock, TX · Member since 2010 · 8k+ posts · 4k+ votes
15y
Sounds like a plan. The only thing I would recommend implementing in addition to what you have cited above is some "Advanced" tab to allow for additional financing structures. Something where projects can have multiple debt stakeholders, classes of shares that pay preferred returns, etc.
Maybe that is something for a different model, but it would be very nice to have eventually.
Accountant · MN · Member since 2008 · 142 posts · 25 votes
15y
That is odd I thought that I had posted a response to your posting on Jul. 9th. asking you for some more clarification on the advanced tab and financial structures.... it was a fairly lengthy post. oh well.
Anyways, yes I have been playing around with the model and hope to do more this weekend. A company that I work for is being sold which has been taking a lot of my free time.
I will see if I can at least get another version out there for you to check out.
Accountant · MN · Member since 2008 · 142 posts · 25 votes
15y
I have saved another version to the Data site. http://www.biggerpockets.com/files/user/OracleofMN/file/Pro-Forma-Template-Small-Apartments-v4-xls It is not final by any means but wanted to get a more recent version of the file out there so the people that want to use it and play around with it can do so. The biggest change is the merging of the GS spreadsheet from Bryan with the Apartment Analysis file v3.
This file contains the following:
1. Financial Summary Tab – The intent of this tab is to show the relevant outputs (returns) related to the inputs and models. You will see the return information similar to what was in the GS file, one variable sensitivity tables to show the impact of changing the major inputs, the current major inputs (corresponding to the sensitivity tables), graphs of the return information. Going forward we can add additional analysis relevant to an investor. This can be a one or two page printout with all of the necessary info for an investor.
2. Financial Inputs – This tab is manipulated by the user to best represent the current investment which is to be modeled. I kept most of the same inputs from the original file but changed them to add more functionality as well as to make them appropriate for the underwriting CF analysis tab (Goldman Sachs File). The manual rent/expenses can only be overridden in the 1st year of the investment – the additional years did not add much value and would not be relevant to the monthly Underwriting CF Analysis tab since assumes growth rates in the projected years. The sensitivity tables are also calculated on this tab (the financial summary tab is linked) because data tables will only function if it is on the same tab as the original input (input tab). This tab still needs to be reviewed to remove the unnecessary data.
3. What-If-Analysis Tab – I removed the data from this for the time being as we changed the return calculations. This tab will be used for multi-variant analysis and will either added to the summary tab or removed all-together.
4. Underwriting CF Analysis Tab – This I where the bulk of the modeling is done. It comes from the Goldman spreadsheet from Bryan but made to work with all of the additional inputs on the inputs tab. It can only model out to year 30 as excel does not have enough columns in the version that I am using (2010). Keep in mind that this is not an income statement but a calculation of the relevant cashflows needed for the IRR calcs. The income stmt and this tab will only tie out down to Net operating Income.
5. Exhibits I, II, III – This is the Net Income Stmt, Amort Table, CashFlow. All of these tabs are for additional information but not used in the return calcs. It will be useful to have especially for bank financing, investor prospecti, etc. The Cashflow does not currently calc correctly. I intend to fix it but its importance is not as relevant as other tabs. Also, the income stmt only models out for 30 years because it is based on the Underwriting CF Tab. This can be added later although thirty years is probably a sufficient timeline for any investment.
6. The Final Two Tabs –Actuals and Actuals vs. Model – These can be used to track your actual expenses and revenues vs. what you originally modeled. This will be helpful for budgeting as well as setting expectations and improving your investment criteria over time. There is an entirely different set of analysis that can development around actual vs. forecasted.
As this spreadsheet is used there is no doubt that you will find small formula errors and other excel nonsense. Please report back accordingly and fixes will be made in later versions.
Also, users may want to change their excel settings for formula calculations to “Automatic except for Data Tables”. This should help when initially inputting all of your investment details. Just be sure to use F9 when finished to update the various scenarios.
Now that we have the structure of the model set we can start building in more functionality and analysis.
Accountant · MN · Member since 2008 · 142 posts · 25 votes
15y
Unstable? Are you referring to the IRR calc's on the data table when IRR can't calculate? I have added an iserror formula which should correct for this - it will show a zero instead of #DIV/0!.
For the inputs, at the moment, all of the ones that should be input by the user are in blue font, while the ones in black are calculations based on the other inputs and should not be edited. Do you think we should call them out further?
Investor · Round Rock, TX · Member since 2010 · 8k+ posts · 4k+ votes
15y
Yeah...I think the divide my zero issue is slowing things down some. I guess I'll reserve judgement until the next revision since it is a work in progress.
For the inputs I am thinking that we may want to create a separate tab that gives a synopsis for each input and how changing it impacts the model in general. There are tons of knobs to tune right now so I am not sure that people will take the time to study the model without some guidance on its use.
Version 5. I added the intstruction tab as you suggested which should be a good reference for anyone new to the spreadsheet.
You will see the other changes listed on the change tab.
I also have a todo list at the bottom of the changes tab for things that still need to be worked on.
Let me know if you are still getting errors becuase I have been working through it for a little while today and it seems to be working fine. I tried not to use any formulas that wouldnt work in earlier versions of excel but i cant remember all of the new functions.
I will see if I can reitierate my earlier post that didnt load.
Accountant · MN · Member since 2008 · 142 posts · 25 votes
15y
We should also think about deleting the earlier versions at some point. New users may get confused looking through all the files. Or better yet create a subfolder where we can keep old verions of files. I am sure this is not/will not be the only file that gets improvements made to it.
Investor · Round Rock, TX · Member since 2010 · 8k+ posts · 4k+ votes
15y
I glanced at the model tonight. Comments:
Instructions Tab
1. Under "Financing Specific"...How would one know what to included in closing costs or how to estimate them? Some guidance with this would be helpful for the model summary
2. Under "Exist Assumptions"...We should have guidance on how to estimate exit costs
3. Under "Other General Inputs"...We should have guidance (tables?) for OI and for capital gains tax assumptions
I'll comment more later...my system is unstable right now ;-)
Investor · Round Rock, TX · Member since 2010 · 8k+ posts · 4k+ votes
15y
I sent the model and this thread to some of the best RE analyst profs in the world this evening Phil...let's keep our fingers crossed that one or both of them give us some more feedback. I'm wiped for the day so I'll comment more on your next revision.
Accountant · MN · Member since 2008 · 142 posts · 25 votes
15y
I saw those emails. That is excellent. I hope that we get some good feedback from them.
I also made the quick changes to the instruction tab as you suggested. I added the tax tables and elaborated on the closings costs and exit costs. You will see these changes in the next verison that I upload.