Especially when valuing property, it is useful to be able to calculate the IRR based on the cash flows up to that year and include a cash flow as if the property was sold that year. This way you can see at which stage the IRR is positive and how much you are relying on a profitable sale to generate your IRR.
YouTube IRR assuming sale (TV) in each year
Theoretical IRR based on cash flows to date and sale
In the example below we have the cash flows in row 37, and we have calculated what the sale value would be in each year in row 40.
In row 42 we want the IRR for each year as if we sold the property in that year. So in year 3 (IRR shown in G42) we want to know the IRR of spending 100 000 in year 0, earning 3 550 in year 1, 7 970 in year 2, 12 910 in year 3 AND selling it for 161 376 in year 3 (so ignore the other potential sale values).
To do this we need to create one additional row in row 41. All this row does is add up that years cash flow plus the potential sale value in that year. This is because IRR works consecutively, so if we tell it to look at year 3 cash flow and then year 3 sale value, it will treat the year 3 sale value as year 4 which is incorrect.
How to use IRR with non-adjacent cells in Excel
Once this is set up the IRR formula needs to be told to look at non contiguous (fancy word for not adjacent) cells.
By default you have to highlight one range when giving IRR the cash flows. However, if you wrap it in its own brackets you can specify more than one. So if you look at the IRR function below,
=IRR( ( $D$37:F37,G41 ) )
note that (in an additional set of brackets), we have told Excel to look at cells D37 to F37 and cell G41 as if they were one range.
With clever use of the dollar signs we have set it up to calculate the IRR each year as if that is the year of sale.
Want to learn more about Microsoft Excel and using it when budgeting or forecasting? If you prefer live courses and live in South Africa look at the MS Excel training courses available. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.