Wednesday, November 28, 2007

Ending the argument: An easy to use spreadsheet to determine the true monthly cost of ownership


It is often the case when perusing the local blogs, that I'll notice "comment battles" will break out over whether x property made money, or how a particular owner would have faired if they had rented instead. Invariably someone will posit some theory, and then someone else will point out what they forgot, and then someone else will point out what they forgot, and it isn't long before the first posters point is forgotten, and the conversation drifts aimlessly along the lines of "tastes great - less filling" without any sincere effort to assemble all the information together and get a definitive, or at least semi-definitive answer.
I've decided to fill in the breach. I don't claim it's the be all end all, but it's a simple easy to use calculator that can at least give you a rough idea of whether a given property came out ahead, or might even help you decide if buying a particular property is in your best interest:

http://spreadsheets.google.com/pub?key=pM4Gw0s2zSeCXIvKktNGLbg

I'm looking for feedback, so if you see something I left out, or some way to improve it, let me know.

[update: I am working on creating an editable web based spreadsheet for everyone to use. In the meantime, you can edit the spreadsheet by following these instructions (thanks anonymous for leaving this tip in the comments!):
Go down to the blue links at the bottom and click either Edit or Google Docs. You may have to sign in or register but once the file opens in a new window click on File and then Copy. A new spreadsheet will open where you can edit the cells.]

32 comments:

Anonymous said...

I just linked to this from my blog, theFrontSteps. Good work!

Missionite said...

Hi Jash,
I haven't ever published a spreadsheet before using Google docs, so I was wondering how that would work. I will make a downloadable copy today, or figure out how to make an updateable one.

Anonymous said...

Yea, I could not edit the green rows either. Good idea - if you can get it to work, i'll be back.

Anonymous said...

To edit: go down to the blue links at the bottom and click either Edit or Google Docs. You may have to sign in or register but once the file opens in a new window click on File and then Copy. A new spreadsheet will open where you can edit the cells.

Anonymous said...

I was hipped to your blog by TFS. The chart is great - I had a client who had developed a similar tool to figure out what he could afford.

I linked it to my blog. It's also a new blog for people in the bay who aren't rich (some of them might just want to live in a shitty condo though, thank you very much!)

Missionite said...

Hi Farrah,
Thanks for checking it out. I can't take all the credit, as many of the concepts and formulas I employed in the spreadsheet were suggested or taught to me by other people, on other blogs. I am going to be making continual improvements as time goes by, so feel free to steer your clients here if you think they will find it helpful.

I would also love to check out your blog and add it to the blogroll here as well, so give us a link, dammit! :)

Missionite said...

Oh and the "shitty condo" comment is a bit of a tweak on socketsite which seems to focus on condos to the point I want to rip my eyes out. I'm a SFH kind of guy, but not to the exclusion of all else, and you will notice I included a provision for HOA payments in the spreadsheet. So it's meant in jest, not in earnest. :)

Anonymous said...

Hi again missionite. I'm sorry I forgot to give you a link before, here it is - www.apieceofthepie.typepad.com. There're a few "shitty condos" in the most recent post, just divert your eyes and scroll down until you see the SFHs - j/k!

I'll absolutely direct people to the chart. The one person who visited my blog yesterday was probably really impressed.

Anonymous said...

I was able to figure out how to edit the spreadsheet. cool. thanks for your hard work in putting together this useful tool.

To make this tool more flexible and useful, I suggest you make the "Homeowner's insurance" category an editable "green field."

In my experience as a SF howeowner, 0.46% is really, really high, especially for those owners who do not take out earthquake insurance. I think my annual insurance cost is more like 0.2%.

Missionite said...

Farrah,
Great I'll check it out and add it to the blogroll. Thanks for reading!

SFTim,
I have taken your advice, and updated the spreadsheet accordingly.

Anonymous said...

The mortgage interest exclusion in your spreadsheet assumes the payment is 100% tax deductible. Only the interest component is deductible, so the longer you hold the property, the lower the deduction becomes. To accurately model this aspect, you need to build out the year-by-year (or month-by-month) principal and interest components of the mortgage payment. This is especially necessary for anyone looking to see how renting vs. buying plays out over several years instead of one or two.

Missionite said...

No, the spreadsheet does not assume the payment is 100% tax deductible, furthermore only the interest that is deductible is deducted (i.e. under $1 million), and the interest calculation is absolutely correct and tested.

The formula I use computes the amount of interest paid for a given time period for a mortgage, so it correctly calculates that the interest is front loaded, and totals the interest paid over the time period. I have checked this against financial calculators and verified that it is correct, and you can verify this yourself by going to bankrate.com and using one of their mortgage calculators.

Missionite said...

Sorry, to be clearer, I should have said "only the interest on the first million of the purchase is deducted".

