Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS), 2nd Edition

Chapter 16

What-If Analysis and Goal Seek

What-if analysis allows the user to test the impact on the outcome of a model by changing values in certain cells. This chapter describes one aspect of the What-if analysis procedure—the Goal Seek. The following chapters will continue with other What-if analysis, such as analysis with data tables and scroll bars.

The Goal Seek concept is demonstrated with the same car loan example that was introduced in Chapter 15. See Figure 16.1 for the model and formulas.

FIGURE 16.1 Car Loan Example Model Used for Analysis

Let me go over the example again. You are purchasing a car for $22,000. You are required to pay $4,000 as a down payment. The annual interest rate is 8.00 percent and the loan duration is three years. The payments are made at the end of period. Some loans require a beginning of the period payment, such as a mortgage, and others, as in this example, require it at the end. The payments are monthly payments.

The loan formula in cell B4 is clear. It is the price minus the down payment. To calculate the Payment amount, you have to use the Excel financial function called PMT, which returns the periodic payment for an annuity. (As the Excel menu defines it, PMT “calculates the payment for a loan based on a constant interest rate.”) Figure 16.2 illustrates the function. Note that for the Rate Argument, I used Rate/12 and for the number of ...