Excel 2007

Using What-If Analysis

/en/excel2007/using-templates/content/

The real power in Excel lies in its ability to perform multiple mathematical calculations for you. One of the tools in Excel that you can use to perform these calculations is a **Data tool** called **what-if analysis**. What-if analysis allows you to see the effect different values have in formulas. Have you ever thought, "What interest rate do I need to qualify for to have a car payment of $400 on the car I want?" This question can be answered using what-if analysis.

In this lesson, you will learn how to use a what-if analysis tool called **Goal Seek**.

Download the example to work along with the video.

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.

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.

- 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**.

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).

- 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.

Use the Car Loan workbook to complete this challenge.

- Use
**Goal Seek**to solve the problem described in the text and video lessons. - Create a problem of your own that you might use Goal Seek to solve. Try it.

/en/excel2007/working-with-charts/content/