Michael Rogers of Chandler Properties provided the following Excel Worksheet for BiggerPockets to share with all of our members.

REI Property Analyzer

We hope you all find it comes in handy.

NOTE: If anyone else has their own worksheet that they'd like to share, please get in touch.

Ahhh...this is just what I need. Thank you.

Thanks Josh! This is just what the Doctor ordered.

I came to this forum looking for exactly this! Wow, what a great surprise!

I'm glad everyone is finding it helpful.

This is great! Could you explain the depreciation portion to me? I put in a rental property that I am considering and It looked to cashflow until depreciation put it into the negatives. Is this a good thing. What is the real life scenario that plays out here?

Depreciation is a phantom deduction, it does not reduce your cash flow.

Hey Josh, to answer your question in more depth, you are allowed to depreciate your building (not the land) over a 27.5 year period. The advantage of this is that on paper it makes it so you look like you took a net loss when you really (hopefully) took a net gain. The IRS will give you a portion of your losses back. Lets say you make a net income of $2400 a year and your depreciation is $100,000 / 27.5 = $3636. It actually looks like you lost $1236 that year. The IRS will you give you back a nice chunk of this. If you start to make over $100,000 from your job and you are a part-time real estate investor, benefits like these will start to go away and they will go away completely at $150,000. However, those numbers are based on your "modified adjusted gross income" so if you contribute to your 401k or pre-tax medical plan it will look like you make less money.

Barry,

I think there is a better explanation.

Depreciation expense is how the IRS lets you recover the cost of the building that is supposedly wasting away. The depreciation expense is the IRS allowance for the portion of the building's value that has been eroded through use in a rental activity.

The depreciation expense is not an actual out of pocket expense, but it still offsets my net rental income making my taxable rental income even lower. lf the depreciation expense is greater than my net rental income, then I have net rental loss for tax purposes, even though the property may have generated a positive cash flow.

Provided I meet the income limitations, I am allowed to use my net passive losses to reduce the amount of income tax I might have to otherwise pay on my other ordinary income. If I don't meet the income limitations, I can save the net rental loss for next year and try again or wait until the property is sold to claim my net rental losses.

Thanks Dave, I do believe your explanation was more accurate and also augmented my explanation. I had inaccurately stated that on paper the depreciation makes it look like you took a net loss when you really took a net gain when this is not always the case. When you say you can "save them for next year and try again", do you mean by attempting to adjust your gross income to meet the requirements? If so, do the previous years combine to give you the total benefits that you would have received had you been eligible from the get go? Thanks.

I had a question of my own concerning this worksheet. What is the purpose of adding back the principal payments and why does it affect taxable net income? Does this change year by year depending on how much each mortgage payment is contributing to paying down the principal?

Hey Barry,

The purpose of adding back the principal payments is that you are going from an analysis on a purely "cash flow" basis to a "Net Income" basis. The principal portion of your loan payment has no effect on net income; just the interest portion. Line 26 (Annual Debt Service) includes both interest and principal. The principal piece must be backed out to determine taxable net income.

Yes, the amount of principal being backed out should increase each year (assuming you have a standard amortizing loan).

I hope this helps.

Originally posted by Barry Robbins:
When you say you can "save them for next year and try again", do you mean by attempting to adjust your gross income to meet the requirements? If so, do the previous years combine to give you the total benefits that you would have received had you been eligible from the get go? Thanks.

The "them" I referred to seemed vague when I reread my post so I edited it for clarity. I am talking about the net rental loss that you could not use to reduce your other ordinary income because your income was too high.

Rental loss is a passive loss As a general rule, you can only use passive losses to offset passive income. The one exception to this rule is called a "net passive loss allowance". When your passive loss is from a real estate rental activity in which you actively participate, the IRS will allow you to use up to $25K in net rental losses to offset other ordinary (active) income, subject to income limitations.

If you have a net passive loss that you can not use, the loss is suspended and carried forward to the next tax year, where is it used to offset rental income, income from other passive income activities, and lastly other ordinary income if allowed within the income limits.

If you sell the property, any suspended passive losses can be taken in full without regard to the net passive loss allowance income limitations.

Did this help?

Michael backs out the principal contribution because it is not a deductible expense on your tax return. You have to pay it if you have an amortizing mortgage loan, and it does reduce your cash flow, but it is not counted as a deduction when computing your taxable rental income.

If you look closely at Michael's spreadsheet formulas, you may notice that the amount of principal he backs out is understated, making the taxable rental income slightly lower than it really is, or a taxable rental loss slightly higher than it really is.

This is not really a problem for a quick and dirty cash flow analysis because the number that is really important to you is the net cash flow before taxes. That number for a property you might be thinking about buying needs to be in your "ball park" of acceptable cash flow for you to even proceed further.

Yes, the principal portion of your amortizing loan annual debt service will change each year, as will most of the expenses you are allowed. Property taxes, insurance premiums, repair costs usually increase each year, but hopefully your rental income increased faster.

:D Thank you! Thank you! This spreadsheet is wonderful. I just joined BiggerPockets, and already, I am thrilled.

What a great tool! Thanks Michael and Joshua.

this was really helpful :)

Thanks for the spreadsheet. It is very helpful.

Question on the worksheet: If I just bought a duplex for 100K, closing costs of 5K, repairs 15K., 20K down-payment, How do put that into the Cost Information section? Do I just put 100K for Building Cost, 20K for Land cost (closing and repairs)?

I tried to put my number in. It did not work so well in my situation.i have second mortgage.

Originally posted by Marcus Lee:
Question on the worksheet: If I just bought a duplex for 100K, closing costs of 5K, repairs 15K., 20K down-payment, How do put that into the Cost Information section? Do I just put 100K for Building Cost, 20K for Land cost (closing and repairs)?

Marcus,

There are only a few fields on this spreadsheet you need to fill in,

For your example, enter $100K in the "Acquitision" cost field (highlighted in yellow).

Your $20K downpayment is 20% of your purchase price. If you will have an interest only loan, then go to the Loan Details section and enter 20% in the "Down Payment (%)" field under Interest Only 1st Balance, otherwise, enter 0% in this field..

If you will have an amortizing loan, enter 20% in the next column P&I 1st Balance. Then for the"Principal" enter $0 in the Interest Only 1st Balance column and enter $80000 in the P&I 1st Balance column. You also need to enter the annual interest Rate you are being charged on your financing.

In the Initial Cost of Investment section at the bottom left, enter $5000 for Closing Costs to Buy and $15000 for Cost of Additional Repairs

You are done.

This is a very good basic worksheet. For a more sophisticated model that works with all forms of commercial real estate, try using [REMOVED]

It's not free ($39) but it is a whole lot cheaper than ARGUS.

Thank you Joshua this is what I've been looking for. I appreciate it.

Someone posted a comment about this not working for more mortgages.

For those with two or more mortgages you can use a weighted average for all the loans and enter them in as one payment. This will suffice, even if the terms are not the same, because you are analyzing on an as-is basis.

If you want to analyze out further, or work in present value analysis for weighing a choice between two investments, you might want to step up to a more sophisticated spreadsheet.

Craig,

It appears that the spreadsheet does accomodate a first and second mortgage.

The depreciation expense is not an actual out of pocket expense, but it still offsets my net rental income making my taxable rental income even lower