In the previous post on product mix optimization we learned to use 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 rental income portfolio by the developer for long term.

In this post we are going to explore how we can use Excel solver tool to optimize the 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 which we had used in our previous post. If you remember, earlier we had defined 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 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 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 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:

Market research team has also provided the development director additional information on rental rates, occupancy and rent escalation for various asset classes:

Also from the quantity surveyors we have followings cost information:

We will be using 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 same so it is irrelevant. But if the land price is not 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 comment, 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.