Sign in or start a free trial to avail of this feature.
10. Estimating the Final Selling Price
The final selling price of our asset will be based on an income multiple. This variable is hard to predict but will hugely impact our final IRR.
Asset sale price
- Our client has told us that the sale will be dependent on Potential Gross Rent
- We will use a multiple of Potential Gross Rent to calculate sale price
- In our client's view, this multiple is 19x - 21x
Calculating the final Potential Gross Rent
- The final Potential Gross Rent will be dependent on the Holding Period
- Use the HLOOKUP function to return the final Potential Gross Rent from the model
- Alternatively, INDEX/MATCH functions can also be used
HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)
--- lookup_value: The value you want to search for
--- table_array: Array that has the lookup row as row 1 and the returned row
--- row_index_num: The row number from which you want to return a value
--- range_lookup: Always write false, unless you want an approximate match
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
CTRL + →: Move to the last cell in the data region
SHIFT + →: Select next cell
ALT + O , C , A: Autofit column
Our next modeling task is to calculate the sale price of our asset.
In conversations with our client, we know that the valuation in the market is typically determined by a multiple of one of three metrics. Potential gross income, effective gross income, and net operating income. In Joe's view, most would-be buyers would base their price on the potential gross income. And he expects a multiple of 19-21 times the potential gross rent at the time of sale. Let's stay conservative and assume that this multiple is 19. In Excel, I've created a new section called intermediate variables which includes the year of sale and potential gross rent and sale. I've also added in a sale price in the outputs section. To calculate the sale price, we first need to calculate the potential gross rent at the time of sale. Obviously, the time of sale will vary due to the holding period. So in our financial model, we won't be able to link to a specific cell to find this value. Instead, we're going to look along the row of years for the correct year of sale, and return the potential gross rent that corresponds to that year. To do this, we can use index match or a HLOOKUP. I haven't used the HLOOKUP much so far so let's try this method for a change. Going back to our control panel, I'll start by calculating the year of sale which would equal to 2014 plus the holding period.
I now apply my HLOOKUP so I'll write equals HLOOKUP and open a bracket.
My lookup value would be the year of sale. My table array would be in the financial model and consist of all of the values in the year row and the potential gross rent row.
Next is the row index known. The first row, which includes our years, is equal to row number one. The blank row will equal to row number two, and the potential gross rent row will equal to row number three. So I'll enter three.
And finally, in our range lookup, I want an exact match so I'll write false, and then close the bracket, and press enter.
And this tells me that in the year of sale 2023, a potential gross rent of $104,013 will occur.
To calculate our sale price, I simply multiply this value by the gross rent multiple.
So I'll select the gross rent multiple, multiply it by potential gross rent and sale.
Let's now check to see if this works for different holding periods. So I'll change the holding period to seven. This reduces the potential gross rent and sale to just under $100,000, and if I go to my financial model, I see that this is the correct value. So our sale price calculation is working correctly. Obviously, if the asset achieves a higher multiple, say 21, this can have a big impact on the sale price and result in a much higher return for Joe.