Goal Seek in VBA: Goal Seek is a handy Excel function that helps to determine the required input value needed to achieve a targeted output value - and this process can be automated using a VBA macro!

Goal Seek using a VBA macro.

One of the functions of the What-If-Analysis in Excel, which can be found under the Data tab, is Goal Seek. Essentially, the function uses a trial & error approach for the unknown input cell until it arrives at the value required to reach a targeted output.

But before we get started explaining how to automate Goal Seek in VBA - why would we even want to automate this process in VBA if the regular Goal Seek function is so easy and handy to use once you know how it's done?

Renewable Energy Valuation according to target IRR case example

Suppose you would like to value a renewable energy asset based on a target IRR while also sculpting debt according to a DSCR target. In that case, you will have an interdependency of different variables. Once your financial model is set up and ready to be used, suppose you would like to determine the enterprise value of the energy plant.

According to our Valuation Dashboard below, the asset's enterprise value from a developer's perspective is 44.4 mEUR. Essentially, this is the figure of how much it costs to develop the asset from a pure capital expenditure and development cost perspective. But what if we know that a potential investor would probably expect a levered IRR target of 6.0% when valuing the asset?

In addition, we know that a debt-financing bank will constrain the maximum leverage for the asset at 80% leverage and will most likely also put different DSCR constraints in place. If we would like to automate the process of finding the right amount of debt through debt sculpting and valuing the asset in accordance with a target IRR, we can only automate this process by using Goal Seek in VBA.

So how to implement the Goal Seek function in a VBA macro?

To understand how to implement Goal Seek in VBA, look at the short line of code required within the VBA interface.

Before getting into the details of how to implement Goal Seek in VBA, you might wonder why you see a named range called Target_NPV in each argument of the code. Didn't we want to value the asset in accordance with a target IRR? You're absolutely right! However, if we try to solve our required input variable - the enterprise value of the asset - to achieve a target IRR of 6.0%, Excel will struggle to get the input variable right.

The percentage 6.0% as a decimal is 0.06, so Excel is supposed to adjust a very small number to solve the Goal Seek. Instead, we can simply use a workaround and ask Excel to set the Net Present Value (NPV) of the asset to zero while using a required rate of return for the XNPV function of 6.0% - this will ensure that our asset achieves a levered return of 6.0% and Excel will not have issues solving the Goal Seek in VBA.

Step-by-step implementation of Goal Seek in VBA:

1. Name all required ranges within the worksheets
   
   1. Target_NPV_delta: The first argument is a delta between the actual NPV in the live case of the financial model and the target NPV we would like to achieve - we need Excel to keep solving the Goal Seek until this range equals zero.
   2. Target_NPV_output: The second argument is the actual NPV in the live model - if it is different from zero, Excel will need to keep solving the Goal Seek.
   3. Target_NPV: This is the target that needs to be achieved - setting the NPV to zero.
   4. Target_NPV_input: This is the Enterprise Value of the asset - Excel will change this variable until we achieve our desired output - an NPV of zero.
2. Once all the required ranges are named within the workbook, you can open the VBA interface - the shortcut to open the interface is ALT + F11. You just have to make sure that the developer is enabled within your ribbon.
3. Open a Module in the VBA interface to start writing your code.
4. Write a Sub statement and call it TargetIRR() in accordance with the code line above - using an apostrophe ' you can also give the macro a short explanation so that other users can easily understand what you have done.
5. Implement a Do Until & Loop line around the Goal Seek function. Behind "Do Until", you need to tell Excel that you want this macro to keep solving until your Target_NPV_delta range is zero. We want Excel to keep solving the macro until our NPV equals zero.
6. Implement the Goal Seek line
   1. Implement Range("Target_NPV_output").GoalSeek - Excel knows this is the output we would like to achieve using Goal Seek.
   2. Set the goal: Goal:=Range("Target_NPV"), - we tell Excel that we want the output to equal our target NPV, which is zero.
   3. Set the input which is supposed to be changed: ChangingCell:=Range("Target_NPV_input") - this is the Enterprise Value of the asset, which Excel needs to keep adjusting until we achieve our target NPV being zero.
7. Don't forget to put a Loop at the end of the Do Until argument.
8. Assign the macro within your spreadsheet to a button and execute it.

So how much will a potential investor pay for our asset?

After running our Goal Seek macro, we know that a potential investor would be willing to pay approximately 76.2 mEUR for our asset, given an estimated IRR expectation of 6.0%. After subtracting the costs of the asset, we end up with a solid profit of 76.2 - 44.4 = 31.8 mEUR. Well done, developer!

You might also notice that the overall gearing of the asset dropped from 80% to 65%. This is because the debt financing bank set certain constraints for the DSCRs within the sculpting process.

How to build an advanced financial model for renewable energy investments?

Do you want to learn how to implement such complex but handy VBA macros within a financial model created explicitly for renewable energy investments? Then check out the Advanced Renewable Energy Financial Modeling course.