How to Utilize Goal Seek in Microsoft Excel
If you have a financial goal but aren’t sure how to achieve it, Goal Seek in Microsoft Excel can help. This tool works by adjusting an input value to achieve a desired result in a formula.
🔹 Common uses include:
- Determining sales targets
- Finding an affordable interest rate for a loan
- Calculating monthly savings needed to reach a goal
What Is Goal Seek in Excel?
Goal Seek is part of Excel’s What-If Analysis tools. It works by:
- Taking a formula that depends on one variable.
- Adjusting that variable automatically to reach a specified target value.
📌 Formula Required: You must have a formula in place for Goal Seek to work.
How to Use Goal Seek in Excel
1️⃣ Example: Determine Sales Targets
You want to find out how many units you need to sell to reach $20,000 in total sales.
Step 1: Set Up Your Data
| A | B |
|---|---|
| Quantity | 200 |
| Unit Price | $25 |
| Total Sales | =B1*B2 |
Step 2: Open Goal Seek
- Go to Data > What-If Analysis > Goal Seek.
- In the Goal Seek window:
- Set Cell → Select the cell with the formula (B3).
- To Value → Enter 20000 (your sales goal).
- By Changing Cell → Select the input cell to adjust (B1 for Quantity).
- Click OK to see the result.
📊 Result: You need to sell 800 units to reach your goal.
2️⃣ Example: Find the Interest Rate for a Loan
You want to take a loan but need to know what interest rate to look for.
Step 1: Set Up Your Data
| A | B |
|---|---|
| Loan Amount | $50,000 |
| Term (months) | 60 |
| Interest Rate | (Blank, Goal Seek will fill this) |
| Monthly Payment | =PMT(B3/12,B2,B1) |
Step 2: Use Goal Seek
- Go to Data > What-If Analysis > Goal Seek.
- In the Goal Seek window:
- Set Cell → Select the cell with the payment formula (B4).
- To Value → Enter -800 (negative because Excel treats payments as expenses).
- By Changing Cell → Select the Interest Rate cell (B3).
- Click OK.
📊 Result: You need an interest rate of 4.77% to afford $800 monthly payments.
3️⃣ Example: Determine Monthly Savings to Reach a GoalYou want to save $5,000 in 12 months with an interest rate of 1.5%.
Step 1: Set Up Your Data
| A | B |
|---|---|
| Interest Rate | 1.5% |
| Term (months) | 12 |
| Monthly Deposit | (Blank, Goal Seek will fill this) |
| Future Value | =FV(B1/12,B2,B3) |
Step 2: Use Goal Seek
- Go to Data > What-If Analysis > Goal Seek.
- In the Goal Seek window:
- Set Cell → Select the Future Value formula cell (B4).
- To Value → Enter 5000 (your savings goal).
- By Changing Cell → Select the Monthly Deposit cell (B3).
- Click OK.
📊 Result: You need to save $413 per month to reach your goal.