Microsoft Office Tutorials and References

In Depth Information

**Chapter 16: What-If Analysis and Goal Seek**

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.

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

—

) Figure

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

used

“

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

s requirements

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

CRTL

(tilde), you can see all the formulas on the sheet. These formulas are

shown again on the right side of Figure 16.3.

þ
B

FIGURE 16.1
Car Loan Example Model Used for Analysis