Excel 2007

What-if analysis

Watch the video! (5:18 min)

Download the example to work along with the video.

Example

You need a loan to buy a new car. You know how much money you want to borrow, how long of a period of time you want to take to pay off the loan (the term), and what payment you can afford to make each month. But what you need to know is what interest rate you need to qualify for to make the payment $400 per month. In the image below, you can see that if you didn’t have interest and just divided this $20,000 into 60 monthly payments, you would pay $333.33 per month. The what-if analysis tool will allow you to easily calculate the interest rate.

Where did the formula come from?

The formula that appears in cell B5 in the example image is a function. It isn't part of the what-if analysis tool, so you will need to understand functions thoroughly before you use what-if analysis. For the example scenario described above, you need a formula that will calculate the monthly payment. Instead of writing the formula yourself, you can insert a function to do the calculation for you.

To insert a payment function:

• Select the Formula tab.
• Click the Insert Function command. A dialog box appears.
• Select PMT.
• Click OK. A dialog box appears.
• Insert your cursor in the first field. A description of the needed information appears at the bottom of the dialog box.

• Select the cell in the spreadsheet with the needed information.
• Insert your cursor in the next field. A description about the needed information appears at the bottom of the dialog box.
• Select the cell in the spreadsheet with the needed information.
• Repeat the last two steps until all of the necessary information is entered in the dialog box.
• Click OK.