When you have multi-year of incremental investment in rental markkets, calculating returns gets complex. The simple way is just to:
1. calculate net sum by year (the net of down payment, rents collected, maintenance, mortgage payments etc);
2. add mark to market property value (minus mortgage balance if any) to the final year.
3. plug only the year by year net cash flow into EXCEL IRR function.
Here is the example with little-landlord's very impressive numbers.
I made a few adjustments:
1. Reorder net rents by calendar years, not by year of acquisition;
2. I doubled rents for 2014 acquisition to reflect full year value;
3. Added total mtm property value to 2014 EoY cash flow as exit value. Note this is just for convenience of calculation. The investment does not have to be liquidated.
So each of the first 2 years have negative CF as incremental investments overwhelm net rents. The next 3 years see incremental investment largely financed by current year rents. In the final year, cas flow is very positive due to huge MTM property value gains.
The IRR over the 5 year holding period is a very impressive 40%. That is without leverage, granted it is not just return to capital as there are lots of bloods and sweats involved to achieve this.
This is probably slightly inflated as the timing of investment and rent collection is assumed to be the same. If we lag the rents by one year, ie shift the investment to the beginning of the year and rents to the end of the year, then
which is still very impressive. 27% is probably too low as it assumes rents collected lag initial investment by a full year. So the true number is probably somewhere between 27% and 40%, say 32-32%.
just my 2c.
**************************************************************************************************************
For those of you who are EXCEL-oriented, below is the implementation of HPR Calculation using EXCEL IRR functions.
1. calculate net sum by year (the net of down payment, rents collected, maintenance, mortgage payments etc);
2. add mark to market property value (minus mortgage balance if any) to the final year.
3. plug only the year by year net cash flow into EXCEL IRR function.
Here is the example with little-landlord's very impressive numbers.
I made a few adjustments:
1. Reorder net rents by calendar years, not by year of acquisition;
2. I doubled rents for 2014 acquisition to reflect full year value;
3. Added total mtm property value to 2014 EoY cash flow as exit value. Note this is just for convenience of calculation. The investment does not have to be liquidated.
So each of the first 2 years have negative CF as incremental investments overwhelm net rents. The next 3 years see incremental investment largely financed by current year rents. In the final year, cas flow is very positive due to huge MTM property value gains.
The IRR over the 5 year holding period is a very impressive 40%. That is without leverage, granted it is not just return to capital as there are lots of bloods and sweats involved to achieve this.
|
Year
|
Investment
|
Net rents
|
Exit Value
|
Net Cash Flow
|
|
|
(outflow)
|
(inflow)
|
|
|
|
2009
|
$ (662,217)
|
$ 96,350
|
|
$ (565,867)
|
|
2010
|
$ (375,000)
|
$ 164,400
|
|
$ (210,600)
|
|
2011
|
$ (192,879)
|
$ 200,500
|
|
$ 7,621
|
|
2012
|
$ (250,500)
|
$ 257,100
|
|
$ 6,600
|
|
2013
|
$ (318,700)
|
$ 286,800
|
|
$ (31,900)
|
|
2014
|
$ (547,200)
|
$ 367,800
|
$ 3,997,000
|
$ 3,817,600
|
|
HPR (IRR)
|
|
|
|
40%
|
This is probably slightly inflated as the timing of investment and rent collection is assumed to be the same. If we lag the rents by one year, ie shift the investment to the beginning of the year and rents to the end of the year, then
|
Year
|
Investment
|
Net rents
|
Exit Value
|
Net Cash Flow
|
|
|
(outflow)
|
(inflow)
|
|
|
|
2008
|
$ (662,217)
|
$ -
|
|
$ (662,217)
|
|
2009
|
$ (375,000)
|
$ 96,350
|
|
$ (278,650)
|
|
2010
|
$ (192,879)
|
$ 164,400
|
|
$ (28,479)
|
|
2011
|
$ (250,500)
|
$ 200,500
|
|
$ (50,000)
|
|
2012
|
$ (318,700)
|
$ 257,100
|
|
$ (61,600)
|
|
2013
|
$ (547,200)
|
$ 286,800
|
|
$ (260,400)
|
|
2014
|
0
|
$ 367,800
|
$ 3,997,000
|
$ 4,364,800
|
|
HPR (IRR)
|
|
|
|
27%
|
which is still very impressive. 27% is probably too low as it assumes rents collected lag initial investment by a full year. So the true number is probably somewhere between 27% and 40%, say 32-32%.
just my 2c.
**************************************************************************************************************
For those of you who are EXCEL-oriented, below is the implementation of HPR Calculation using EXCEL IRR functions.
IRR is an excel built-in function. cf0 - cf_n are the cash flows in the last column of the table. the key is to make sure that money going into your pocket and going out of your pockets have opposite signs.
|
Year
|
Investment
|
Net rents
|
Exit Value
|
Net Cash Flow
|
|
|
(outflow)
|
(inflow)
|
(inflow)
|
|
|
2008
|
-662217
|
0
|
|
=SUM(I16:K16)
|
|
2009
|
-375000
|
96350
|
|
=SUM(I17:K17)
|
|
2010
|
-192879
|
164400
|
|
=SUM(I18:K18)
|
|
2011
|
-250500
|
200500
|
|
=SUM(I19:K19)
|
|
2012
|
-318700
|
257100
|
|
=SUM(I20:K20)
|
|
2013
|
-547200
|
286800
|
|
=SUM(I21:K21)
|
|
2014
|
0
|
367800
|
3997000
|
=SUM(I22:K22)
|
|
Sum
|
|
|
|
|
|
HPR (IRR)
|
|
|
|
=IRR(L16:L22,0)
|
IRR is the discount rate that sets the net present value of all cash flows to zero.
