At its core, retirement planning is about connecting present to future cashflows, where those cashflows might reflect your minimal annual needs, your more aspirational annual wants, or your anticipated annual funds from one or more sources. Standard practice in finance is to discount future cashflows back to the present by applying a discount rate. Spreadsheets are helpful in such calculations, and most have built-in functions to compute the present value of a given future cashflow stream. If that stream is constant over time, the function does not require a column that enumerates each annual cashflow. However, realistic problems anticipate changes in the cashflows over time. If there is no systematic pattern to those changes, then there is no alternative to enumerating them in a column. However, since we don’t know the future with much level of specificity, we usually assume growth at a constant (positive or negative) growth rate, and also assume a constant discount rate over the future time horizon. For such cases, we offer a formula that can be implemented in a single cell without reference to enumeration of associated annual cashflows.
Let’s work through an example. You anticipate retiring in 20 years, believe your minimal annual needs will be $50,000 in today’s dollars, estimate that annual inflation will be 3% and the appropriate discount rate is 4.5%. The table below is a spreadsheet snapshot that enumerates the annual cashflows over each of the next 50 years (column 1), first by excluding growth (column 2) and then by including it (column 3), and then calculates the present value of each future growing cashflow (column 4). As shown, the sum of zero-growth cashflows equals $2.5 million ($50,000 annually times 50 years), the sum of actual growing cashflows over the fifty years equals about $5.8 million, reduced to less than $1.8 million in present value. The box in the lower right demonstrates the shortcut formula exactly replicates the present value, based solely on its five input parameters.
Of course, as the problem is defined, cashflows in the first two decades and the last decade are not of direct interest. The display below plots over time the growing cashflows, constant cashflows, and discounted cashflows.
The problem requires that we extract that slice along the timeline that captures the information for the third and fourth decade into the future. The first two decades are relevant only because cashflows are assumed to be growing throughout that time, even though they are not accessible, and the relevant cashflows must be discounted over that period as well. A formula to compute the present value of only the relevant cashflows is as follows:
It may seem more intuitive to break the formula into three components:
- The present value of the first cashflow, to be received Year 21
- The present value of the cashflows over all future years, to infinity
- Carving out the interval of present values from Year 21 through Year 40.
As confirmation, here is a direct calculation of the infinite growing series:
Notice also that the formula is very flexible. First, it can be inserted as written in a spreadsheet cell, presuming you first name each input parameter cell. For example, in Google Sheets, click Data → Named ranges, and then fill in the pop-up window on the right. Then experiment by changing parameter inputs to mimic finance problems with well-know standard formulae. For example:
A constant cashflow infinite stream can be approximated by setting “finalYear” to “1e9”, or any sufficiently large finit number (like 1,000):
The present value of cashflows for any single future year can be extracted simply by adjusting “priorYear” and “finalYear” to fit the year of interest.
Below is a visual depiction of the single-year slicing, and above is the valuation of the original problem, confirming the accuracy of the formula.
A Standalone APP
We have developed a standalone app to estimate the present value of cashflows over any future time interval, assuming constant growth and discount rates. It is more convenient and does not require a spreadsheet.
— Jerry Platt, Ph.D., Emeritus Professor of Finance, San Francisco State U.