• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Stephen L. Nelson

Author. Accountant. Aspiring Apiarist.

  • Articles
  • e-Books
    • Maximizing Section 199A Deduction
    • Setting a a Reasonable S Corporation Salary
    • Small Business Tax Deduction Secrets
    • Real Estate Tax Loopholes & Secrets
    • DIY LLC Formation and Incorporation Kits
    • Sample LLC Operating Agreements
    • Sample Corporation By-Laws
  • Contact

Simple Modeling with Goal Seek in Excel

May 18, 2015 By Stephen L. Nelson Leave a Comment

You can use the Goal Seek command to determine which input value for a formula pro- duces a specified formula result. Goal Seek, then, lets you quantify what needs to happen in order to achieve some specified result. Rather than supplying a set of formula inputs and letting Excel calculate the result, you supply a formula result, all of the input values except one, and then let Excel iteratively determine what the missing input value needs.

For example, suppose you want to determine the interest rate that would result in a $1,400- a-month payment on a $100,000 loan with a 10-year repayment term using the workbook shown in Figure 6-14. Cells B1, B2, and B3 hold values. Cell B5 holds the formula shown below to calculate the monthly interest payment:

=PMT(B1/12,B2,-B3)

Figure 6-14. A simple loan payment workbook.
Figure 6-14. A simple loan payment workbook.
If you have questions about how the PMT function works, refer to Chapter 5’s discussion of this function and Excel’s other financial functions.

To determine the rate input value required for your specified formula result, follow these steps:

  1. Choose the Tools menu’s Goal Seek command.
    Excel displays the Goal Seek dialog box (see Figure 6-15).

    Figure 6-15. The Goal Seek dialog box.
    Figure 6-15. The Goal Seek dialog box.
  2. Specify which cell’s formula you want to return a specified result.
    Use the Set Cell text box to specify the cell that holds the formula Excel will attempt to set to the specified value. For the example workbook shown in Figure 6-14, you would specify the Set cell as B5.
  3. Specify this result.
    Use the To Value text box to specify the formula result you want. For the example work- book shown in Figure 6-14, you would specify the To Value as 1400.
  4. Indicate which input cell should be adjusted.
    Use the By Changing Cell text box to specify the input cell that Excel should adjust in an attempt to calculate the formula result you want. For the example workbook shown in Figure 6-14, you would specify the By Changing cell to B1. Then click OK. Excel adjusts the input cell to a value that produces the desired formula result and displays the results in the Goal Seek Status dialog box (see Figure 6-16).

    Figure 6-16. The Goal Seek Status dialog box.
    Figure 6-16. The Goal Seek Status dialog box.

Goal Seek typically finds the correct input cell value in a fraction of a second. If your cal- culations are very cumbersome, however, Goal Seek may take longer. When this happens, you can click the Goal Seek Status dialog box’s Stop button to terminate the search. Or you can click the Pause button to temporarily suspend the search.

One common way to make Goal Seek’s calculations more complicated or cumbersome is when you have it calculate a string of formulas. For example, this might happen when you ask Goal Seek to calculate the input cell value to a first formula, which supplies a result to the second formula, which supplies a result to a third formula, which is the formula you want to produce a specified result.

Filed Under: Finance, Using Excel Tagged With: business modeling

About Stephen L. Nelson

Stephen L. Nelson is the author of more than two dozen best-selling books, including Quicken for Dummies and QuickBooks for Dummies.

Nelson is a certified public accountant and a member of both the Washington Society of CPAs and the American Institute of CPAs. He holds a Bachelor of Science in Accounting, Magna Cum Laude, from Central Washington University and a Masters in Business Administration in Finance from the University of Washington (where, curiously, he was the youngest ever person to graduate from the program).

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Article Categories

  • Accounting
  • Business Planning
  • Finance
  • Real Estate
  • Statistics
  • Taxes
  • Using Excel

Copyright © 2025 Stephen L. Nelson, Inc. · Contact · Steve’s Bio · Publications · Glossary