In the previous post on product mix optimization we learned to use the Excel solver tool to optimize mixed-use real estate development.
The main assumption in that post was that the products will be sold. Many readers asked a similar model where the properties will be held in a rental income portfolio by the developer for long term.
In this post, we are going to learn product mix optimization for rental properties and explore how we can use the Excel solver tool to optimize mixed-use real estate development where properties are held for rental income.
If you are new to Excel Solver, you should refer back to the original post which explains the solver tool in very detail [Link Excel Solver].
In this post, we will be using a similar situation to understand product mix optimization for rental properties that we used in our previous post. If you remember, earlier we had defined an objective function to maximize margin. However, in rental properties margin will not work.
So what can we maximize? Can we maximize internal rate of return (IRR)? Or Net Present Value? Or something else?
I selected net present value to define the objective function because of two reasons - firstly NPV is additive, and secondly, it takes the time value of money into consideration (unlike margin).
Sample Problem Construction
Assume that there is a mixed-use development being planned on a land parcel. The municipal authority has provided a few guidelines for the development:
Maximum allowed GFA = 250,000 SqM
Minimum Community Buildings GFA = 10%
Maximum allowed commercial GFA = 35%
The developer has acquired the land and has hired a master planner. The product mix has not yet been finalized. The developer will be holding the assets for the long term i.e. these properties will be leased.
The market research team has completed the market research and recommended that by the time project is constructed and delivered to the market, the market situation will be such that it can absorb the followings without affecting either occupancy or the rental levels:
The market research team has also provided the development director with additional information on rental rates, occupancy, and rent escalation for various asset classes:
Also from the quantity surveyors we have the followings cost information:
We will be using the following cap rate and discount rate:
It is just an assumption and you can use anything you want.
Before moving to the solver, we need to do the cash flow projections for the rental income taking into account the construction cost and the terminal value.
For the cash flow of other assets, you can refer to the Excel workbook attached at the bottom of this post.
If you notice we have calculated net present value (NPV) per SqM. It is fine as NPV is additive. We have ignored the land cost assuming that land cost for all the asset classes are the same so it is irrelevant. But if the land price is not the same for all the assets, then you can easily include the land price in the cash flow projection.
Now we need to construct the problem ready for the solver. Let's tabulate the recommended GFA and NPV/SqM for each asset class:
The objective function can be defined as:
Maximize: Z = 134 X1 + 35 X2 + 177 X3 + 50 X4 + (-86) X5
Subject to:
You will notice that some of the constraints are overlapping; so we need to rearrange the constraints as follows:
Now the non-negativity constraints
X1, X2, X3, X4, X5 >= 0
Applying Solver
We have already formulated our problem in Excel, and it is almost ready for applying solver.
You should have the solver add-in enabled and should set the solver as follows:
If you are new to the solver, please refer the original post.
Once you run the solver, you we get the answer report.
You will notice that the net present value is maximized for the given set of constraints.
What If Solver Doesn't Find a Solution?
It may happen that Solver returns an error that "Solver could not find a feasible solution."
This may happen because of the following reasons:
- Your model is under or over-constrained
- Your model constraints are impossible to meet
- Your model constraints have an error
These can be easily resolved by checking the current constraints for errors and ambiguities
Hope you enjoyed this post on product mix optimization for rental properties; if you have any comments, let me know through the comment section below.
Also, you can download the Product Mix Optimization for Rental Properties Excel model for free from the below link.
Are you someone involved with real estate feasibility?
We are excited to launch the next generation of real estate feasibility software by April 2023.
claim your 15% early-bird discount
Know more about the product features
Is there any easy method on this, that is simple?