Getting Cozy at Home...with Excel: Goal Seek
This Week’s Featured Tool: Goal Seek
In the first installment of Getting Cozy at Home...with Excel I discussed Excel's Templates feature. This week's topic is Goal Seek. Are you in the market to buy your dream house but weary of its colossal mortgage? Looking to settle a loan but skeptical you can manage the monthly payment? Goal Seek helps you make informed decisions about your finances. Using Goal Seek, you can customize a loan’s parameters to match a given budget.
The PMT Function
To utilize Goal Seek, you must be well-versed in Excel’s PMT (Payment) function. The PMT function allows you to calculate the payment on a loan at a given principal, fixed interest rate, and payment period. The function’s syntax is:
Three arguments are required: the interest rate per pay period for the loan (Rate), the number of payments (Nper), and the present value (Pv), as shown below. (Arguments Fv and Type are optional and not applicable in this particular example.)
TIP #1: Always use cell references for your PMT arguments to make the function dynamic (and to allow Goal Seek capabilities!).
TIP #2: If the interest rate is annual and the payment is monthly (as shown above), to obtain the monthly interest rate, divide the annual interest rate by 12.
TIP #3: A negative Pv value will give you a positive PMT (payment) value.
Remember: before you use Goal Seek, you know the objective, but you don’t know how to reach it. Goal Seek enables you to find the input value(s) needed to achieve a goal or result. Say goodbye to manual “guess-and-check” methodologies.
For example, let’s say you are in the market to buy a property with a mortgage of $400,000 and your first potential lender quotes you infeasible terms (see above screenshot). Given a 7% annual interest rate and a term length of 360 months (30 years), the monthly payment (PMT) would be about $2661. However, suppose you can only afford a maximum PMT of $2200. How can you achieve this goal?
To achieve a PMT of $2200, you must do at least one of the following:
- Borrow less $ (Bye bye dream house!)
- Negotiate/shop around for a better interest rate
- Extend the term of your loan
Let’s say you are hell-bent on your dream house and refuse to settle for a property with a lower mortgage. This leaves you with the latter two options for lowering your PMT. But how low must you negotiate your interest rate to achieve a PMT of $2200? To what extent must you extend the term of your loan so that you only pay $2200 per month?
To answer these questions:
- Launch Goal Seek. (Data»Data Tools»What-If Analysis»Goal Seek)
- Reference the cell containing your PMT function in the “Set cell:” field.
- Indicate your goal PMT in the “To value:” field.
- For the “By changing cell:” field, reference the cell that must change to obtain this goal (which is the interest rate in this example.)
Once you press “OK,” Excel will find your answer. In this example, an interest rate of approximately 5.2%, while holding the mortgage amount and term length constant, will achieve a $2200 PMT. Thankfully, a 5.2% interest rate may just be in the realm of possibility!
To experiment with different term lengths and mortgage amounts, the process is the same. The only difference would be the cells referenced in the “By changing cell:” field.
As you can see, if a new house is on your horizon, Excel’s Goal Seek might just become your new best friend.
Up Next: Stay tuned for the next chapter in the series Getting Cozy at Home...with Excel: Data Table.
A former college track star (boasting a sub 5 minute mile) and high school science teacher, Elizabeth Robie is passionate about education and technology. In her spare time, she enjoys running, playing with her dog Seamus, and watching Duke basketball religiously. At Learn iT! Elizabeth teaches the entire MS Office Suite.