Realistic IRR

[Gaurav Bahal]

Hi BP'ers,

As I am digging more into analyzing REIs I am realizing that ROI is not a good metric compared to IRR because ROI does not factor the time value of money. This becomes especially important when I am holding a property for a long time and hoping for the appreciation and equity to be a large component of return.

My question is:

1. What is a realistic after tax IRR for a class B property with ~10% year one Cash on Cash return and 4% average annual appreciation? I am assuming 30 year time period and am getting 12% after tax IRR. The same property gives me a average 40% ROI for 30 year hold.

2. Does anyone have a spreadsheet to plug in assumptions and get the basic ratios and metrics including IRR for a REI?

Thanks,

Gaurav

[Dion DePaoli]

An IRR equation is not hard to setup in excel. Let's walk through it:

In period 0 you will need the cost basis of the investment expressed as a negative number (outflow).  So if you purchase the property plus closing costs for $100k then you put in ($100,000) or you can use -$100,000.  Those both mean the same thing.  Next moving down or to the right, however you want to setup your time interval you will put each period's net cash flow.  So if you want to solve for after tax income you will need to use after tax cash flow.  Since it is after tax each period will be 1 year.  Let's say after tax income is $7,000.  The setup will look like this:

        A               B

1      P0         ($100,000)
2      P1         $7,000
3      P2         $7,000
4      P3         $7,000 

.....to end of years

Then you simply put in the IRR equation in to a cell: =IRR(B1:B4) or wherever the cash flow ends. If each period is a year then the result will be the annual IRR. You can use monthly periods and the resulting IRR equation will deliver a monthly IRR which you can simply multiply by 12 to find the annual IRR.

There have been some discussion threads around using IRR that you can search for.  We use IRR all time.  Do understand though, that at a certain time into the future income, taxation and expenses all become somewhat speculative.  

Generally if you get an error in the IRR equation it is because you do not have a cost basis (negative number) in the beginning.  Also, if you get a negative IRR it means your cash flow didn't pay back the initial investment.

12% IRR is a good return.