GRADUATED ANNUITIES IN EXCEL

Graduated Annuities Using Excel

Strictly speaking, an annuity is a series of equal cash flows, equally spaced in time. However, a graduated annuity is one in which the cash flows are not all the same, instead they are growing at a constant rate. So, the two types of cash flows differ only in the growth rate of the cash flows. Annuity cash flows grow at 0% (i.e., they are constant), while graduated annuity cash flows grow at some nonzero rate. Graduated annuities are found in many places including pensions that have built-in cost of living adjustments, lotteries such as PowerBall, and others. Any finite series of cash flows that are growing at a constant rate is a graduated (or, growing) annuity.

We have already seen how to calculate the present value and future value of annuities. Excel makes that easy because it has built-in functions that automatically handle annuities. However, there are no functions that can calculate the present value or future value of a growing stream of cash flows. Fortunately, we can make the PV function do the work for us by altering the interest rate that we use. Before beginning, it may be useful to download the example spreadsheet that I will use for this tutorial.

The Interest Rate Adjustment

The first thing to understand is that there are two opposing rates when dealing with graduated annuities: The growth rate and the discount rate. The growth rate makes the cash flows larger, but the discount rate makes them smaller. Therefore, the "net" interest rate that we will use must be a combination of these two rates.

Because the two rates work in opposition to each other, we can approximate the correct rate to use by simply using the difference between the discount rate and the growth rate. Intuitively, this should make sense. However, because the rates compound over time, the adjustment is a bit more complex. Specifically, the net rate can be calculated using the following formula:

where i is the discount rate and g is the growth rate. Remember that compounding is a multiplicative process, not additive. Because of this we need to divide, not subtract, to net out the rates. When we adjust the rate using this formula, we can use the resulting rate in the PV function.

PV of a Graduated Annuity Due

A graduated annuity due is one where the first cash flow occurs today, that is at the beginning of a period. These are slightly easier to deal with than a regular graduated annuity, so we will deal with them first. Let's look at an example:

You are considering the purchase of an investment that will pay $1,000 immediately, and then 4 additional payments that grow at a rate of 3% per year to account for expected inflation. If your required return is 8% per year, what is the value of this investment?

Set up a worksheet that resembles the one pictured below:

The spreadsheet shows the information given in the problem, and also a time line of the cash flows in column E. Note how the cash flows begin in period 0 (today) because we are dealing with a graduated annuity due. The time line isn't needed, it is there to clarify the timing of the cash flows and so that we can verify our answer.

To find the present value, we usually use the PV function, but we can't use it in the normal way because of the growing payments. However, using the above formula, we can calculate the net rate that we need:

Net Rate = (1 + 0.08)/(1 + 0.03) - 1 = 0.04854

Using the spreadsheet that you created above, in B9 enter the formula: =PV((1+B4)/(1+B3)-1,B5,-B2,0,1). Note that we have to both adjust the Rate argument and tell the function that the cash flows occur at the beginning of the period (type argument = 1). You should find that the answer is $4,557.98. Note that it also shows the use of the NPV function to confirm this result. The NPV function, of course, requires an entire table of cash flows so it isn't as practical as knowing how to adjust the interest rate and use the PV function. The spreadsheet below shows the final result:

PV of a Graduated Regular Annuity

We now change the cash flows to a graduated regular annuity (cash flows at the end of the period). It should be obvious the the present value will be somewhat less than above because the cash flows are received one period later. The spreadsheet below is similar to the previous one, but notice that the cash flows have been shifted one period forward.

In this case, we make exactly the same interest rate adjustment. The PV function is almost the same as before, but we set the Type argument to 0 because the cash flows occur at the end of the period (you could also omit the Type argument). Next, we need to modify the output of the PV function by dividing by (1 + g), so the formula in B10 is: =PV((1+B4)/(1+B3)-1,B5,-B2,0,0)/(1+B3). The answer is $4,220.35, and is again confirmed by using the NPV function on the time line of the cash flows.

There is one modification that we can make. I only mention this for completeness, not to try to confuse you. Realize that if we treated this, to start, as a graduated annuity due then the PV would be at period 1 instead of period 0. In that case, we need to discount the result by one period, so we divide by (1 + i). The alternative version of the PV function is: =PV((1+B4)/(1+B3)-1,B5,-B2,0,1)/(1+B4). Note that the answer is an identical $4,220.35.

Future Value of Graduated Annuities

Once one understands how to calculate the present value of a graduated annuity, then finding its future value is very easy. Simply find the present value and then calculate the future value of that number. The only thing to remember is that the future value of an annuity due is defined to be one per after the last cash flow. In this problem the future value will be in period 5, regardless of whether it is an annuity due or a regular annuity. The same applies to normal (all cash flows equal) annuities. The picture below shows the results for both types:

Note that I've shown the time lines for both types of graduated annuities. Again, this is only for verification of the results and you normally wouldn't want to list all of the cash flows.

As noted, for the graduated annuity due we use the same PV function, but we use it as an input to the FV function. Essentially, the formula is: =FV(8%,5,0,PVGA). Using actual cell references, the formula is: =FV(B4,B5,0,PV((1+B4)/(1+B3)-1,B5,B2,0,1)). It looks complicated, but it really isn't if you have followed along. The future value of our graduated annuity due is $6,697.17 at the end of period 5.

Using the exact same logic, we can find the future value of a graduated regular annuity. Simply use its PV as an input to the FV function: =FV(B4,B5,0,PV((1+B4)/(1+B3)-1,B5,B2)/(1+B3)). The answer is $6,201.08, again as of the end of period 5.

Summary

In this tutorial we have essentially learned one thing: To calculate the present value or future value of a graduated annuity, we simply have to use a "net" interest rate. This rate, sometimes called the "resultant rate," is basically the difference between the discount rate and the growth rate of the cash flows. That isn't quite the whole story, though, because we actually have to adjust for the fact that the rates compound over time. Therefore, instead of using the difference in the rates, we use the result of dividing them. Once the rate is adjusted, we can use it in Excel's PV function just as we would for any normal annuity.