|
(from M2, who wrote the spreadsheets, just so nobody gets it twisted -- i'm just facillitating, here, reposting what was in the e-mail he sent).
----------------------------------------------------------
The goal of the included spreadsheets are to calculate the annualized return on a piece of property you’re renting and/or living in. Annualized return is the average return you get on an Investment on a yearly basis, so instead of saying: “I got an ROI of 50%” you break it down on a year by year basis, so you can compare it to other investments.
Details below...
Due to time constraints, I’ve provided a high level explanation of each – you should be able to figure out the rest on your own, if not, just ask questions and I’ll answer them.
Spreadsheet - Annualized Return Detail:
The purpose of this spreadsheet is to track all of your costs related to a property on a month by month basis, determine your P/L, Impact on your cash flow, look at revenues, look at equity increase via appreciation, etc, etc.
I even include a section that looks at how a negative cash flow property is potentially causing you additional losses, as that’s cash you’re not able to invest on a month by month basis.
Using this spreadsheet allows you to account for various events that occur during the life of your ownership of the property with excruciating detail, due to the fact that the amount you pay on the interest and principal portions of your mortgage changes from month to month, plus other variable costs this much detail is required to really determine what your actual return is.
High Level Overview:
· You start off by inputting the value of the home at purchase time, your % down payment and your APR; the spreadsheet will generate your Mortgage Payment and tell you how much was paid on Interest vs. Principal on a month by month basis.
· Next, fill in your annual costs related to maintenance, home owners association, property taxes, your top marginal tax rate, etc. Note that the maintenance cost is pre-calculated as 2% of the home’s value and property taxes at 2.2% of same.
· Additional Tax Note: Row 31 allows you to adjust your tax rate as tax laws change or as you go up and down in Tax brackets.
· Improvement Costs are calculated by adding 80% of the improvement cost to the original value of the home, if you think the house will appreciate more, put in your own number.
· If you jump down to Line #30 you can input a One or a Zero, a one indicates that the property is one you live in, a zero means you’re renting it out, this is due to the property having different taxation implications.
· As in previous spreadsheets I made, if you update the numbers in one column they repeat for future columns, in relation to the above this allows you to adjust your numbers over time for a house you live in, than move out of and rent and than live in again.
· If you go to Columns AK & AL you can see an annualized return analysis for the first 3 years. Appreciation adjusted P/L factors in rental profits, increase in equity and the home’s appreciation, the first return numbers are calculated on that. Liquidity return looks how much free cash the house generates over the base costs without the tax deductions. If you want to extend the analysis over three years, you just select and copy the entire spreadsheet back to the two year analysis and than copy forward of the three year one.
Section Overview:
Financing Breakdown: Looks at your mortgage payment and the amount paid towards interest and principal, your current home equity balance and remaining loan balance, if you refinance your loan as far as APR, you can adjust that APR and all of your payments will be modified accordingly.
Expense Analysis: Breaks down the initial annual costs you put in at the top of the spreadsheet on a month by month basis. If you have a fee for a home owner’s association, you will need to put that in yourself and past it across the sheet.
Revenue Analysis: Breaks down the revenue from renting the home, I have a line item for “other revenue” in case you rent out a garage whilst living in the home (or something like that). The cool thing here is that you can live in the home and rent out part of it, put that data in under rental and see how just renting out your basement can greatly increase your return on a home.
Tax Analysis: Adjusts your costs for the tax deduction, interest is automatically adjusted and I assume you can deduct your property taxes and depending on whether you’ve classified the property as a rental or not, it deducts for the other expenses too.
Appreciation Adjustment: This portion modifies your P/L calculations to factor in appreciation and let’s you know how much appreciation you get on a month by month basis. The rate you put in is the annual appreciation rate, not the month by month rate.
Opportunity Cost: If you have a month over month cash flow for the property, this section looks at the cash you’re losing by not having that money available to invest; even if the property is eventually profitable when you factor in tax deductions. I’m basically saying that even after you rent out for one year and have the deduction cash to fill in the cash flow gap, you’re still tying up cash you could be investing.
This is just a high level explanation, but it should be enough for you to figure this out – everything else is fairly self explanatory.
Spreadsheet – Property Analysis Cash Flow:
This one allows you to do a quick and dirty annualized return analysis for multiple properties at the same time, you can’t really account for changing tax laws, personal situations, costs and the like with this, but it’s actually pretty accurate.
If you understand the spreadsheet above, this one is pretty self-explanatory – you put in the same data and the spreadsheet generates the mortgage payment for you and adjusts your home value based on improvements.
Next, you input your anticipated rental amount and the spreadsheet spits back P/L numbers for 100% rental revenue (rented all year around) and 75% rental numbers (more realistic, accounts for dead periods when the house is empty).
Columns AA & AB indicate the positive or negative cash flow on the property on a monthly and annual basis
Column P indicates your annual operating costs for the property
Starting with Column AC is where the ROI calculations start: The goal here is to define an analysis period that you want to generate annualized return data for, input a few data points and go from there. The most important number to put in starting off is column AD as that’s the analysis period you’re going to look at, column AC is fairly significant as well as that’s your top marginal tax rate.
The annualized return is calculated based on the profit from a sale and the profits from renting the property out.
Other Notes:
· You have to put in the total interest paid via the Mortgage Amortization spreadsheet included with the other spreadsheets, the amortization spread is real simple, just put in the same mortgage data for the particular property you’re working with and it generates 5-year interest data and 5-yr equity increase data. If you need data for periods other than that, you’ll have to use the schedule to gather that info on your own.
· You have to put in the equity increase as well via the Amortization spreadsheet
After the above, the spreadsheet will tell you, the profit gained from the property after the equity is factored in. Put in the anticipated future appreciation rate in column in Column AO and the property value at sale is calculated.
The spreadsheet than calculates the proceeds from the sale after the realtors commission is factored in and the loan is paid off, next it calculates gross profits based on the proceeds and rental profits (or loss).
Property Analysis Personal – Assumes that you’re just planning to live in the houses and “may” rent out part of it for additional income, but at the end of the day, it’s just your home. If you understand the above spreadsheet, it’s easy to see where this one is different.
---------------------------------
peace & blessings,
x.
"I'm on the Zoloft to keep from killing y'all." - Iron Mike
my philosophy on free time:
"and next time when he get it he'll waste it on somethin' useful" - MF Doom
peace & blessings,
x.
www.twitter.com/poetx
=========================================
** i move away from the mic to breathe in
|
