Back into an Answer with Goal Seek

Learn Excel from MrExcel podcast, episode 2033 - Using Goal Seek to Back into an Answer!

I am podcasting this entire book, click that “i” on the top-right hand corner to get to the playlist!

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Well, we're revisiting the model that we build in episode 2022, this is one where we calculated a loan payment, and we used the Insert Function button to figure out the PMT function. And my problem is, this was a car, and I only wanted to pay $425 a month, so what principal value will lead us to $425 a month? Well, there's three ways to solve this, alright. Method #1 is algebra! No, no, I'm sorry, with all due respect to all high school math teachers out there, algebra is something that I intend to never use again after high school. Now, this one's not in the book, but it actually is a way to solve it. Let's change the model around and try and find the inverse function, so instead of using PMT, find a function that will give us the principal. And if we go back into Insert Function, and actually search for what we're looking for, PMT, the second item is the inverse, right? And it's possible to use the PV function, so rate/12, Nper of our periods is 60, and the payment I want to make that be negative, because it's money coming out of the bank, and I get the actual answer right there. Although, I'm closing my eyes, that way I forget this, because no one goes to the all the trouble of using algebra or inverse function.

Here's what we do, we play a game that I was very familiar with back in middle school, summer is in middle school! I'd be home during the day, I watch a program on American television called “The Price is Right”, where a guy named Bob Barker would give away all kinds of fabulous prizes. And do you remember the game where he would give away a car if you could just say the price of the car? You would guess, and he would shout HIGHER or LOWER! Alright, so my first guess was 25995, Bob says Lower, so I go 20000, Bob says Higher, now see, I am a fan of this game, I am an aficionado of this game. Of course, what you do then, just go right in the middle, so kind of go to 23000 and Bob's going to say Lower, and then you split it again 21.5, and so on. There you are, Winner, but this is a slow, slow way to go, but you understand what's happening here. Right, if you ever watch “The Price is Right” or if you just- we're guessing progressively higher and lower until we get to the right amount. And with thanks to Chad Thomas for his illustration of Bob Barker here, you know, Bob Barker has retired, he's been replaced in the game By Drew Carey, but it turns out that Bob Barker still has a job, the Excel team has hired him, he's back here on the Data tab, What-If analysis, and he lives inside the Goal Seek command!

Alright, so we're going to set cell B5 to $425 by changing cell B1, and when I click OK, that whole routine that happened on the last spreadsheet is going to happen in less than a second. Did you see that, 22384.9, and they're actually a lot more precise than I was, I got to 22385, it's actually 22384.93425. Which I guess, by the way, is the exact same answer that we got back here, but much faster, and without ever having to try and figure out the inverse function and change the model around. It is a great way to back into an answer to find the right input cell, and the model still works, if we would want to come in here and change things go to 72 months, you'll see that it will calculate. Now in this case, we were trying to figure out the principal, the right price of the car, to get us to $425, but you can do all kinds of crazy things.

We can go in here to What-If Analysis, Goal Seek, and say “Alright, I want to pay 425 dollars a month for this car, but I want to do it by changing, let's say, the term.” Click OK, and they find that you need a 71.3379 month loan to get to that answer so, you know, be creative, as far as which input cell you are going to change. Goal Seek is just one of the tips in this book, lots of tips, I'll be broadcasting the whole book, it will take at least the rest of October to get through this. But you can order the entire book now, $25 in print, $10 an e-book, click that “i” on the top-right hand corner. Alright, so how to back into an answer in Excel? First choice: algebra, I reject that! Second choice: find the inverse function of our payment using the Fx button right there, search for payment, and the inverse function might show up, some functions don't have an inverse. Or “The Price is Right” method, higher lower higher lower, but Goal seek is an automated way to do “The Price is Right” method, even works with changing the term.

Alright well hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!