[ad_1]
Have you ever had a financial goal that you wanted but weren’t exactly sure how to get there? Using Goal Seek in Microsoft Excel, you can determine what you need to achieve your goal.
Maybe you want to save a certain amount of money over the next few years, but you’re not sure how much you need to set aside each month. Or maybe you want to get a loan and know how much you can pay each month, but don’t know what interest rate to look for.
Goal Seek can help you with these types of calculations and others.
What is goal seeking in Excel?
Goal Seek is part of the What-If Analysis group built into Excel. It works with values ​​that you insert and a formula that you enter to calculate those values. This means that a formula is needed to use Goal Seek.
The tool is especially useful for the situations mentioned above, such as savings, investments, and loans, but it can be used in other scenarios as well. Once you get the results from the Goal Seek tool, you can simply view or include them in your sheet.
Goal Seeking Examples
If you’re ready to give this nifty feature a try, let’s look at some usage examples.
Search for Sales Goals
For an easy way to get familiar with the tool, we’ll start with a basic example. We want to determine how many units of our product we need to sell to reach our goal. So, we have the current quantity, unit price and total sales as below.
The total sales in cell B3 is a formula that multiplies the quantity by the unit price: =B1*B2.
We want to know how many units we need to sell to reach our goal of $20,000 in total sales. For this, Goal Seek is ideal.
Go to the Data tab, click the What-if analysis dropdown arrow and select “Goal Seek”.
In the small Goal Seek box, insert the following:
set cell: Enter the cell reference that contains the formula you want to change. Remember, changing the formula shows us the input we need to reach our goal.
Appreciate: Enter the result you want. For us, this is 20,000.
When changing cells: Enter the cell reference you want to adjust. For us, this is B1 for Quantity.
When finished, click “OK”. The Goal Seek Status box shows that a solution was found, and you’ll see a preview in your spreadsheet.
As you can see, we now know that we need to sell 800 units of our product to reach our goal of $20,000. Click “OK” to apply the change to your sheet or “Cancel” to simply close the Goal Seek Status window.
Loan targeting
Another good way to use Goal Seek in Excel is to get help with loans. You may have the loan amount, the term in months, and the payment you can afford, but you want to know what interest rate to look for. Because the Goal Seek tool works with formulas, you can leave the interest rate cell blank and allow Goal Seek to fill it in.
RELATED: How to calculate the payment, interest or term of a loan in Excel
Here we have the Loan Amount, Term and Payment fields filled out. (You can ignore the payment amount shown for now, as the formula still needs the interest rate.) We have the formula for the PMT (Payment) function in cell B4: =PMT(B3/12,B2,B1).
Select Data > What-If Analysis > Goal Seek and insert the following:
set cell: Enter the cell reference that contains the formula you want to change. For us, this is B4.
Appreciate: Enter the result you want. For us, this is -800 because we can pay $800 per month.
When changing cells: Enter the cell reference you want to adjust. For us, this is B3 for the interest rate.
Note: Excel uses a negative number for the payment when you use the PAYMENT function.
Click “OK” to see the results. It looks like we’ll be trying to get a 4.77% APR on our loan.
Select “OK” to apply the change to your sheet or “Cancel” to close the box.
Search for goals for savings
For one more example, you may have a savings goal that you want to achieve in a certain period of time. We want to save $5,000 in 12 months, our bank offers 1.5% interest, and we need to know how much to deposit each month.
RELATED: Best Online Banking and Budgeting Tools to Replace Simple
Here we have the Interest Rate and Number of Payments filled in. Payment is blank because Goal Seek will enter it and Goal is $0 because the formula still needs the payment amount. We have the formula for the FV (Future Value) function in cell B4: =FV(B1/12,B2,B3).
Go back to Data > What-if Analysis > Goal Seek and insert the following:
set cell: Enter the cell reference that contains the formula which is B4 in our example.
Appreciate: Enter the result you want. For our goal, this is 5,000.
When changing cells: Enter the cell reference you want to adjust which is B3 for Payment.
Click “OK” to see your results. We see that we need to save a little over $413 per month over the next year to reach our goal of $5,000.
Note: Excel uses a negative number for the payment when you use the FV function.
Again, click “OK” to apply the change or “Cancel” to close the window.
When you need help figuring out how to reach your financial goal, open the Goal Seek tool in Excel. To learn more, take a look at how to use Excel’s built-in actions feature.
[ad_2]
