As part of our occasional series on lesser-known Excel functions, this post highlights a key use of the FVSCHEDULE function in financial modelling.
The FVSCHEDULE function gives the future value of a quantity after applying a series of growth rates to it. The required inputs are an original value (or “Principal”), and a schedule of growth, return or interest rates. A key area of application is to work out the average compounded growth rate for a multi-period data set (i.e. where the growth rate in each period is different).
In the case that the periodic growth rates are based on the logarithmic method (i.e. are the logarithm of the ratio of the periodic values), then the average of such growth rates can be used directly. However, where the growth rate is serial growth (not logarithmic), one would need to add 1 to each growth rate and then multiply all the figures together before taking the appropriate root for the average annual compound figure (e.g. the square root for the annualised average over a two-year period. This process can be shortened by use of the FVSCHEDULE function: By setting the original (Principal) value to 1, the function returns the product of “1 plus the growth rate” in each period, so that subtracting 1 from this product gives the total growth. This increases the flexibility of the models and reduces the size of the calculation area, and hence is a useful tool (e.g. if a new period is added to the data set, the only change required is that the Excel range used in the formula be altered, rather than the formula itself needing amending).
Readers can experiment this for themselves in Excel (the function is easy to use, it is the lack of awareness of it that we are highlighting here). Specific example spreadsheets are also available in chapter 20 of my recent book Principles of Financial Modelling.