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 will base their price on the potential gross income, and he expects a multiple of 19 – 21x 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 at 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 will equal to 2014 plus the Holding period.
I'll now apply my HLOOKUP, so I'll write "=hlookup" and open a bracket.
My lookup value will be the year of sale, my table array will 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 Num.
The first row, which includes our years, is equal to row number 1.
The blank row will equal to row number 2, and the Potential Gross Rent row will equal to row number 3, so I'll enter 3.
And finally, in our Range lookup, I'll want an EXACT MATCH, so I'll write FALSE.
I'll 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 and multiply it by the Potential Gross Rent at Sale.
And if I extend this column so I can see the value with ALT + O C A, I'll see that our sale price is almost $2,000,000.
Let's now check to see if this works for different holding periods, so I'll change the Holding period to 7.
This reduces the Potential Gross Rent at 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.