Graduated Annuities

A graduated annuity is like a regular annuity, except instead of getting the same cash flow each period, the cash flow grows at a given rate throughout the term. There a two basic types of graduated annuities, ordinary annuities (cash flow comes at the end of each period), and annuities due (cash flow starts at the beginning of each period).

My first thought was to simply build a cash flow model that would compound the cash flows by the growth rate, and then take the present and future value of the cash flows. We will start with a simple example. The input cells are yellow. The first input cell is a drop down box asking for how many times the cash flow will occur each year. For this example we enter “Annual” or once a year. Next we enter the number of years, and then the starting cash flow amount ($1,000). We are going to assume that the cash flows will grow 3.0% each year. The next input data is the rate (APR) we will use to discount the cash flows to get the present and future values (in green cells).


The model takes the information and creates a cash flow stream like below that when discounted at 8.0% has a present value of $7,550.13 and a future value of $16,330.17.

Grad2Notice at the top of the cash flow it says (Ordinary Annuity (End).  That was because, looking back at the input cells, we entered with the drop down box TRUE to the question “Ordinary Annuity?”. If we entered a FALSE to this question, the cash flow would look like this:


All the cash flows have moved up one cell and the last cash flow is a zero. This is an Annuity Due. The new present and future values have also changed:


Back to the input cells. The first input cell above is “Annual”, but the drop down box offers three other periods per year. They are Semiannually, Quarterly, and Monthly.

Take for example a graduated annuity that increased each month, by 1/12 th of the growth rate. It would look like this (g/p), where g is the growth rate and p the number of periods per year. On the other hand, if one wanted the stated rate to equal the rate whether paid annually or monthly, it would look like this, (1+g)^(1/p)-1. I gave the user a choice:


I could build a case for either. The g/p gives a slightly higher return, which would be equal to the annual (APY) yield. This is the same concept used in mortgages. A 3.0% monthly rate on your mortgage is equal to a 3.0416% annual yield. The (1+g)^(1/p)-1 calculation is similar to the CPR calculation I refer to in a number of my past posts. A 10 year annuity paying monthly would have inputs like below:


Obviously, I won’t show all 120 cash flows, but the first few would look like this:


If you are only doing a few graduated annuities, the cash flow model would be my choice.  If you have hundreds of calculations, you might want a formula instead. I have broken down the formulas into two categories. The first one assumes a g/p compounding assumption and the second a  (1+g)^(1/p)-1 assumption. I am not a mathematician, so I express the formulas using  Excel functions.  I hope they are helpful:


  Download Excel file “Graduated

All spreadsheets created in Excel 2013


One thought on “Graduated Annuities

  1. Pingback: Breaking Down the Mortgage Mega-Formula | Excel@CFO

Leave a Reply

Your email address will not be published. Required fields are marked *

WordPress spam blocked by CleanTalk.