Open Excel on your PC or Mac. It’s in the Microsoft Office group, which you'll find in the All Apps area of the Start menu in Windows, and the Applications folder on macOS.
Type the example data. In this example, imagine you want to borrow $100,000 from a bank. You know how much you can afford to pay every month ($900), and that you’d like to pay it off in 180 months. The information you’re missing is the interest rate. Enter these values into the following cells: A1: Loan amount A2: Terms (months) A3: Interest rate A4: Payment Now type these cell values for information you already know: B1: 100000 B2: 180 Enter the formula for which you have a goal. We’ll use the PMT function because it calculates the payment amount: B4: Type =PMT(B3/12,B2,B1) and press ↵ Enter or ⏎ Return. We'll be entering the payment amount ($900) in a different tool. Until then, Excel assumes the value is 0, so the result of the formula is $555.56. Ignore this.
Click the Data tab. It’s at the top of Excel. Now that you’ve entered the example data, you can run the Goal Seek tool.
Click What-if Anaylsis. It’s in the “Data Tools” section of the ribbon bar at the top of Excel. A menu will expand.
Click Goal Seek. The Goal Seek window will open.
Enter B4 into the “Set cell” field. You’re typing B4 because that’s where you typed the PMT formula you’re resolving.
Type -900 into the “To value” box. In the example, you want to pay $900 per month, which is why you’re entering it into this box. The value is negative because it’s a payment.
Type B3 into the “By changing cell” box. This is the empty cell, which is where the result of the Goal Seek tool will appear.
Click OK. Goal seek will run and display the insurance rate in B3 based on the payment amount in B4. In the example, Goal Seek determined that the interest rate is 7.02%. To display the insurance rate as a percentage (appropriate for this example), click the Home tab at the top of Excel, click cell B3, then press Ctrl+⇧ Shift+%.
Comments
0 comment