Short-term Rentals: Investment Simulation and Analysis Financial Model

Airbnb, Financial Model, Investment, Real Estate, Residential Properties

10-year Financial Projections, 5-year Financial Projections, Amortization, Budget, Cap Rate (Capitalization Rate), Cash Flow Analysis, Cash Flow Projections, Cash-on-Cash Multiple, CFO, Dashboard, DCF Model, Debt Amortization, Debt Schedule, Debt Service Coverage, Discount Rate, Excel, Financial Analysis, Financial Feasibility, Financial Model, Financial Modeling, Financial Planning, Financial Projections, Financial Ratios, Financing, Forecast, Forecasting, Investment Multiple, Investor Cash Flows, IRR (Internal Rate of Return), NPV (Net Present Value), Passive Income, Property Valuation, Real Estate Valuation, Rental Income, Revenue Projections, Scenario Analysis, Sensitivity Analysis, Startup Financial Models, Terminal Value, Uses and Sources of Funds, Valuation, Venture Capital

Video Tutorial:

Many individuals have realized they can rent out their second house via short-term leases (leases less than 12 months) to make some extra money. An entire industry has evolved out of that called STR or short-term rentals. This type of real estate asset class was built for individuals, but now is an entire multi-billion dollar industry with things like AirBnB and VRBO and others getting into the game.

People like to stay at a house rather than a hotel, so here we are with this new type of real estate investment opportunity. This financial model was designed to plan out the cash requirements and performance of being an STR real estate investor. It is comprehensive, flexible, dynamic, and very useful for a single property analysis or many.

The basis framework starts off with an acquisitions tab where the user defines all relevant inputs for up to 20 properties. Each property does have an input for the unit count, and so that allows the model also to be used for many 100s or 1,000s of properties by putting in tranches of properties into each row and defining all the economics by unit.

The user can enter assumptions for each of the 20 property slots independently, and they include timing (purchase date, cost/renovation period if applicable, decide if you are financing with debt or not, define if you want to have a refi or not and how long from the initial acquisition will it be before the refi, all assumptions around the LTV/cap rate for Refi, and much more.

There are revenue assumptions that account for seasonality. That means for each of the 12 months of the year; there are inputs for the expected average monthly utilization (how many days in a given month on average is rent being collected) as well as inputs for the base rent and adjustment from the base in each of the 12 months. This is defined individually for each property tranche.

On top of the rent logic, there is also an estimated annual rent increase rate for each property.

Owning a home that you rent out over the course of a year means there will be expenses like utilities, property taxes, property management fees (if you are outsourcing the management), insurance, and other general costs of owning a home. The user is able to define up to 9 slots for monthly fixed costs and 9 slots for monthly variable costs. These are on a per-unit basis and have a growth rate attached to them as well.

The variable expenses will be driven off the maximum utilization rate, and any lower utilization months will have proportionally lower variable costs. This really gives the user a good idea of expected cash flows as they scale out their STR business.

Finally, there are exit assumptions for each individual property tranche. That means defining the exit cap rate and the exit month separately on each. I have never made such assumptions that are granular in a real estate model, and it gives the user the optimal ability to create financial strategies and plans that may be more reflective of their actuals.

Each property tranche will have its own monthly and annual pro forma that shows revenues, expenses, net operating income (NOI), debt / refi / debt service/exit proceeds less selling costs, and final monthly/annual cash flow.

Those monthly and annual summaries also automatically get aggregated into a consolidated monthly and annual pro forma which is used to give an overview of the entire financial performance of all properties over a 15-year period (max). Here you will also see IRR (based on monthly periods and converted into an annual rate for best accuracy).

There will also be a joint venture waterfall option so the user can determine how much of the required investment comes from investors (LP) and sponsors (GP). There is an IRR hurdle structure to determine how this cash is distributed, and as the IRR increases, the cash split the LP receives can be reduced (thereby promoting the GP). Here is also a DCF Analysis for each party (LP vs. GP) and visualizations to show that split as well.

To see the overall financial performance even better, charts were built to visualize the consolidated annual cash flows and show the cash flow from each of the 20 properties in a stacked bar chart. You will also see NOI vs. debt service and Debt Service Coverage Ratio for each property individually and in aggregate. This is important because the bank will want to make sure there is enough operational cash flow to support the debt being taken on if the debt was chosen as a funding option.

You can plan out all sorts of strategies with this real estate financial model.

The cap rate is applied to the trailing 12-month NOI per exit month, renovation costs are evenly spread over the defined renovation period, and there are sanity checks throughout the entire model and aggregated checks to make sure nothing is broken when you start manipulating the assumptions. This has gone through extensive testing to ensure all scenarios flow without issue.

You must log in to submit a review.