Microsoft Office Tutorials and References
In Depth Information
Chapter 16: What-If Analysis and Goal Seek
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
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.
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
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
16.2 illustrates the function. Note that for the Rate Argument, I used Rate/12 and for
the number of periods, the Years*12, since I am calculating monthly payments. Also I
calculates the payment for a loan based on a constant interest rate.
Loan (minus sign Loan). The payment function requires a minus sign for the
loan so that the monthly payment will be positive; this is part of Excel
for the Payment function (otherwise it will result in a negative value). See Figure 16.2.
The function calculates the monthly payment for the loan to be $564.05. I
calculated the last two cell values, B8 and B9. For the Total Payments we used
Total_Payments -Loan. The results
are shown on the left side of Figure 16.1. When you use CTRL
Payment*Years*12 and for the Total Interest:
(left apostrophe) or
(tilde), you can see all the formulas on the sheet. These formulas are
shown again on the right side of Figure 16.3.
FIGURE 16.1 Car Loan Example Model Used for Analysis