Share this content on Facebook!
18 Jul 2016

Mat-Su


Building financial models is definitely an art. The only method to enhance your craft would be to develop a variety of financial models across numerous industries. Let's consider using a model to have an investment that is not past the reach of most individuals - an investment property.


Before we jump into building a financial model, we should ask ourselves what drives the business that we're exploring. The answer may have significant implications for the way we construct the model.


Who'll Use It?


Who will be using this model and what will they be utilising it for? A company may have a new product for which they have to calculate an optimal price. Or perhaps an investor may want to pre-plan a project to see what sort of investment return they might expect.


Depending on these scenarios, the outcome of what the model will calculate could be very different. Unless you know precisely what decision the consumer of your model must make, you may find yourself starting over several times before you find an approach that uses the right inputs to obtain the appropriate outputs.


On to Real Estate


Within our scenario, we want to find out what kind of financial return don't be surprised from a good investment property given certain information about an investment. This information would include variables such as the purchase price, rate of appreciation, the cost at which we are able to rent it out, the financing terms available fore the property, etc.


Our return about this investment will be driven by two primary factors: our rental income and the appreciation from the property value. Therefore, we ought to start by forecasting rental income and the appreciation of the property in consideration.


After we have built out that area of the model, we can make use of the information we've calculated to figure out the way we will finance purchasing the property and just what financial expenses we can expect to incur consequently.


Next we tackle the property management expenses. We'll want to use the home value that people forecasted in order to be in a position to calculate property taxes, so it's important that we build the model in a certain order.


With these projections in position, we are able to begin to piece together the wages statement and also the balance sheet. As we put these in place, we might spot items which we have not yet calculated and we may have to return and add them in the appropriate places.


Finally, we can use these financials to project the money flow towards the investor and calculate our roi.


Laying Out the Model


We should also feel about how you want to lay it out therefore we keep our workspace clean. In Excel, among the best methods to organize financial models is to separate certain parts of the model on several worksheets.


We are able to give each tab a reputation that describes the information found in it. This way, other people that use the model can better understand where information is calculated in the model and just how it flows.


In our investment property model, let's use four tabs: property, financing, expenses and financials. Property, financing and expenses would be the tabs on which we input assumption making projections for our model. The financials tab will be our results page where we will display the creation of our model in ways that's easily understood.


Forecasting Revenues


Let's start with the property tab by renaming the tab "Property" and adding this title in cell A1 from the worksheet. By taking care of some of these formatting issuing on the front end, we'll come with an easier time keeping the model clean.


Next, let's setup our assumptions box. Several rows below the title, type "Assumptions" and make a vertical list of the next inputs:


Cost

Initial Monthly Rent

Occupancy Rate

Annual Appreciation

Annual Rent Increase

Broker Fee

Investment Period


In the cells to the right of every input label, we'll set up an input field with the addition of a realistic placeholder for every value. We will format all these values to be blue in color. This can be a common modeling convention to point these are input values. This formatting will make it more convenient for us and others to know how the model flows. Here are some corresponding values to begin with:


$250,000.00

$1,550.00

95.00%

3.50%

1.00%

6.00%

Four years


The purchase price would be the price we expect to pay for a specific property. The first monthly rent would be the price for which we expect to book out the property. The occupancy rate will measure how good we keep your property rented out (95% occupancy means that there will only be about 18 days the property will go un-rented between tenants each year).


Annual appreciation determines the rate the value of our property increases (or decreases) every year. Annual rent increase will determine how much we will boost the rent every year. The broker fee measures what number of the selling price from the property we'll have to pay an agent whenever we sell the property.


The investment period is how long we will contain the property for before we sell it. Now that we have a good group of property assumptions down, we can start to make calculations according to these assumptions.


A Note on Time Periods


There are lots of methods to begin forecasting out values across time. You can project financials monthly, quarterly, annually or some mixture of the three. For most models, you should think about forecasting the financials monthly during the first couple years.


By doing so, you permit users of the model to determine some of the cyclicality from the business (if there is any). Additionally, it enables you to spot certain issues with the business model that won't show up in annual projections (such as cash balance deficiencies). After the first couple of years, you can then forecast the financials on an annual basis.


For the purposes, annual projections will reduce the complexity from the model. One side aftereffect of this alternative is the fact that whenever we begin amortizing mortgages later, we'll find yourself incurring more interest expense than we would when we were making monthly principal payments (which is what happens the truth is).


Another modeling choice you may want to consider is whether or not to use actual date headings for your projection columns (12/31/2010, 12/31/2011,...). Doing so can help with performing more complex function later, however, for our purposes, we will simply employ 1, 2, 3, etc. to measure out our years. In Excel, we are able to have fun with the formatting of those numbers a little to see:


Year 1 Year Two year 3 Year 4...


These numbers should be entered below our assumptions box with the first year from at least column B. We will carry these values out to year ten. Projections made beyond ten years do not have much credibility so most financial models don't exceed 10 years.

Investment Properties



Comments

There isn't any comment in this page yet!

Do you want to be the first commenter?


New Comment

Full Name:
E-Mail Address:
Your website (if exists):
Your Comment:
Security code: