Chapter 4 Video: Arithmetic Gradient

In this Excel tutorial I'm going to talk about arithmetic gradient or the capital G. The capital G is when you have a series of cash flow that increases by consistent dollar amount. Now when we’ve been working with Excel, so far we've had really great formulas that we can go find to do these different calculations like, present value, future worth, things like that. But when you have a gradient there is no easy formula back there. So you're actually going to have to physically put the gradient in and then do your work. So for example, we have a $500 is our gradient base and our gradient is increasing by $150. So, at year zero we start out with a negative $3000, but year 1 is where our gradient starts, so I'm going to make this equal to my gradient, okay. Now, for the next year what I have to do here is I have to code in the gradient, so this would be equals B7 plus the gradient dollar amount, which is B3. But I'm going to do that cool corner move pull down thing that I showed you in Excel basics, so I want to hard code that B3 so that when I do the incrementing, it always stays with B3. So, to do that I hit the F4 key and I get the dollar sign in front of both the column and the row which means it will lock it there, and simply hit return. Now I can do my corner move, grab the corner here pull down and notice it did the incrementing for me.

Now that I have that gradient in there, I can calculate my present worth. Remember, the negative $3000 is already at the present, so interest is not accrued on it. So ,you're only going to deal with B2 through B5, bringing or…excuse me B7 through B11, bringing it back to year 0 and then adding on B6. So, to do this you would simply type equal B6 plus the net present value of the remaining cash flow. The rate is in B13 comma and then I just highlight the remaining cash flows which is B7 through B11. Notice how it puts a colon in there that means it took the range of B7 through B11. Simply close the parentheses and you get your present worth. You can also, when you have a gradient like this, you can also calculate internal rate of return in a very simple way. Simply do equals IRR and you just highlight the entire cash flow. In this instance you do take year 0, close the parentheses and you get an internal rate of return of 9.13 percent that you can use to compare to your MAR or make financial decisions. In this Excel tutorial I've shown you how to do an arithmetic gradient when Excel doesn't have a formula to tackle it for you.