In the last post we looked at graduated annuities, where the cash flow changes at a given rate. Imagine you need the present value of an annuity with a cash flow that changes unevenly and that change stays the same for certain periods. Take for example the cash flow below:
Here we have a 10-year annuity that pays $1,000 each month for the first year, $1,100 each month for the second year, etc. I could use PV and FV of Periodic Cash Flows and type in each change in the cash flows, along with the number of periods, but that would take a while and increase the chance of errors. Instead, we could use this spreadsheet called Uneven:
The first cell asks for the interest rate (4.000%). Next, we enter the periods per year which is monthly for this example. The number of periods per year options are:
Then we fill in the number of payments and the corresponding amounts. The check box also allows for payments that start at the beginning of each period, rather than at the end (default). The day count is assumed to be 30/360.
The column titled “PV (For Period)” calculates the present value (using the Excel PV function) for each amount and number of payments, regardless of the point in time they occurred. The column titled “PV (From Start)” takes the present value of the corresponding amount in “PV (For Period)”, as if it were a single payment, from that time back to the present. using the Excel PV function.
Then we sum the values in column “PV (From Start) and we get the present value. For calculating future value we use the Excel FV function, entering the present value.
All spreadsheets created with Excel 2013