, , , , ,

Calculate Residual Land Value in Excel

Here’s the scenario. You’re a real estate developer. You spot a prime parcel of land that would be perfect for your real estate project. So you approach the owner of the land about selling and she says, “Okay, bring me an offer.” How much do you pay? What is the land even worth to you? What if there are no comparable properties to use to accurately appraise the land, how can you justify the offer you’re about to give the seller? One way to arrive at an appropriate price is calculate the project’s ‘Residual Land Value.’

a green field ripe for real estate development

What is Residual Land Value?

Residual land value is a method for calculating the value of development land. This is done by subtracting from the total value of a development, all costs associated with the development, including profit but excluding the cost of the land. The amount left over is the residual land value, or the amount the developer is able to pay for the land given the assumed value of the development, the assumed project costs, and the developer’s desired profit.

Quick and Easy Way to Calculate Residual Land Value

I’m going to make this quick and easy. Using an existing real estate development model with land cost as an input, let me show you how to use Excel’s Goal Seek tool to calculate the residual land value for a real estate project.

  1. Open up a real estate development Excel model that has land cost as an input. Any model with land cost as an input will do, but in this case I’ll use my Residential Land Development Pro Forma.
  2. Fill in all of the project assumptions (blue fonted cells) except for land cost.
  3. Identify the return or profit target for your project. In my case, I’m going to use a 3.0X equity multiple as my profit target.
  4. With a profit target in mind, navigate to the ‘Forecast’ section on the ‘Data’ ribbon of Excel and select ‘What-If Analysis’. Then choose ‘Goal Seek…’
  5. With the ‘Goal Seek…’ window open, first link the ‘Set Cell‘ box to the cell that outputs your profit target value. In my case, I link the ‘Set Cell‘ box to cell E14.
  6. Next, set the ‘To Value‘ box equal to your profit target. In my case, I set the ‘To Value‘ box to 3.
  7. Finally, link the ‘By Changing cell:‘ to the ‘Land Cost‘ cell and hit ‘Ok‘. In my case, I link the ‘By Changing cell:‘ box to cell I5.
  8. Excel then iteratively changes the land cost value (in cell I5) until the equity multiple (in cell E14) equals my target (3.0X).

The resulting value in the ‘Land Cost‘ cell is the residual land value. If I believe all of my other inputs, I can with confidence pay the seller the resulting land cost (i.e. residual value) and feel confident the project will yield my desired profit.

Watch Me Calculate Residual Land Value

As simple as this is, it never hurts to also see it done. I’ve recorded a video calculating residual land value using my Residential Land Development Pro Forma. Check it out and let me know if you have any questions:

About the Author: Born and raised in the Northwest United States, Spencer Burton has over 14 years of real estate investment and development experience. In his current position, Spencer assesses new investments for a $40bn real estate fund. He resides with his wife and kids in Dallas, TX.