Building financial models is an art. The only way to improve your work, a variety of financial models to build a series of industries. Try a model for an investment that is not beyond the reach of most people – a real estate investment property.
You can follow the downloading of the pattern for as investment property.
Before we start we build a financial model, we must ask what motivated the companies we are investigating. The answer has important implications for how we build the model.
For example, a financial institution such as a bank by assets is his (investments, loans, mortgages, etc.) are driven, then we would probably get a better balance of growth of the company. A retail store is on the other hand, driven by the sale of its products. Therefore, we should concentrate on the revenue forecast and profit and loss account.
Who uses it?
Other questions are, who use this model and what they will use it for? A company may add a new product for which they need in order to calculate an optimal price. Or investor can assign a project to see what kind of investment he or she is to be expected.
In these scenarios, the end result of what the model calculation can be very different. If you know exactly what to do should the decision of the user of your model, you may end up starting several times, until an approach that is the proper inputs, the right products can be used.
On Real Estate
In our scenario we want to know what kind of financial services, we can expect from a given investment property some information about the investment. This information includes variables such as the purchase price, appreciation rate, the price at which we can rent, financing terms available before the property etc.
Our rental income and appreciation of the value of the property, our return on this investment will be determined by two key factors. Therefore, we should start by rental income forecasting and valuation of the property in consideration.
Once we built this part of the model, we can use the information that we calculated in order to understand how we have to finance the acquisition of property and finance charges incurred as a result we can expect.
The next section, we address the prediction of property management fees. We have had the value of the property, we planned to use to calculate property tax, it is important that we build the model in a specific order.
Once we establish these projections, we can begin to reconstruct the profit and loss account and balance sheet. As we have established this, you can tell that these elements are not calculated and we have to go back and maybe add them in the appropriate places.
Finally, we can use this data to project the financial statements of cash flows for the investor and to calculate our return on investment.
Formatting model
Since we are a model to build a little complicated, we should be about how we want to put it to think, that we keep our workplace clean. In Excel, one of the best ways to organize financial models on certain sections of the model is to be separated on different sheets.
By the computations and projections, which are closely related in the same worksheet and separation from other calculations that are more relevant to other sections of the model on separate tabs (sheets), we keep the model organized.
We can describe each tab a name, the information contained herein. In this way, other users of the model to better understand where the data in the model and how it flows is calculated.
In our model of an investment property, we use four tabs: ownership, funding, expenditure and financing. Ownership, financing and expenditures are tabs that we have support and input for our model project. The rider will be our financial results page where we have the output of our model in a way that is easy to understand (in the form of financial statements) is displayed.
The revenue forecast
First things first, let us with the property tab, A1 by renaming the tab “property” and the addition of these titles in the cell of the worksheet. The care of some of the formatting problem on the front end, we have made it easier to keep clean the model.
Next, we put our box assumptions. A few lines below the title, type “assumptions” and a vertical list with the following entries:
Purchase Price
First month’s rent
Occupancy
Annual appreciation
Annual rent increase
Brokerage
Investment period
In the cells of the right of each input label, we set up a field by a realistic space for each value. We format each of these values to be blue. This is a joint modeling convention to indicate that these input values are. This format is easier to understand for ourselves and others, such as the flow model. Here are some values to start according to:
$ 250,000.00
$ 1,550.00
95.00%
3.50%
1.00%
6.00%
4 years
The purchase price is the price we expect to pay for a particular property. The first month’s rent will be the price for which we expect the RENT. The utilization is measured as the property we (meaning 95% utilization, that there is only about 18 days that the property is not leased to tenants each year will be) to hold.
The annual appreciation rate determines the value of our property increases (or decreases) each year. Annual rent increases will determine how we grow the rent every year. Measures brokerage, what percentage of the sales price of the property, we need to pay a finder’s fee, if we will sell the property.
The investment period is how long we will hold the property for sale in front of us. Now that we have a good set of assumptions of ownership down, we can begin to calculations based on these assumptions.
A reference to time periods
There are many ways to begin planning on the values through time. You can receive financial monthly, quarterly, annually or project a combination thereof. For most models, you should test the predictions of the monthly financial statements for the first two years.
You can users of the model, some of the cyclicality of the company (if applicable) to see. It also allows you to get some problems with the business model that does not appear to identify in the annual surveys (such as cash balance impairments). After the first two years you can then end up on a yearly basis.
For our purposes, the annual forecasts to reduce the complexity of the model. A side effect of this choice is that if we begin to repay your mortgage will be completed later we incurred more interest than we would if we monthly payments of principal (which is what happened were real).
Another modeling choice, you may want to consider whether to use the title of the actual date of your column projection (31.12.2010, 31.12.2011, …). This can help you make the function more complicated later, but again, for our purposes, let’s just use 1, 2, 3, etc., to measure our year. In Excel, we can play with the formatting of these numbers something like this:
Year 1 Year 2 Year 3 Year 4 …
These numbers should be entered under our assumptions the field the first year after the column B have at least these values, we will bring in its tenth year. The projections over 10 years do not have much credibility when most financial models do not more than ten years.
On projections
Now, when we put our time labels on the “Property” worksheet, we are ready to begin our projections. Here are the original values we want for the next ten years in our pilot project:
Value of Property
Annual rent
Properties for sale
Brokerage
Mortgage Bal.
Bal Equity Line.
Net
Value of the property of
Add these items in column A, just below and to the left, where we added labels per year.
Line value of the property will simply project the property’s value over time. Value the first year will reference the same purchase price, our hypothesis and the formula for it too easy on this hypothesis. The formula for annually on the right side of the first year will be as follows:
= B14 * (1 + $ B $ 7)
Where is cell B14 directly to the left of the year in which we are currently calculating the value of the property and $ B $ 7 is an absolute reference to our “annual assessment” hypothesis. This formula can be pulled over the line to calculate the remaining years of the property.
The line of the annual rent calculated the annual income the property each year. The formula for the first year is as follows:
= IF (B12> = $ B $ 10,0, B5 * 12 * $ B $ 6)
B12 should be “1″ in the labels of each year, we have created. $ B $ 10 an absolute must have a reference (be the data in our cell hypothesis must be an integer, even if it is formatted to read “year”, otherwise the formula will not work) on our hypothesis investment period. B5 should be a reference to our hypothesis, monthly rent, and $ B $ 6 should be a gold-standard pinout.
What is this feature that if our investment is less than the year in which this value should be calculated, then the result should be zero (we are no longer even the property after it is sold, we can not collect, rent ). Otherwise, the formula to calculate the annual rent that the monthly rent multiplied by twelve and by the load.
For subsequent years, the formula should look like this:
= IF (C12> = $ B $ 10,0, B16 * (1 + $ B $ 8)) Even if the investment period is less than the year in which this value should be calculated, then the result will be zero . Otherwise, take only the value of rental income in the last year and the increase of our adoption of the annual rent increases in cell $ B $ 8
Output time
Now that we found property values and rental income are available, we can now predict revenue from the subsequent sale of the property. In order to use the net proceeds from the sale of our property charge, we have to predict the values mentioned above: the selling price of goods, brokerage, mortgage balance and the balance equity.
The formula for predicting the selling price is as follows:
= IF (B12 = $ B $ 10, B14, 0)
This formula says that if the current year (B12) is equal to our investment period ($ B $ 10), while our selling price is equal to the value of the property proposed by us this year (B14). Otherwise, if the year is the year we want to sell the property, then there is no sale and the sale price is equal to zero.
The formula for calculating the brokerage takes a similar approach:
= IF (B18 = 0.0, B18 * The $ B $ 9)
This formula says that if the sale price for a given year (B18) is equal to zero, then the brokerage fees are equal to zero. If there is no sale, there are no brokerage fees. If there is a sale, and brokerage fees are equal to the sales price (B18) through our acquisition of brokerage fees ($ B $ 9) is multiplied.
Our mortgage balance and the balance will be our equity line will be charged to us on the next worksheet, we will now leave for two blank lines as placeholders for these values. Our net proceeds from the sale of real estate is simply the sale price less commission, less the mortgage balance less the balance on-line home equity.
We should have a line called “value of the property.” This line shows the value of the property that we own, so that it corresponds to a value of zero when we sold it. The formula is simple:
= IF (B12> = $ B $ 10,0, B14)
B12 refers to the current year in our slogan year. $ B $ 10 refers to our hypothesis, investment horizon and B14 refers to the current value in the year of online property value, we calculated. All this is merely the line of our online property value, but it shows zero for the value of the property after we sell the property.
The financing
Well, we model how we finance the acquisition of property. Obtain a new tab “financing” and add the title of “financing” at the top of the table. The first thing we need to know is how much we need for the funding.
To begin, we will “purchase price” enter a few lines below the title. Refer to the right of this cell to our hypothesis, the purchase price of “ownership” tab (= property! B4). We will format the text of that cell to be green because we are a link to information on another worksheet. Formatting Text in green is a common convention of financial modeling, to keep track of where the information flow.
Below this line, enter “capital”. On the right side of this cell, we are now taking over of $ 5,000.00 (formatted text in blue to indicate an entry). Our assumption of working capital provides additional capital, we believe that we need to cover the daily management of an investment property. We may have some issues that are not fully covered by our rental and our working capital will help ensure that we do not run into cash flow problems.
The bottom line of working capital, so we type “Total capital requirement” and the right of this sum cell, the values of our purchase price and taking into account capital. This amount represents the sum of equity capital we need to raise.
Sources of capital
A few lines down our “total capital required,” we will create a box capital sources This box has six columns with the following headings:. Origin, quantity, price%, rate, duration, and the annual payment. Two typical sources of capital for the purchase of a property is a mortgage and a line of credit (or credit). Our last source of capital (for this model anyway) will be our own money or shares.
In the Source column, we will add a “first mortgage”, “credit line” and “equity” in the three cells under our sources section. For a typical mortgage, a bank will usually lend up to 80% of property value on a first mortgage, so we are 80% in line type for the first mortgage as part of the purchase price,% title (re-formatted in blue when specify an input value).
We can now calculate our first mortgage in the amount column of the following formula:
= B5 * C11
B5 is a reference to our purchase price and the C11 is a reference to our hypothesis of the purchase price%.
In today’s market, banks are reluctant to offer lines of credit, if it invests less of a 25% in real estate, but say they are willing to give a little. Suppose you give us an additional 5% of the value of real estate as an equity line. Enter 5% (blue) in the equity line of credit line as part of the purchase price% heading.
We can calculate a similar formula for equity line in the quantity column:
= B5 * C12
Now that we are the amount of bank financing available for our purchase, we can calculate how much capital we need. Enter as part of the amount field in the equity line, the following formula:
= B7-B11-B12
B7 is our total funding required. B11 is the first mortgage financing from the B12 is available and the available funds from the equity line of credit. Again, we assume that we have to cough up money for something that we are not able to finance from the bank.
The cost of capital
Now we want to understand what this means it will cost us. For interest rates, say 5% on the first mortgage and 7% on the equity line. Enter these values in the blue column in our prices. For terms, is a typical mortgage for 30 years and an equity line should be 10 years. Enter these values in blue concept.
The column will be an annual payment of the annual payment calculation, we need to do to fully pay off any loan taken by the end of his term interests. We will use an Excel function for this:
= PMT (D11, E11, B11, 0)
The PMT function gives us the value of the fixed fee, we will have a certain rate (D11), a number of periods (E11), a current value (B11) and a future value (which will be zero if we have to repay the loan). We can then run with the same formula in the cell, to calculate the payment for the equity line.
Now we are ready to plot our projections. Start by copying column headers from the property register (year 1, year 2, etc.) and glue on the tab below the capital financing sources of our box. Let us also draw the line of assets, the value of the property tab (highlighted in green to show values, they are a different matter).
Now, we present balances in connection with our first mortgage. Let us label this section of the worksheet “first mortgage” and below, add the following positions in the first column:
Opening balance
PMT interest
PMT main
Closing balance
Subsequent Events Sale
For the first year of our opening balance sheet, we will only refer our first mortgage amount (= B11). For years two and later on, we will simply refer to the previous closing stock (= B25).
To calculate the interest payments for each year, we simply multiply the beginning balance of our assumed interest rate (= B22 * $ D $ 11). B22 is an opening balance for the current year and $ D $ 11 would be our assumed interest rate.
To the payment of principal each year to calculate interest payments, we simply subtract the current year from our annual payment ($ F = $ 11-B23). $ F $ 11 is the annual payment, we calculated before, and B23 is the interest payment for the current year.
Our closing balance is just our opening balance less our capital payment (= B22-B24).
Finally, the balance of our after-sales simply our closing balance for each year or zero, if we have already sold the property (= IF (B19 = 0.0, B25)). This line is it easy for us to represent our debts, as we build our balance sheet later.
We now repeat the same lines and calculations to project our equity funds. Once we finish with these two sources, we closed our financing spreadsheet.
One step back
We can now return to our mortgage and equity balance on the property tab location to calculate our net revenues. For the mortgage balance, we use the formula:
= IF (B18 = 0,0, financing! B22)
B18 refers to the value of the current year on the property for sale. If the value is zero, then we want the mortgage balance to zero, because we do not sell are the property of that year and did not need to show a mortgage balance. If the value is not zero, then we want the mortgage balance during the year, the financing of the tab (Financing! B22) can be found show.
We use the same formula for the calculation of the balance of fairness online.
Spending on
See our spending tab called “expenses” and paste the same way to the top of the table. This worksheet is simple and straightforward. First we create a table with the following assumptions input labels:
Tax rate
Annual home repairs
Annual rental fee broker
Other Expenses
Inflation
Next to each of these cells, we adopted the following values are you in blue:
1.10%
$ 800.00
$ 100.00
$ 50.00
1.50%
Each of these assumptions is a component of the ongoing cost of managing a property. Box under our assumptions, we re-insert Browse year by one of our other worksheets (year 1, year 2, etc..)
Let us in a row, the value of our properties, we previously calculated, and format the values shown in the green to drop. We need these values to calculate our tax burden, it will be easier to have it on the same worksheet.
Below this line we will add a few articles online, we will be predicted:
Home repairs
Rental agency fees
Other Expenses
Control
Our first year will be the home repairs simply equal to our annual event (= B5). For subsequent years, however, we need to consider whether we still possess the property. If not, our costs will be zero. If this is the case, we want to grow your home repair costs by the inflation rate. Here’s what the function for the following years should look like this:
= IF (C $ 13 = 0.0, B15 * (1 + $ B $ 8))
In this case, C $ 13, the effective value of the current year is to fix the responsibility of the B15 last year at home, and $ B $ 8 refers to the rate of inflation. For rental brokerage fees and other costs, we can use the same methodology to predict these costs.
For taxes, we need to use a different calculation. Property taxes are based on the value of the property, so we used a percentage to the tax hypothesis is represented. Our formula for the calculation of taxes shall be as follows:
A = B13 * $ B $ 4
Since our taxes will be zero if the value of our property is zero, we can simply multiply the value of our property (B13) of our assumed tax rate ($ B $ 4). And now we plan our spending.
Rally
Now comes the fun part. We need to put all of our projections in the financial statements presentable. Be passed within the framework of the model around, we want, it is very clean and well formatted.
Leave the label on the “Financials” and then type the title at the top of the table. A few lines down, we will begin our balance sheet by adding a “balance” label in the first column. Just below this line, we will present in our standard shipments per year, but this time we want to include a 0 a year before the column.
On the left side of the blade just under the headings of the year, we are the balance sheet as follows:
Cash
Property
Total assets
First mortgage
Line of Credit
Total Debt
Paid-in capital
Retained earnings
Total shareholders’ equity
Total liabilities and
Check
Our cash value of the year zero will correspond to the amount of capital we invest, we want, so we refer to our equity value of the financial table (= financing! B13), and format the value in green.
Real estate, first mortgage, equity and retained earnings in line is all zero zero, because we have invested anything yet, still. We can go ahead and add formulas to total assets (cash and property), the total debt (first mortgage and the equity line), equity (paid-in capital plus retained earnings) and Total liabilities and stockholders’ equity (total debt plus shareholders as a whole). These formulas are the same for all years of the record.
For the rest of the year zero for the premiums, we use the same formula as cash for the year zero (= financing! B13).
Back at the box office, we will use this line as our balance sheet because the money is for the most liquid stocks. In order to make money in a leaf, we are less in cash equal to total liabilities. This is to ensure that the results are always in balance. We still have to see if our money is always negative, which could pose a problem.
On a balance sheet, ownership is usually represented in its historical value (our cost), we will use the following formula to value our property and then format it show in green:
= IF (C5 => property! $ B $ 10,0, capital! $ B $ 4)
C5 presents the current year. Property! $ B $ 10 is a reference to our hypothesis, investment period and $ B $ 4, a reference to the purchase price. The value of the property is either zero (sold to) or equal to our purchase price.
Our first mortgage and equity balance, we can easily change the balance after the sale of the financial tab. We format each line in green and displays them on a different sheet is drawn.
Be Paid capital is equal to our initial investment (as we will not have to provide additional investments) or zero, after we sold the property. The formula is as follows:
= IF (C5 B> property = $ B $ 10,0, $ 16)
C5 presents the current year. Property! $ B $ 10 is a reference to our hypothesis, investment period and $ B $ 16 is a reference to the value of the year zero of our paid-up capital.
We must look for the line of retained earnings that we planned our profit and loss statement skip, because it is based on net income.
The control line is a quick way to tell if your balance is in equilibrium. It is simply equal to the sum of assets minus total liabilities and equity. If the value is not zero, then you know there’s a problem. As a bell and whistle booster, you can use conditional formatting to highlight problems.
The calculation of the Bottom Line
Below the Line of Control, we will implement our profit and loss account in the same way, we have our record – followed by a “Statement of income” label from our annual column headers, we will deliver it. our profit and loss account as follows:
Rental income
Proceeds from the sale
Total Revenues
Home repairs
Rental agency fees
Other Expenses
Total Operating Expenses
The operating profit
The interest expense
Control
The net profit
Rental income, sale proceeds can repair your home, rental brokerage fees, taxes and other charges simply by other spreadsheet programs, where we (and formatted in green, of course) have calculated can be drawn. The interest expense is simply the sum of interest payments for both the first mortgage and equity financing tab.
The others are simple calculations.
Comments