Chapter 4 Video: Geometric Gradient

In this Excel tutorial I'm going to show you how to work a geometric gradient or the little g. Little g represents a gradient increase that goes at a constant percentage. Remember, the capital G is the arithmetic and that goes at a constant dollar amount. Little g is constant percentage. So in this example, say your current salary is $62,000 and you got five grand tucked away in your savings account, you're expecting a 4 percent annual increase and your savings account makes 2 percent. What is the present worth of the money that's being deposited? That's what I'd like to know. So, let's get in here and take a look. So, at year 1, my salary is gone up by the 4 percent, which I can calculate simply as B2, which is my base salary plus my B4 times B2, which gives me that my total raise was $2480. Which is the amount of money I'm going to put in my savings account.

Now, to do the geometric increase, again this is another one in Excel where there's not one of those handy dandy great formulas, you actually physically have to code in the gradient. Just like you have to do with the capital G, you have to do with a little g. So in this cell, this would equal C8 times 1 plus my little g, which is 4 percent. But I want to do again that move or I can do a pull down and it copies my formulas down the column, so I’m going to lock that B4 down so it doesn't increment. So I'm going to highlight it, hit the F4 key until I get a dollar sign in both in front of the column and row, which means it locks down exactly at cell B4 and close my parentheses.

You can see my salary went up. Now to see how my salary went up for the next few years all I have to do is grab the corner and drag it down and you can see how your salary increased. So here notice again that I lock down the B2 which is my base salary, so I can look at my total raise, so I grab the corner here and drag it down and you can see in year 2 I'm going to tuck away that $5000 difference, all the way up to year 4 where I’m tucking away over $10,000. Now, I'd like to know what's the present worth of this series of deposits. Remember, I started out at year zero with $5000 in my savings account, that money is already sitting there, so when I'm calculating the present value everything else is being brought back to it and then it's added in and it doesn't have an interest factor on it because it's already sitting in the place that I want to be.

So I simply put equals, net present value, oops! Net present value, I said oops because I was thinking I want to add in my current salary, but I can do that at the end. My rate from my savings account was in B5 and then I'm simply going to highlight the B8 through B11 series, close the parentheses and then I want plus what's currently sitting in my savings account, which is B3. Simply hit return and you could see the present worth would be $29,318.40, if you were to put away each raise that you've got. In this Excel tutorial I showed you how to handle a geometric gradient which is the little g, where you have a constant percentage increase.