[HW 2.4b] VBA: Goal Seek with range names

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
8.00% 898,065
7.75%
7.50%
7.25%
7.00%
6.75%
6.50%
6.25%
6.00%

 

Update:
Here’s the simple program.

Sub GoalSeekToZero_with_rangenames()
‘======================================================================
‘ 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”)
End Sub

 

 

 

Advertisements

2 comments

  1. Where does the 898,065 purchase price come from? In the model, the initial purchase price in the XIRR calculations that return 8% seems to be 874,679

    Like

    1. Oh never mind, figured it out

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: