Please critique my Excel Property Analyzer
Hello everyone,
My wife and I have been looking at properties for the past couple of days and we had been writing everything down by hand.
I got sick of the disorganization and created an excel sheet to see if properties would cash flow with "X" down for "X" amount of years etc.
However, we were not sure what to include in all the expenses.
This is what we have so far:
Vacancy
Property Management
Insurance
Taxes
Maintenance
Electricity
Water/Sewer
Waste
Capital Expense 5% X Gross rent
Misc
Then I left a few blank spots for things I forgot.
any help appreciated!
All the best
Send me an email - i'll send you something to compare.
TTFN,
Greg
Hey Johathan,
Typically, you would see vacancy as a separate line item in your revenue section, reducing you potential income. This would get you to net income.
Also, I would subtotal your expenses before you get to your capital expenditures.
Heres how we basically structure our line items:
Gross Potential Rent
Less: Vacancy
Net Rental Income
Expenses (listed individually)
Total Expenses
Net Income
Capital Exenditures
Net Cash Flow
Of course, as long as you understand the nature of all components of cash flow, you can set up your schedule however it makes most sense to you.
Good Luck
Greg I emailed you thanks.
Tod thanks for the reply.
Just to clarify;
For the capital expenditures, you are saying that I should multiply the net income but 5% and NOT the net rental income?
Or else I have too big of a number for capital expenditures?
Is this correct?
thanks
Hi Jonathan -- just a few things that have sunk in for me in my long illustrious (6 mths and counting!) investing career:
You should multiply the Gross Potential Rent by 5% for "Replacement Reserves" and include it when computing your Net Operating Income. You will need to fine tune the 5% by looking at the building mechanicals and roof and estimating remaining life of these items and replacement costs. If replacement is needed in the next couple of years on the roof, for example, and you elect to not do it immediately, I normally include that item in the up-front purchase price (for analysis purposes) and recompute the ROI in that fashion. In this case, you will need to reserve funds (or build funds) to prepare for that replacement.
An advantage of knocking out these "near-obsolete" items up front is that if you're financing with a community bank, the bank will often loan you funds for "purchase+rehab" for all the work you do right around the time of purchase. Then you have a functionally new property that will require less maintenance and be much easier to sell if need be. And.. if you do find yourself needing to sell for some reason at a later point, you may or may not have funds at that time to replace a roof and other items, forcing you to dump an "as-is" property at a loss.
I'd recommend using at least a one-month vacancy factor (8.3%), and be sure to add in the prop mgr's cost of leasing the unit (commonly half month rent). So if the unit turns over every year, prop mgmt could be 10% (normal fee ) for 11 rented months, but 50% in the vacant month. This equates to a PM fee of 13.3% for the year. You'll also have to clean, paint and possibly replace flooring at these turnover points, so make sure you include this in your PM fee or add to maintenance. If it's a multi, don't forget yard maintenance, snow removal, etc., even if you're doing it yourself. (time is money).
If you're buying well under tax-assessed value (usually the case), make sure you research and understand the process for appealing the assessment, and how long this will take. You can save a ton on this. There are services that will handle this for you for a % of the first year savings, or a flat fee.
You'll be buying way under "replacement cost", so determine how much you want to insure (perhaps 50% above your all-in cost), and base your insurance calc at that level (perhaps .40% multiplied by 150% of all-in). Don't let the agent convince you to insure at "replacement costs".
Be sure to add in loan closing costs to your up-front. Some is fixed -- lender fees, appraisals and inspections (these are higher for multi-unit), title search, attorney fee -- and some is variable with your loan amount -- points, title insurance, county fees sometimes), so I like to break it out in that fashion.
On a side note, purchase an extra umbrella liability policy to protect your property equity and personal assets from lawsuits. 1mm should cost $200-250/year.
Thanks for the reply David.
I was under the impression that 'capital expenditures' was the same thing as 'replacement reserves' but I may be wrong.
Im also curious about how to calculate this capital expenditure/replacement cost.
You say to multiply the Gross rent by 5% but unless Im mistaken, Tod says to multiply the net income by 5%.
Anybody else care to comment on this?
thanks
Replacement reserves is simply what you're "setting aside" from the rental payments to handle the large "capital expenditures" as they happen. 5% of gross rents is a common set-aside % that in most cases will build sufficiently to handle your major 15-20 year items (roof, furnace, water heater, HVAC, boiler, appliances). Using 5% of Net Income will not be sufficient.
Take a look at this Freddie Mac form:
http://www.freddiemac.com/sell/forms/pdf/998.pdf
It is used by appraisers/underwriters to estimate Net Operating Income for a property. It's helpful to see how lenders/appraisers look at it, particularly the "Replacement Reserves" section. You will need to have some idea of costs to replace things, such as $150/square for a tearoff and re-shingle, $600 for a water heater, etc.
Thanks everyone.
Regarding "However, we were not sure what to include in all the expenses." See also what you will report to the IRS, specifically form 8825 http://www.irs.gov/pub/irs-pdf/f8825.pdf. I have also used a similar form to the one David references, but it is interactive ... might be the same thing (https://www.efanniemae.com/sf/formsdocs/forms/216.jsp)