Jennifer, Here Are Your Articles for Wednesday, January 23, 2019
Is this email not displaying correctly?
View it in your browser
Friend Me on Facebook Follow Me on Twitter Connect with me on LinkedIn
Recur360 Custom App Development QuickBooks and IT Consulting Client Testimonials Office Locations Get to know our Staff
Share Save

What's Up with Excel's Goal Seek Feature

 

Occasionally you may encounter formulas in Excel where getting to an answer entails some trial and error. For instance, let's say that you're planning to buy a new car. You know the interest rate that will be charged, and you know the term of the loan that you're seeking. You also know that you'd like your payment to be $250 per month. The one piece of information that you're missing here is how much you can borrow to have a payment of $250/month for 48 months at 3%. If you didn't know otherwise, you'd find yourself entering various guesses into your spreadsheet to eventually get the formula to return a payment of $250. However, instead you can use the Goal Seek feature to eliminate the guesswork.

Let's first build a worksheet that we can use for Goal Seek. Enter the words Interest, Term, Principal, and Payment into cells A1:A4 of a blank worksheet. Enter 3% in cell B1 and 48 in cell B2. We'll leave cell B3 blank for the moment. In cell B4 enter this formula: =-PMT(B1/12,B2,B3).

The PMT worksheet function in Excel enables us to solve for a loan payment amount. The function has three required arguments: rate, nper, and pv. Two other optional arguments are available for those with more complex payment calculation needs. In this case the rate argument corresponds to our interest rate. Excel requires that interest rates be on the same basis as the term, which we're expressing monthly, so we divided the interest rate by 12 in our formula. The nper argument reflects the term of the loan, in this case cell B2. The pv argument is short for present value, which is another way of referencing the amount of money being lent.

Although you could try manually entering different values into cell B3 for pv until eventually the formula in cell B4 returns $250, let's instead use the Goal Seek feature. Activate Excel's Data menu, click on the What-If Analysis command, and then choose Goal Seek. A dialog box will appear from which you'll provide the following:

  • Set cell: This is the address of the cell that contains a formula that you're trying to solve for, in this case cell B4.
  • To Value: This is the amount that you would like the formula specified as Set Cell to return, in this case $250.
  • By Changing: This is the address of the cell that contains the input that you want Excel to solve for, in this case cell B3.

When you click OK in the Goal Seek dialog box, Excel will start at zero and rapidly enter different numbers into the By Changing cell until eventually the Set Cell is equal to the To Value. In the case of particularly complex spreadsheets Excel may report that it cannot find an answer, but generally Goal Seek is a way to quickly ascertain an answer when you're missing a single input required by a formula. In this case you'll find that you can borrow $11,294.67 at 3% for 48 months to have a $250/month loan payment. If this amount appears as a negative value then you omitted the minus sign before the PMT function, which always returns a negative value. By adding a minus sign in front of PMT it returns a positive number for the payment, which means the result we're solving for will be positive as well.

A constraint of Goal Seek is that it can only solve for a single missing input. If you have situations where you need to perform an optimization, break-even analysis, or solve for two or more missing values, Excel offers the Solver feature as a next step. Solver is far more complex than Goal Seek but is yet another means for extending Excel's functionality.

 
Share Save

Your Comments

Saved Articles
Comments and Feedback
Refer A Friend
Your Privacy
Our firm provides the information in this e-newsletter for general guidance only, and does not constitute the provision of legal advice, tax advice, accounting services, investment advice, or professional consulting of any kind. The information provided herein should not be used as a substitute for consultation with professional tax, accounting, legal, or other competent advisers. Before making any decision or taking any action, you should consult a professional adviser who has been provided with all pertinent facts relevant to your particular situation. Tax articles in this e-newsletter are not intended to be used, and cannot be used by any taxpayer, for the purpose of avoiding accuracy-related penalties that may be imposed on the taxpayer. The information is provided "as is," with no assurance or guarantee of completeness, accuracy, or timeliness of the information, and without warranty of any kind, express or implied, including but not limited to warranties of performance, merchantability, and fitness for a particular purpose.
Powered by
Copyright © IndustryNewsletters All rights reserved.

This email was sent to: jennifer@otbtechnology.com

Mailing address: 2002 Willow Beach, Keego Harbor, MI 48320