There is also the interest on an additional $100k that is apparently deductible as well. This can change based on filing status (AMT, etc.) I'm researching this, and will be incorporating it into the spreadsheet as soon as I'm confident I understand how it works. But this shouldn't have a material effect on the calculations of owning a home for 3-5 years.

Unknown said...

Nice spreadsheet! but there appear to be a few, um, inaccuracies.
Using the $1.4M example:
If deposits for the renter are $15,000, (2months + damage) then interest lost on funds would be $1625, not $5001.76.
((deposit * rate)/12) * Months
Then the investment income from the difference between rent and the owners costs; since most of the costs accrue monthly, and the sales costs are only at the end of the ownership period, then you won't get as much as is shown in income. The $170000 down and $42,000 purchase generate $22,966 over 26 months at 5%; then the income on the monthly costs should be calculated like a mortgage; avoiding the roughly $116000 in monthly or bi-annual payments only will generate about $6700. in income.
So the renter is out of pocket $118,024 + $1625 = $119,649, and has income from investment of avoided expenses of $29,666.
The homeowner blew $285,417 - $20000 gain = $265,417 (after deducting tax benefit from interest ).
So, Renter is ahead ($265,417 - 118,024) + $29,666 = $177,059.
or $6809 per month.
Thats still grotesque...

Missionite said...

Hrmm,
When I have paid a deposit it was always just one months rent. Two months rent is a new one on me. And $6k in damage? What kind of parties do you throw? Cause I want to go to them! :)
But that brings up a good point that deposit amounts can vary from landlord to landlord. I have added a field for deposit amount, and you can now plug in your own deposit and the interest will adjust accordingly.

Missionite said...

Red,
I have read three times the second half of your comment, and I still can't quite figure out what you are saying.
I think you are talking about the monthly contribution to investment in cell C79.
That cell basically adds up all your fixed monthly out of pocket expenses (mortgage, property tax, HOA and insurance) and then subtracts your average rent over the period.
It does not factor in other annual or irregular owner expenses such as taxes, repairs, maintenance, etc. So the true spread is actually wider.

Sales costs aren't factored into this at all, nor are they factored into what you would do with the down payment if it was free to invest. Maybe you could explain your thoughts here further?

Unknown said...

RE:Deposits.
I used to be a landlord, and the move in was first and last and 1 months rent in damage; higher if furnished. Might not be able to get that legally in SF... anyway, figured out the problem there - the formula for interest on the deposit shouldn't have (1+C75), should be just C68*(C75)*(C20/12)
Does $5001.76 in interest on $4500.00 at 5% annual seem high to you?
Again, great spreadsheet!

Unknown said...

RE: C79
I think the monthly costs should be figured after taxes, particularly given the example tax bracket value of 43.3%, and since both Interest and Property tax are deductable. That reduces the monthly difference between renting and owning considerably, enough to make it seem that owning is cheaper than renting. In this case, its the transaction costs that kill, anyone buying in a near flat (or declining!) market and flipping a property is really going to eat it. Thats why I would prefer those costs to explicitly show in the Rent income section; purchase costs with interest.
If folks had really studied what owning costs before buying, prices would not have gotten so extraordinarily out of whack; a lot of people are really going to get hurt as they drop back to reality. This spreadsheet analysis should at least save some from the pain.

Missionite said...

Red,
In 20 years of renting, I've never paid for damage up front. I'm glad you weren't my landlord! :)

I see what the problem is: you are a little confused about what is contained in cell C70.
Cell C70 is the interest PLUS principal. So $501.76 in interest, plus the deposit of $4500 = $5001.76.

