MVP #56: Learn How To Use Goal Seek in Excel With This Interesting Example

, , No Comments
Goal seek is one of those powerful but seldom used tools in Excel.

It allows you to set-up a small model and tell Excel to optimize it for you based on one variable input and one set output. It’s the perfect introduction to a model and linear programming in Excel.

Let’s see a simple and common use case. Below is a loan calculation table. Say I have found a huge business opportunity in large scale cocoa farming and I want to borrow N100 million from the bank. The table below is the conditions the bank gave me: a payment period of 10 years and annual interest rate of 24%.

Excel has a formula for calculating the annual payment amount.

So I go and check my business financial projection, and find out that I can only afford to make N20 million annual payment. What rate will I negotiate with the bank? 

This is where Goal Seek comes in. We simply tell it to find out what interest rate will evaluate to N20 million annual payment.

To access Goal Seek, go to Data menu and What-If-Analysis.

I set payment amount cell to N20 million and tell Goal Seek to vary the interest rate.

Once I click on OK, it does a series of iteration and gives me the result.

I should ask for 15% annual interest rate.

It's now your turn to find that interesting use of Goal Seek.


Post a Comment

You can be sure of a response, a very relevant one too!

Click on Subscribe by Email just down below the comment box so you'll be notified of my response.