Chapter 8 Video: Alt and Goal Seek

In this Excel tutorial I'm going to be talking to you about how to use Goal Seek when the alternatives that you're looking at have differing lives. It's really easy to look at Alternatives using incremental analysis such as IRR or rate when you're working in Excel, when the lives of the alternatives are the same, but you run into a problem when the Alternatives you're evaluating have different lives, like this one, notice it has a life of 5 for the short one and the long one has a life of 9. Whenever you have lives that are..are of different lengths you compare them by assuming that the Alternatives can be repeated until the least common multiple of their lives. Well, in this case the least common multiple is going to be 9 times 5, which if my math is correct is 45. That takes a lot to do. So an easy way to do this is to set it up in Excel and use something called Goal Seek.

So in this tutorial I have setup information. The interest rate that we're going to try out at first is just 10%, give it a whirl with something. My Alternatives are short and long. The initial cost of my short one is 75,000 and my long project is a 125,000. The life is 5 for the short and 9 for long. Here in E5 notice that I've calculated the equivalent uniform annual cost and you can see in my formula that equals payment. I've locked the cell B2. So it'll lock on the interest rate of 10%. Ah.. D..So it goes rate, my number of years is in D5, notice the green matches the green, and my initial cost is negative C5, purple and purple. And you can see that I get my EUAC. Now the reason I locked this is I could grab the corner here and just pull this down and it would automatically calculate the next one for me. So it's just an easy way to make sure everything works. Notice that changed to D6 for my life and negative C6 for my initial cost.

Now the difference between these two is $1,920 and what you're trying to do when you're comparing these two, or doing an incremental analysis, you want that difference to be zero. So an easy way to do this is to go to Goal Seek. So under data, go to what-if analysis and notice that you can select Goal Seek. Okay. Now Goal Seek pops up and I'm going to bring it up into the screen so you can see it. So what I want to do is to set cell E7. Notice it says E7. I want the difference to be 0 by changing my interest rate that's in B2. And that's all you have to do and you hit OK. And notice that when you're finished that 4.12 percent is the interest rate that makes you indifferent between these two projects. So whenever you're doing an incre... comparing Alternatives and you have different lives rather, the easiest way to do an increnal..incremental analysis is to use Excel like I've shown here and use Goal Seek.