12. Calculate Investment IR
To calculate our investment IRR, we need to combine the initial investment, operating cash flows and cash flows on sale. See how to calculate IRR in this lesson.
Expected cash flow profile
- Most real estate investments have similar cash flow profile
- At the start, a large negative cash flow, representing the price paid for the asset
- In the middle, a series of small positive cash flows that represent operating cash flows
- At the end, a large positive cash flow representing the sale of the asset
Internal rate of return (IRR)
- For IRR, we need to find the investment cash flows for each year
- To do this, we combine initial costs, operating cash flows and cash flows at sale
- The IRR function then accepts the investment cash flows as an array input
Note: The client should probably be using MIRR as a better metric for returns
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
CTRL + C: Copy selected item(s)
SHIFT + →: Select next cell
ALT + E , S , F: Paste formulas
CTRL + →: Move to the last cell in the data region
With all of the components of our model now in place, it's time to calculate our cash flows and Internal Rate of Return.
Most investments typically follow the same cash flow profile.
They will have a large negative cash flow at the beginning, which will be the initial purchase of the asset, small cash flows for a number of years, which will be the operating cash flows, and a large positive cash flow at the very end which will be the asset sale.
Our investment will be no different.
In our model, I've split the investment cash flows into three sections: Initial costs, Operating cash flows, and Cash flows on sale.
Let's start with the initial equity investment to buy the asset.
This is going to be a negative number and it will be equal to the PURCHASE PRICE minus the LOAN AMOUNT.
Next will be the purchase costs, which again will be a negative number, and that will be the PURCHASE PRICE multiplied by the PURCHASE COSTS, which is a percentage.
Next up are the Operating cash flows, which I can copy in from row 89.
Here they are, and then I'll copy across for the remaining cells.
Lastly, we have the cash flows on the Sale of the asset, and the timing of these cash flows will be determined by the year of sale.
So I'm going to use an IF function to check if the current year is equal to the year of sale, and if it is, I'll enter these cash flows.
So starting with selling costs, I'll write "=If", and I'll check if the current year is equal to the YEAR OF SALE, and if it is, I'll return the selling costs which are equal to the SELLING PRICE multiplied by minus the SELLING COSTS, which is a percentage.
If the year isn't equal to the year of sale, I'll return 0, and I'll copy this across for the remaining cells.
Next up are the Proceeds from sale, and again, I'll use the same approach.
I'll check if the current year is equal to the YEAR OF SALE, and if it is, I'll return the SALE PRICE, and if it isn't, I'll return 0, and again we can copy across.
Off camera, I'll do the same for Mortgage balance and Taxes paid on sale.
Note that for the Taxes paid on sale, I had to anchor this term because it does not extend out across the sheet.
Let's now calculate our Investment cash flows, and I'll do this by simply SUMMING the column, and I'll copy this across for the remaining cells.
To calculate our IRR, I'll move back to the dashboard, write "=irr" in the selected cell and then select the array of Investment cash flows.
And this tells me that our project has an IRR of 9.8% for the current set of inputs.
When our client reviews the model, he wants us to find out what variables most affect the return and what he would need to assume for the 15% threshold to be hit.
Let's find out how to do this in the next lesson.