Posted: Thursday 14 February 2013
Excel has a nifty feature called Goal Seeker. If you have a particular dollar amount you want to arrive at by changing one variable (for instance, how much money can I borrow at 7% interest for 60 months in order to have a $350 monthly car payment), Goal Seeker can help you find the answer.
Using the car example above, we'll create a payment formula (Don't know how? Check out my prior blog on it: http://us.deskdemon.com/dnet/blog.php?user=msmarieh&blogentry_id=766). With your cells already filled in for the PMT formula, choose Data Ribbon - What If Analysis - Goal Seek (Tools-Goal Seek in 2003). Set cell D1 (Goal Seek always works on a cell with a formula in it) to value -$350 by changing cell C1 (our total amount borrowed). Goal Seek will then change the cell to the correct amount (in this case borrowing $17675.70 at 7% interest would give you a $350 monthly payment).
Excel has an additional feature called Scenarios which allow you to change more than one element of the formula. Choose Data - What If Analysis - Scenario Manager (Tools-Scenarios in 2003) - Add (to create a new one). Give your scenario a name. I suggest something descriptive, like interest rate changed to 6.5%. Select which cell will be the changing cell (for example A1 - the interest rate), put in the revised amount (.05416666 for 1/12 of 6.5% for instance). Note that you can select more than one cell by holding down your control button or clicking and dragging. Excel will ask you what each cell's value should be. It will then come up with an answer that matches your differences. Scenarios are essentially "what if's". What if I got a lower interest rate and took a loan for a longer period of time? What if I borrowed more, but paid it back faster? What if our company increased it's revenues by 10% and lowered expenses by 5% across the board next year?
Lots to play with for these Excel features. Take a moment to poke around with them!