Solving for a Variable

I like this one; this is the one where you already have the answer and you just need to know what number to plug into the formula to get the answer.

Suppose, for example, that you're buying a new home. The purchase price on the home is $125,000 and the interest rate the mortgage company is offering is 6.5%. If you assume that you'll put nothing down, your monthly mortgage payment would be $790.09 on a 30-year, fixed-rate mortgage.

But suppose that you have some extra cash and you don't want to be house-poor with a monthly payment that is larger than you really want to pay. In fact, suppose that you want to pay only $550 per month. How much would you need to put down to have a monthly payment of $550?

In Figure 14.7, I set up the purchase price of the loan in B2. I left B3 empty. The term of the loan12 months/year times 30 years equals 360 monthsappears in B4, and the interest rate appears in B5. In B6, I entered a formula that subtracts the contents of B3 from B2. And, in B7, I used the PAYMT function, which uses the monthly interest rate (B5/12), the loan term (B4), and the loan amount (B6) as variables. Now, I want to know how to make the result of B7 equal $550. I'll also find out how much I need to put down and what my loan amount will be when I make this calculation.

Figure 14.7. How much should I put downand subsequently borrowto end up with a monthly payment of $550?

Open the Tools menu, point to Numeric Tools, and click Solve For. The Solve For dialog box appears (see Figure 14.8).

Figure 14.8. Use this dialog box to identify the value of a variable when you already know the solution.

In the Formula Cell box, use the Range Picker to select the cell that contains the formula you're solving; I selected B7.

In the Target Value box, type the value you want the formula to result in; for this example, I typed 550.

In the Max Iterations box, you can specify the number of passes Quattro Pro can make to solve the formula. In the Accuracy box, you can specify how close you want Quattro Pro to be to the target number.

In the Variable Cell box, use the Range Picker to select the cell that Quattro Pro can change to arrive at the result you specified in the Target Value box; I selected B3, the down payment amount.

Click OK. Quattro Pro sets the Monthly Payment amount at the value you specified in the Solve For dialog box. Quattro Pro also fills in the value needed for the down payment. And, because my formula for the Loan Amount subtracted the down payment from the Purchase Price, Quattro Pro updates my loan amount also (see Figure 14.9).

Figure 14.9. Quattro Pro sets the result of the formula to the value you specified and then calculates the variable(s) needed to achieve the result.