In class, we covered how to Goal Seek. Sometimes, it’s necessary to run the goal seek tool many times, such as if you were trying to find the price of something at a specific yield, under a range of scenarios.
One simple workaround for this is to call the goal seek tool from a macro. To do this, give a range name to the “Set cell” (call it of “change”) and another range name to the “By changing cell” (call it “target”). Making the “To value” equal to zero will simplify to exercise, as well. A helpful starting point is to record a macro using the Goal Seek tool, and adjust as necessary. Below is a subroutine I wrote to automate the task.
You can use the goal seek tool directly or via a macro. Either way, please solve for the purchase price necessary to hit the below unleveraged IRRs in this real estate model. I put this model together to evaluate a real estate project in Japan.
|Unleveraged IRR||Purchase Price|
Here’s the simple program.
‘ This just automates the Goalseeking feature of excel.
‘ First, assign the range name “change” to cell that contains the
‘ value you are going to change. (e.g. the price)
‘ Second, identify the result cell. (e.g. yield)
‘ Third, name another cell “target” and make it the difference between
‘ what the yield is currently and what you want it to be.
‘ The macro sets “target” equal to zero by changing the cell “change”
‘ Using range names is helpful if you have to backsolve the same
‘ analysis multiple times.
range(“target”).GoalSeek Goal:=0, ChangingCell:=range(“change”)