As Seen on TechCrunch: Finofo Raises $1.65M CAD!

Excel Guide

How to Use Goal Seek in Excel: A Step-by-Step Guide

Goal Seek is a valuable tool in Excel that allows you to find the input value needed to achieve a desired result. Here's a step-by-step guide on how to use Goal Seek.

Example Scenario:

Let's consider a simple scenario where you have a loan amount, an interest rate, and a target monthly payment. You want to find out what loan amount is needed to achieve that target monthly payment.

Step 1: Open Excel and Enter Data

  1. Open Microsoft Excel and create a new or open an existing workbook.
  2. Enter the relevant data in the cells:
  3. Cell A1: Loan Amount
  4. Cell A2: Interest Rate
  5. Cell A3: Target Monthly Payment
  6. Cell B1: (Enter a starting loan amount)
  7. Cell B2: (Enter an interest rate)
  8. Cell B3: (Enter the target monthly payment)

Step 2: Calculate Monthly Payment

Use the PMT function to calculate the monthly payment based on the loan amount, interest rate, and the number of periods.

=PMT(B2/12, 30*12, -B1)

This formula assumes monthly payments for 30 years (30*12) and a negative loan amount (-B1).

Step 3: Set Up Goal Seek

  1. Click on the cell containing the calculated monthly payment (let's say it's C1).
  2. Go to the Data tab in the Excel Ribbon.
  3. Click on What-If Analysis and select Goal Seek from the dropdown menu.
  4. In the Goal Seek dialog box:
  5. Set Cell: Select the cell containing the calculated monthly payment (C1).
  6. To Value: Enter the target monthly payment value.
  7. By Changing Cell: Select the cell containing the loan amount (B1).
  8. Click OK.

Step 4: View Result

Excel will now adjust the loan amount in cell B1 to achieve the target monthly payment you specified.

Tips and Considerations:

  • Sensitivity to Initial Values:
  • Goal Seek may give different results based on the initial values. Make sure to start with reasonable initial values.
  • Multiple Goal Seek:
  • Goal Seek can be used for various scenarios. If you have multiple target values, you can repeat the process.
  • Convergence Issues:
  • In some cases, Goal Seek may not converge or may provide unexpected results. This can happen with highly complex models.

Conclusion:

Goal Seek is a valuable tool when you need to find the input value necessary to achieve a specific goal or result. It's particularly useful for financial modeling, scenario analysis, and decision-making in Excel.