The formula I have in there is computing compound interest, such as you would get if the money was sitting in a money market account. Putting in the formula you propose would deliver a number that does not take into account compound interest, and would leave out the principal. The formula I am using is the correct one, trust me, and since the number in C70 is added to the total "expenses" of renting, you definitely want to include the deposit in this field (although you get it back - in theory - at the end of the rental, you don't have access to it for the entire period, so it is properly considered an expense in my book)

Missionite said...

Red,
If I understand you correctly, you want to treat the expenses on the rent side as "after tax" income. But it's all after tax income!

Taxes have been removed as a factor by treating deductible expenses on the owner side as "income".

You can't treat taxes as "income" on the owner side, and then also call it an "expense" on the renting side. That's double counting the impact of taxes. You must choose one or the other, and since all owners gets the same deductible I've gone with treating deductions as "income" for owners.

I'll greatly simplify it so you understand:
Let's say I make $100/year in taxable income. I'm in the 33 percent tax bracket, so I owe $33 a year in taxes. My after tax income then is $67.
Now let's say I buy a house. I pay $1/month mortgage, of which .83 cents is interest on the loan. So over the course of a year I get $10 in tax deductions (.83 * 12= $10), then that means my taxes go down by $3.30 ($10 * .33 tax bracket) which means my after tax income goes up by $3.30, which means my after tax income is $70.30.

I still have to pay the mortgage, so we reduce that $70.30 by $12, and I'm left with $58.30 after taxes.

Under my spreadsheet I am treating that $3.30 as "income" on the ownership side.

So we have this:
Annual income: $100
Deductions: $10
Taxable income: $90
Taxes: $29.70
After tax income: $70.30
Mortgage: $12
Income after taxes and mortgage: $58.30

If I make the same exact income and I'm renting for the exact same amount as I pay in mortgage, $1/month, but I don't get the deduction on interest, it looks like this:
Annual income: $100
Taxes: $33
After tax income: $67.
Rent Expense: $12
Income after taxes and rent: $55

Do you see now? The only difference between the two is the $3.30 the owner gained in deductions! So I call that "income" and it's accounted for. I call mortgage an "expense" and it's accounted for.
I call rent an "expense" and it's accounted for.

If I re-calculate the expenses on the rental side as "After tax" I'm essentially taxing the renter twice!

Anonymous said...

Eeek! C72, the interest lost by the renter on the deposit, is giving me the giggles. The value works out at 105% with a 5% rate.
Try setting the rate to 0% and you still pay 100%. Even my credit cards don't get that.

(Interest @ ann. appr% + deposit )= C70*(1+C77)^(C20/12)
Gotta extra 1+ ; should be
= C70* C77 *(C20/12)

Unknown said...

RE: C70
Assume you don't get the rent deposit back? had some real *!@ landlords, eh? When I was a landlord, I gave almost everyone the full deposit back, even the guy who knocked the place off its foundation. (he claimed it was the earthquakes fault)
Anyway, the renter "loses income" on the full amount that the landlord holds, which is first, last and deposit; and with a little effort cleaning, should get the deposit back, so it is not an expense, only the income lost on it is. The renter has provided a sizable loan to the landlord; here in Santa Cruz, interest must be paid on those funds.
If you annotated the cell "You aren't going to get it back, sucker", then I'll live with it, I suppose.

Unknown said...

Well, yes, I am a simple guy, and I just couldn't figure out why those numbers down there at the bottom of the spreadsheet didn't work for me. So, simple me, I started setting the gozintas to zero so that the numbers that come out could be followed.
So, zero down payment, zero costs, zero HOA, no Insurance,no selling commission, zero Renovation & Maint. 12 months owned. Selling price same as purchase.
Then I took the "Total Payments during Ownership" and subtracted the "Total Tax Savings" (same as annual now) and then divided by the 12 months.
That number is the owners out of pocket. ( Its also what the realtor will tell you is your real cost...)
Then I set the RENT to the same amount. No deposits, no increase.
So now the owner should come out ahead, since she is building some equity, while the renter has exactly the same monthly costs. Right?
Oops, down there at the bottom is that line "Monthly contribution to investment" and its not zero! oddly enough, it is exactly the same as the monthly tax benefit the owner gets from the IRS. (ok, after you fix your W2s to claim 40 children)
Cost of Renting is now greater than the loss on ownership, but the Rent "Income" calculation still computes out a sizeable "Total Rental Income".
The resulting "Own Vs Rent Difference" is thus not right either.
I agree with the premise that the renter should be investing the difference between the homeowners costs and renting and that the difference should be acknowledged. But the generous IRS benefit to the home owner should not be ignored. There ARE times that buying does make sense, and that tax benefit makes a big difference; when I bought my home it meant I still had some money left for food (well, after renting out a couple of rooms).
So, to sum up: the owner is not burning ( Monthly Mortgage + Monthly Property tax); the owner IS burning ( ( Monthly Mortgage + Monthly Property tax) - Monthly Tax Deduction).

Missionite said...

Simply interested,
C72 is principal+interest. So setting appreciation to 0% will still leave you with 100% of the principal, dig?
You aren't the first to be thrown by that, so I've tried to clarify this on the latest iteration.

Red,
I like your approach to testing the numbers. And while I started writing this comment thinking one thing, after trying to walk through this with you, I caught a minor error (accidentally added a small number twice), and then shortly afterwards a gigantic one, probably the biggest one so far. Essentially I wasn't treating the savings from the tax deductions as relevant. Instead I was calculating the taxes that would have been owed, and adding those to the expenses. Well it's clear to me now that this is definitely biased towards the renter, because in the spreadsheet the renter isn't getting taxed on their income, but the owner is. So I have corrected the sheet and started doing as you suggest: subtracting the tax savings from the out of pocket expenses. I think this is the right path, and I'm kicking myself for not seeing this sooner. I feel like an ass.

So I really want to thank you for taking the time to articulate your point. Because it forced me to walk through the numbers again from a different point of view, and I don't know if I would have discovered these errors otherwise.

I have made the corrections, and I think things are improved now.
Run your scenario again, and see if the result matches your expectations.

Anonymous said...

Hello !.
might , perhaps very interested to know how one can manage to receive high yields .
There is no initial capital needed You may begin to get income with as small sum of money as 20-100 dollars.

AimTrust is what you thought of all the time
AimTrust incorporates an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

It is based in Panama with affiliates around the world.
Do you want to become a happy investor?
That`s your chance That`s what you really need!

I feel good, I started to get income with the help of this company,
and I invite you to do the same. It`s all about how to select a correct companion who uses your funds in a right way - that`s the AimTrust!.
I make 2G daily, and my first investment was 500 dollars only!
It`s easy to join , just click this link http://mypoququsa.jamminweb.com/gujiqehy.html
and go! Let`s take this option together to feel the smell of real money

Anonymous said...

Hello !.
You may , perhaps curious to know how one can reach 2000 per day of income .
There is no need to invest much at first. You may begin to get income with as small sum of money as 20-100 dollars.

AimTrust is what you thought of all the time
The company incorporates an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

It is based in Panama with affiliates everywhere: In USA, Canada, Cyprus.
Do you want to become an affluent person?
That`s your choice That`s what you really need!

I`m happy and lucky, I started to get income with the help of this company,
and I invite you to do the same. If it gets down to choose a proper companion utilizes your savings in a right way - that`s the AimTrust!.
I earn US$2,000 per day, and my first deposit was 1 grand only!
It`s easy to start , just click this link http://jyxaninyl.kogaryu.com/fexadak.html
and lucky you`re! Let`s take this option together to get rid of nastiness of the life

Anonymous said...

Hello !.
You may , perhaps very interested to know how one can reach 2000 per day of income .
There is no initial capital needed You may commense to receive yields with as small sum of money as 20-100 dollars.

AimTrust is what you haven`t ever dreamt of such a chance to become rich
The company represents an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

Its head office is in Panama with offices around the world.
Do you want to become an affluent person?
That`s your chance That`s what you wish in the long run!

I`m happy and lucky, I started to take up income with the help of this company,
and I invite you to do the same. If it gets down to select a correct partner who uses your funds in a right way - that`s AimTrust!.
I make 2G daily, and my first deposit was 1 grand only!
It`s easy to get involved , just click this link http://vecafokygi.angelcities.com/fegufim.html
and go! Let`s take this option together to become rich

Anonymous said...

Hi !.
You re, I guess , probably curious to know how one can reach 2000 per day of income .
There is no initial capital needed You may begin earning with as small sum of money as 20-100 dollars.

AimTrust is what you haven`t ever dreamt of such a chance to become rich
The company incorporates an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

Its head office is in Panama with offices everywhere: In USA, Canada, Cyprus.
Do you want to become an affluent person?
That`s your chance That`s what you really need!

I feel good, I started to get real money with the help of this company,
and I invite you to do the same. If it gets down to select a proper partner utilizes your funds in a right way - that`s AimTrust!.
I make 2G daily, and my first deposit was 1 grand only!
It`s easy to join , just click this link http://ufucynet.angelcities.com/ewovon.html
and lucky you`re! Let`s take this option together to become rich

Anonymous said...

Hi !.
You re, I guess , perhaps very interested to know how one can manage to receive high yields .
There is no initial capital needed You may commense to get income with as small sum of money as 20-100 dollars.

AimTrust is what you need
The company incorporates an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

Its head office is in Panama with structures around the world.
Do you want to become an affluent person?
That`s your choice That`s what you really need!

I`m happy and lucky, I began to get real money with the help of this company,
and I invite you to do the same. It`s all about how to select a proper partner utilizes your money in a right way - that`s the AimTrust!.
I earn US$2,000 per day, and my first deposit was 1 grand only!
It`s easy to get involved , just click this link http://aqowaqune.uvoweb.net/yjovuh.html
and lucky you`re! Let`s take our chance together to feel the smell of real money

Anonymous said...

Well I to but I dream the post should have more info then it has.

Anonymous said...

Very remarkable topic

Anonymous said...

Hello everyone!
I would like to burn a theme at here. There is such a thing, called HYIP, or High Yield Investment Program. It reminds of ponzy-like structure, but in rare cases one may happen to meet a company that really pays up to 2% daily not on invested money, but from real profits.

For several years , I make money with the help of these programs.
I don't have problems with money now, but there are heights that must be conquered . I get now up to 2G a day , and my first investment was 500 dollars only.
Right now, I'm very close at catching at last a guaranteed variant to make a sharp rise . Turn to my blog to get additional info.

http://theinvestblog.com [url=http://theinvestblog.com]Online Investment Blog[/url]