One of the most common uses for a spreadsheet is to answer what-if questions. In asking such questions, you typically create a worksheet using functions and formulas to make the various calculations you're interested in. You then test various combinations of figures to determine the outcome for each. For example, you might test how different sales scenarios will affect your income, how different contribution amounts and return rates will affect your 401(k) plan, or any one of a myriad of day-to-day home or business calculations.

Once you've run a few sets of figures through a spreadsheet model, you'll want to keep track of the various combinations of data and results, and this is where Excel's Scenario tool is handy. It manages the various scenarios for you, permanently storing the combinations of figures and letting you retrieve and display them at any time. In addition, and perhaps of more value still, the Scenario tool lets you create a master list detailing each scenario and its results, so you can compare them side by side.

The easiest way to understand Excel's Scenario tool is to use it in a practical situation, such as this loan-calculation worksheet. To make the basic sheet shown in Figure 1 ( Click Here ), type the following entries into the indicated cells in a new Excel worksheet:

B2   Loan calculator
B3   Loan amount
B4   Annual interest rate
B5   Payments per year
B6   Years to repay
B8   Payment due per period
B10   Total payments
B11   Total interest paid
C3   150000
C4   .065
C5   12
C6   25
C8   =PMT(C4/C5,C5*C6,C3)*-1
C10   >=C5*C6*C8
C11   =C10-C3

The PMT function in cell C8 calculates the payment on a loan where the interest rate and payments are constant over the life of the loan. The syntax of the function is PMT(rate,nper,pv), where rate is the period interest rate, nper is the number of payments made, and pv is the loan amount. The only thing to ensure when you use this function is that you reduce the annual interest rate to a period interest rate by dividing the annual rate by the number of payments per year. So a 6 percent annual rate becomes a period rate of 6 percent/12 or 0.005, when you make 12 payments per year. As you are making payments on the loan, the result from the PMT formula will be a negative number, so we've simply multiplied by -1 to make it positive for aesthetic reasons.

The formula in cell C10 simply multiplies the period payment by the number of payments to calculate the total amount you'll have paid by the time the loan is repaid in full. The formula in cell C11 calculates how much of this amount represents interest.