Real Estate Proforma
This is a Google Sheet that you can use to create a real estate proforma.
Click here to get your copy of the Google Sheet Proforma
Video Tutorial
Key Notes
All Inputs and Outputs occur on the "Cover_Input" tab. The "Timeline" tab is really the "Backend" of this spreadsheet, and you do not need to look at it. The other three tabs are worksheets for you to think about specific inputs.
The proforma is built for easy inputs and easy scenario analysis.
- Inputs: All Gray and light blue cells are potential inputs. Light blue cells I see as being optional formulas which I will explain later.
- Scenario Analysis: When you first download the sheet, Scenarios 2-4 will match Scenario 1. The
power of this is that you can fill in "base" assumptions in Scenario 1 and quickly adjust for scenarios in the
other slots.
I have used this proforma heavily for my own Real Estate investing. I wanted to be able to quickly model building mobile homes vs duplexes, what if rents are higher or lower, etc.
I looked at quite a few properties where I was looking to add mobile homes on the property for rent. Many of these properties had an SFD house existing. I would prep the "base" case in Scenario 1 with just the existing house. Next I would make adjustments to show adding a single mobile home in Scenario 2 and two mobile homes in Scenario 3. Seeing the three possibilities side-by-side helped me assess how risky the opportunity was, i.e. could I afford if for some reason I was only able to do one mobile home, or how did the house alone fare?
My Father and Grandfather briefly looked at buying a property that included apartments and a trailer park. The rents at this property were "below market", meaning that similar properties were charging more for rent. I was able to plug in the current rents into this proforma, then quickly show in the other scenario slots what it would look like if rents were increased to various levels.
- Five Year Return Breakdown: The proforma assumes that you will buy this property and then sell it in five years. The reason for this is so that we can see where your returns will come from, Cashflow, Equity Stepup, Debt Paydown, and Tax Writeoffs.
Input Instructions
Again, gray and light blue cells are inputs. Here is an explanation of the inputs for each section.
- Global Inputs (To the Right): To the right of the Scenario Slots and under the logo are a few
inputs. The only one I expect you to adjust is the Start Date, and that is going to be the expected
closing date. The other items could be adjusted. Here are their functions briefly.
Market Return Assumption: This is the expected return of the broad stock market. This assumption is for certain performance metrics to think of as an alternative investment vs what you are proposing to do with the property.
Inflation: This is the expected value of inflation over the next few years. Arguably this should be 3% or 2%. I keep it lower because it is more conservative.
Tax Rate: The tax rate is used to calculate the expected returns from tax writeoffs.
Estimated Land Value %: This percentage affects how much of the purchase price you are depreciating to get a tax writeoff. You can only write off the value of the buildings on the property. If you are buying raw land, put 100%.
- Purchase Info: This section is straight forward. You put in the amount of your down payment for Starting Equity and the Purchase Price. The percentage of the down payment is shown to the right of the Starting Equity
- Loan Info: This section calculates the size of debt payments on the property.
Loan Amount: This is not an input. It calculates the size of the loan as simply the difference in the Purchase Price and Starting Equity, and it also adds in the Additional Loan that is in the Improvement Cost Section below.
Rate and Term: The rate is the interest rate that you expect on the property. You can look up the current rates and input that. The term is how long the loan is "amortized" over. The 30 year mortgage is the classic term length. These are things you may analyze with scenario analysis, looking at how you will do with a different rate or shorter term.
Escrow Items: When you get a loan, the bank requires property insurance, property taxes, and other things be paid from an escrow account that they control. To fund these items, a portion is added to your monthly payment. You can look up how much these items will cost in your county.
Payment: This is the calculated amount of your payment. It uses a payment calculator to come up with the principal and interest portion of the payment based on the Rate, Term, and Loan Amount, then the Escrow Items are added in.
- Income Potential: Here is the section where rent and expense assumptions are made.
Units: Here is where you put in how many dwelling units are on the property. A house would be one, a duplex is two, etc.
Expected Rent: Here is where you put the average monthly rent per unit. Notice that there is a blank tab called "ExpectedRent". This tab is meant for you to have a "Worksheet" to think about and calculate what rents should be at this property.
Expense Rate: This is the percentage of Expected Rent that is expected to be eaten up by expenses. I recommend making this number higher than you probably expect.
NOI (Net Operating Income): This is not an input. It is the total annual rent minus the total annual expenses. NOI is a common metric in the real estate world. It is the income strictly from the operation of the property, before your debt payment is paid.
Cap Rate: Cap Rate is another common metric in the real estate world. It is calculated as the annual NOI divided by the Market Value. You can research what this property should have as a Cap Rate. This is important for the Exit section below. If in doubt, put 8%.
First Rent Paid: This input is for you to account for a delay between the purchase of the property and when you expect to start collecting rent. This would be important if you plan to live there for a year or it needs a lot of work.
- Improvement Costs: This is to input the expected cost of any work you need to do to the
property.
Expected Cost: This is where you put in the raw cost to make the improvements you need to make. There is a tab called "Expenses". This is a blank worksheet where you can make a detailed list of the improvements you need to make and the expected cost. Once you have made this analysis, put the number in the Expected Cost box. For example, if you need to paint and redo the floors, do some research for how much this should cost, then input the total here.
Additional Loan: to the right of Expected Cost, there is a box for how much of the improvement costs will be funded with loans. This adds to the Loan Amount above. Note that putting these costs in with the purchase loan may not be a good assumption.
Improvement Finish: This is when you expect the work to finish for the improvements you are going to make. It is important to the proforma because it is assumed that the improvement costs are spread over the time from purchase to the Improvement Finish.
- Exit: This section is to input the expected market value of the property after your
improvements.
Expected Market Value: This is the only real input in this section. There is a tab called "ExpectedValue" that is meant to be a worksheet for you to analyze how much the property could be worth. You can do this based on comparable properties nearby, or you may use the three valuations shown below as a basis as well.
Other Values in this Section: There are three valuations shown. Value as Rental (Cap) takes the Cap Rate and NOI shown above and calculates an expected value based off of that. 5 Year NOI Valuation uses the five year timeline that is built by this proforma to value the property. Perpetuity to the right values the property based on the inflation rate and expected market return (this is a finance thing...)
Output Explanation
There are four sections of outputs. I will explain below. Some of them will be pretty technical.
- Basic Income Statement: This is a simple Profit and Loss statement on an annual and monthly basis. Gross Income is just the expected rent collected over the year or month, and Operating Expenses are subtracted from this to get to NOI (Net Operating Income). After this, your mortgage payment is subtracted as Notes Paid. Finally we arrive at Pretax Income, and this is your expected cash flow from the property in the first year or month.
- First Year Tax Writeoffs: I do not deal with taxes much at present, so I really suggest that
you do some research on these items and consult an expert. I believe that in principle the basic tax writeoffs
for real estate are captured by this section. It does assume that you are doing an itemized deduction as opposed
to the standard deduction, which may not be the case. The first column shows the writeoff while the second
column shows the total expense that was used to calculate the writeoff.
Interest Paid: From my understanding, interest paid on your home or an asset you collect income from or that is used in business is tax deductible. This calculates the total interest paid in the first year and applies the tax rate to it.
Depreciation: From my understanding, you can depreciate rental properties. This calculates the first year's depreciation assuming a 27.5 year depreciation schedule and applies the tax rate to this.
First Year Tax Reduction: Adding together the Interest and Depreciation writeoffs, this is the estimated tax reduction in the first year of ownership.
Total First Year CF: Adding the Tax Reduction to the Pretax Income above calculates the estimated first year cash flow. To the right of this is the first year cash flow divided by the amount of cash put down. This metric can be though of as a dividend percentage.
- Five Year Exit Return Breakdown: This is a great view of how you can make money through real estate investing. The four sources of return on real estate assets are shown. This assumes that the property is sold after five years. Whether or not you intend to do this, it is still a good analysis to look over to see where your value is coming from.
Cashflow: This is the actual cash income that is expected over the next five years. There is inflation in this analysis, so it will not exactly match Pretax Income times five, but it should be close.
Equity Stepup: This is where the value of the property went up more than you paid to improve it. This is broken into two categories. Sweat Equity is where the value of your improvements are worth more than you paid for them. It is called sweat equity because it often comes about by doing work yourself for cheaper than paying professionals. Appreciation is increase in the value of the property because of inflation or higher demand in the area. This analysis just grows the value of the property by the input inflation rate.
Debt Paydown: While you are making payments on a property, part of your payment is going to principal. These principal payments are increasing your equity in the property and you will get this money as cash upon sale.
Tax Writeoffs: This is the total expected tax savings over the five years of ownership of the property.
Total: Adding up these four sources of return shows us how much we expect to make on this property over the next five years. To the right is this number divided by five for you to think about how much this could mean annually.
Tax Implication MIRR: IRR is a popular finance metric. It stands for Internal Rate of Return. MIRR is "Modified"-IRR. We don't need to get into the details of this, but know that it is the annualized rate of return on buying the property and renting it out for five years, then selling. This percentage should be compared to alternatives like our Expected Market Return or the returns on CDs or savings accounts at the bank. To the right of this is the annualized total return divided by the cash put down on the property, an annualized return on cash.
- Metrics: There are a slew of metrics presented here that may not be valuable to you. I will briefly go over them below.
Cash Down: This is simply the amount of cash paid either through the down payment or on improvements. This as a percentage of the expected market value is shown to the right.
Annual Cashflow: This is a repeat of the Pretax Income above. Cash flow as a percentage of cash down is shown to the right.
Payback Period: This is the number of years that it will take to recoup the cash spent on the property if it were paid exclusively from cash flow and no other sources of return.
Debt Service Coverage: This is a ratio of the NOI of the property divided by the total debt payments made over the course of the year. If this is less than 1.0, that means the property is not paying for itself.
Stepup: This is a repeat of the Sweat Equity above.
Five Year Exit: This is the expected cashflow upon sale divided by the Cash Down at purchase.
Five Year Exit MIRR: This is the IRR discussed above but it does not account for the tax Implications of the property.
Five Year Total: This is the total return discussed above on a five year sale, but again it ignores the tax implications.
ROE: Return on Equity. This is the annual cashflow divided by the Cash Down.
ROA: Return on Assets. This is the annual cashflow divided by the Expected Market Value.
- Five Year Exit Return Breakdown: This is a great view of how you can make money through real estate investing. The four sources of return on real estate assets are shown. This assumes that the property is sold after five years. Whether or not you intend to do this, it is still a good analysis to look over to see where your value is coming from.