Absolute Beginners Guide to Quattro Pro X3

You can use "What-If" tables in Quattro Pro to see the effects of substituting different values for variables in formulas. Suppose, for example, that you are thinking about buying a house. You've determined that the purchase price is $125,000 and you're going to put down 20% as your down payment and borrow $100,000 for 30 years. Mortgage rates are readily available at 8.5%, but you'd like to know what your monthly mortgage payment would be if you paid points to lower the annual interest rate on the loan. You want to know what your monthly mortgage payment would be for each annual interest rate between 5% and 8.5% in half-point increments. Essentially, your "what-if" question is, "What will my monthly mortgage payment be if the annual interest rate on my loan is 8.5%? 8%? 7.5%? 7%?" and so on.

To answer your question, you use the PAYMT function, which requires a monthly interest rate, a loan term, and a loan amount as the three variables it uses to calculate a monthly mortgage payment. You should use the same timeframe for the interest rate and the loan term to accurately calculate the payment. Because you want to calculate a monthly mortgage payment, express the interest rate and the loan duration in months when you set up the formula; divide the annual interest rate by 12 and multiply the number of years on the loan by 12. You can see the sample formula in the Formula bar in Figure 13.10.

You can set up the formula for each of your scenarios, as shown in Figure 13.10, by copying and pasting loan assumption information enough times to test all the interest rates you want. Then, edit each interest rate cell, shown in row 7, to supply a different rate. This method makes you work harder than you need to work, however.

Instead, you can set up a one-variable What-If table, shown in Figure 13.11. In this case, you supply the loan assumptions only once without specifying an annual interest rate. Then, you set up a two-column table to act as your What-If table. The left column of the table contains the values you want to substitute in your calculation; in Figure 13.11, these values appear in B9.B16.

In the right column of the table and in the cell above the first value you want to test, type the formula you want Quattro Pro to use when it substitutes valuesin this case, the PAYMT function shown in cell C8. Make sure that one variable of the formula points to the blank cell in the top-left corner of the tablethe cell immediately above the values you want to test and to the left of the formula. This blank cell is called the input value, and in this example, I used B8 as the input cell for the variable in the PAYMT formula that I want to test.

Then, open the Tools menu, point to Numeric Tools, and click What-If Tables. Quattro Pro displays the What-If window shown in Figure 13.12.

Select the One Free Variable Option and then click the Range Picker button beside the What-If Table option. When Quattro Pro collapses the window, select the cells containing both the formula you want to calculate and values you want to substitute in the formula; in this example, I selected B8.C16.

Next, click the Range Picker button beside Input Cell option and select the blank cell above the substitution values and to the left of the formula; in this example, I selected B8.

Click the Generate button, and Quattro Pro places, in the cells beside the various interest rates, the result of the formula obtained by changing the variable in the formula that points to the input cell at the upper-left corner of the What-If table (see Figure 13.13).