Nate Oliver's blog

Today's Blog entry comes from a real-World experience I once had, while purchasing a large asset on a deferred payment schedule. The asset's price, like many large assets, wasn't fixed, but negotiable. So, prior to heading into negotiations, our team wanted to understand what various cash flows would look like if we altered the payment schedule to match a targeted Internal Rate of Return (IRR).

In order to expedite this process, I used an Excel-native utility known as Goal Seek. In fact, in order to further expedite the process, I used Goal Seek in a VBA procedure. To find Goal Seek, pends on your Excel version. Pre Excel 2007, it can be found in the 'Tools' area. Post Excel 2007, you'll want to click into the 'Data' tab, and explore the 'What If Analysis' button.

Goal Seek, Solver's younger brother, is pretty slick. It allows you to change a cell's value based on a calculated value, elsewhere. There is a trick to implementing Goal Seek when you're dealing with small numbers, like percentages. You want to change your calculation options to enable iteration, as desribed here:

Office Online Link

Maximize the number of iterations, to 32767, as mentioned, here:

Office Online Link #2

And crank your max change way down, to a very small number. As the first link notes, this will slow Goal Seek down a little bit, but your results will be much better. I.e., there's not much point in getting the wrong result, faster.

I'm attaching a simplified example of my construct. It contains a simple VBA procedure, and it is provided with no assurances or warranties - if you chose to open it, you do so at your own risk. That VBA procedure is as follows:

Sub BackSolve()
Range("B6").GoalSeek _
    Goal:=Range("B8").Value, ChangingCell:=Range("G2")
End Sub

This procedure allows us to bypass the Goal Seek UI dialog, and simply process our request. What does it do? It modifies the year 5 cash payment (outflow), in Range G2, by changing to our targeted IRR in Range B8, which is an input cell (yellow). You'll note as you run this that your calculated IRR, in Range B6, will update to match our request, due to our cash outflows adjusting. This is another good trick to modeling - tieing out your results.

While the example is a simplified one, it can be implemented in larger, more sophisticated models. And with that, Happy Labor Day Weekend!