Excel 2007

What-If Analysis Tools

There are three What-If analysis tools that you can use. To access these, select the Data tab and locate the What-If Analysis command. If you click this command, a menu with three options appears.

Goal seek is useful if you know the needed result but need to find the input value that will give you the desired result. In this example, we know the desired result (a $400 monthly payment) and are seeking the input value (the interest rate).

Goal Seek

To Use Goal Seek to Determine an Interest Rate:

• Select the Data tab.
• Locate the Data Tools group.
• Click the What-If Analysis command. A list of three options appears.

• Select Goal Seek. A small dialog box appears.
• Select the cell that you want to set to a specific value. In this example, we want to set B5, the Payment cell.

• Insert the cursor in the next field.
• Enter a value in the value field. In this example, type -$400. Since we’re making a payment that will be subtracted from our loan amount, we have to enter the payment as a negative number.

• Insert the cursor in the next field.
• Select the cell that you want to change. This will be the cell that tries various input values. In this example, select cell B4, which is the interest rate.

• Click OK.
• Then, click OK again. The interest rate appears in the cell. This indicates that a 7% interest rate will give us a $400 a month payment on a $20,000 loan that is paid off over five years, or 60 months.