Microsoft has built the Goal Seek function to aid your spreadsheet development. Since Excel 2019, it has been available from the Data ribbon. In earlier versions of Excel, it was present from the Tools menu. It gets straight to the point. It asks you which value you would like to fix (in this case the investor’s return), what you would like to fix it to, and asks what you would like to change. All fields can accept cell references. It will then calculate the input through trial and error.

The good news is that Goal Seek can be accessed and run from Visual Basic. An Excel program can write a short script to ensure your variables get re-calculated each and every time you alter your spreadsheet. As Goal Seek takes a noticeable period of time, this could adversely affect your sheet’s performance. The beauty of accessing Goal Seek from Visual Basic is that you can switch off all the processes, such as refreshing the screen, that cause the function to run slow.

John Abrams is a technical expert, he has written technical blogs, manuals and reviews for many websites such as office.com/setup