Goal Seek is one of the most powerful features in Excel, and it’s really easy and fun to use.
Goal Seek allows you to back-solve for a single input value that will cause a single output to be equal to a value you specify. For instance, let’s say in a simple operating projection model you have property Purchase Price as an input, and Year 1 cap rate as an output .
If you are seeking to acquire the property at a 6.00% cap rate, the NOI for Year 1 is a calculated value, and you are solving for the Purchase Price that will result in a 6 cap, then you would do the following in Goal Seek (Alt+T+G keyboard shortcut, or Data tab > What if analysis > Goal Seek)
Set cell: (cell coordinates of) Year 1 Cap Rate (this cell must be a calculation-based output cell)
To value: 6% (or .06)
By changing cell: (cell coordinates of) Purchase Price (this cell must be a user input cell)
When you are done making your inputs, click OK, and then (if the input cell in question is in your field of view) watch the input value change rapidly as it converges on the input amount that solves the equation. Once it stops changing, the Goal Seek dialog box will show the Target value you input and the Current value.
If you wish to keep the Current value, click OK. If you want to revert back to the original input value, click Cancel.
The beauty of Goal Seek is that with even a simple model you can get a good grasp of the feasibility of a deal by asking and answering questions such as:
- What does rent PSF have to be for us to get a 6 cap assuming a purchase price of X?
- How low would construction costs PSF need to be for us to build to an 8 cap?
- What is the maximum land purchase price I could afford to pay for the assuming rents of X and construction costs of Y if I need to build to a 10 cap?
These are fun questions to answer using our free Back of the Envelope templates as well as in Valuate, which has an even slicker version of Goal Seek built in that only requires a single keystroke instead of 3 inputs and 2 clicks.
Want to learn 25 more Excel power techniques? Join our webinar this week.
$20 off with coupon code: toolkit1Register Here