Three kinds of What-If Analysis tools come with Excel:
Scenarios, Goal Seek, and Data Tables. Scenarios and Data tables take sets of input values and determine possible results.
Goal Seek :
Goal Seek works differently from Scenarios and Data Tables in that it takes a result and determines possible input values that produce that result.
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.
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.
Try this example :
Follow @Sarvavasi.com