Update: this post has been revised to reflect fixes to problems with the NPV analysis in the previously posted spreadsheets.
Last week Mike Piper wrote an interesting post about calculating expected return for a home purchase, and it got me thinking about a similar kind of analysis for investment property.
The spreadsheet 5-yr-income-prop.xls shows expected return and Net Present Value (NPV) for an income property investment held 5 yrs. Three cells are colored dark orange to indicate that these three cells must be recalculated if you change the number of years.
I used one of the figures from Mike’s post which he got from William Bernstein’s The Investor’s Manifesto: Preparing for Prosperity, Armageddon, and Everything in Between, a book I’ve been wanting to read. Specifically, I used the 1% inflation-adjusted growth rate for home prices.
For the imputed rent dividend, I substituted the effective gross rent income (EGI) multiplied by the tax rate and divided the result by the home purchase price. The 4.84% dividend could be unrealistically high given the low price to annual rent ratio of 14.88. The Finance Buff commented on Mike’s post that this ratio is 20-25 in many markets. However, a recent (excellent) article War of Values in the December issue of SanFrancisco Magazine about the Lembi family noted that a studio apartment in the Marina district of San Francisco rents for $2,000, so in the Bay Area it’s possible that a $350,000 one-bedroom condo might rent for $2,000.
The home is financed with a 30-year fixed mortgage at 5%; there’s a separate sheet showing the full amortization of the loan.
I suppose rents increase annually, but I kept them the same per year throughout the life of the investment. I also kept the marginal tax rate and standard deduction the same for all years, though these could certainly change. The inflation rate is arbitrarily set at 2.2%, but it obviously could change every year.
In comparing the after-tax, after-inflation interest rate of 2% to the 3.06% expected return, the property looks like a good investment, but if it’s held only 4 years the property has a negative NPV due to the negative annual after-tax cash flows (ATCFs), short amount of time for appreciation and high loan balance at the point of sale. Usually in evaluating investments, you want to reject projects with a negative NPV.
However, if the property is held 5 yrs the NPV is positive. So in this case the property should be held at least 5 years.
At least in the case of income property, it’s a good idea to analyze the investment using more than one measure of return.
Please let me know if you find any mistakes in the spreadsheets!
{ 3 trackbacks }
{ 3 comments… read them below or add one }
Everything looks good to me. All I might do is some styling to make it clear for end-users which cells are “input” cells and which are “output” cells.
Good point. I made these changes plus fixed a bug in the NPV calculation for the longer-term scenario. Thanks for reviewing!
I’m not as much interested in investment property, but I would think this could also help with planning to purchase property to live in. It would be along the same lines, live in the house more than 5 years and make a decent profit, versus planning to move in less than 5.
thanks for the post!