In models with a time axis, there are many instances where one wishes for the value of an input parameter to change gradually over time. For example:

For the assumed growth rate in Sales Revenue to be higher in the early years (after product launch), and then to slow down to reach a stable long-term rate.

For the inflation rate to gradually increase to a long-term average, after having currently being a lower level.

For the productivity of staff to gradually increase, but then to plateau. For example, before considering inflation (i.e. in real terms), the unit cost of production could be desired to decrease (to reflect increasing productivity).

To combined two such effects, such as: real terms unit costs to be decreasing over some time, whilst inflation is gradually increasing.

In this section, we provide examples of the implementation of the calculations of these types of “tapering” processes. The section covers both linear tapering and percentage-based (geometric or exponential) tapering.

General Comments on the Examples, and the Use of the IFS Function

The examples are based on the case that often arise in practice, namely that the value of the parameter to be used (such as the periodic growth rate):

Has a constant value prior to some first time point.

Has a different constant value after some later time point.

Tapers gradually from one value to the other in the time periods in between the two points.

Example (I): Linear Tapering

As a first example, the following image shows a completed set of calculations, in which the growth rate of Sales Revenues is 10% until year 3, 5% from year 7, and tapers gradually in between (which is emphasised using the shaded cells in row 7):

Thus, the overall process has four input parameters i.e. the growth figures 10%, 5% and the time points 3 and 7.

Noting that – for any single time period in the model – there are exactly three possible cases for the “time bracket” in which it is i.e:

Before period 3 (before the first time point).

After period 7 (after the second time point).

Between period 3 and period 7 (between the first and second time points).

In fact, since the formula used to calculate the growth between period 3 and period 7 is an “interpolation” formula, it makes no difference if “before period 3” is considered to include period 3, or whether period 3 is considered to be “between period 3 and period 7” (and similarly for period 7): The formula is designed to give the same value in either case. In other words, as shown in the following image, the growth rate is considered to be a continuous function of time:

To create such a formula, there are two main logical components:

For any time period, determine which of the three time brackets the point is in.

Apply the appropriate formula in each case. That is, in the first and third cases, one simply uses the first and final growth rates respectively. In the middle case, one uses the tapering formula for the growth rate.

This can therefore be implemented by using IFS function to check which of the time-bracket conditions is met, and then applying the relevant growth figure in each case.

For example, for cell J7 in the first image above, the following image shows the first two sets of arguments for the IFS function, which returns the growth rates for the two “edge” cases i.e where the time period under considearion is period 3 or before, and where it is from period 7 onwards:

Between these two times (i.e. for the tapering period from period 3 to period 7), the AND function can be used as the thir logical test for the function:

The final pont to add, under the argument “Value_if_true3” (which is not shown in the above image) is the formula to be used for the tapering. There are two main forms, which are discussed and presented in the following examples:

Linear (or absolute) tapering. This is where the change in the value of the item being tapered is constant in each period. The example used above for the growth rate is an example: The change in growth in each period is 1.25% (i.e. the difference between 10% and 5%, spread over the four periods from period 3 to period 7, or (10%-5%)/(7-3) in each period. A further example is shown below.

Proportional (geometric or exponential tapering). This is where the change in value of the item being tapered is a proportion of the starting amount at the beginning of each period. Examples are shown below.

Example (II): Linear Tapering

A second example of linear tapering, is shown in the following image. In this case, it is used to taper a cost item, which is initially $100 for three periods, and then tapers down to $50 by period, with the periodic change within each period of the tapering being $12.50 (as calculated and shown in cell G6).

Within the tapering period, the formula uses for the tapering simply reflects the effect of the cumulated number of periods since the start of the tapering:

Note that in linear tapering – as shown by the percentage figures underneath the following image – the percentage change in the item is not constant: For a declining profile, the constant level of absolute change ($12.50 of decline per period) becomes an increasing percentage of that period’s starting value:

Similarly, for an increasing profile (such as if the values for Cost were to increase from $50 to $100 from period 3 to period 7), the changes or growth rates would form a decreasing series, rather than an increasing one.

Example (III): Geometric (Exponential) Tapering

To create a situation where the change in each period of the tapering is proportional to the starting value (i.e. a constant rate of growth or change in the tapered value), a different formula is required.

Using the same costs example above, the required periodic change (r) can be calculated as the amount required so that – when cumulated over 4 years – the figure of $100 would change to $50. That is, the value of r must satisfy the equation 100*(1+r)^4 =50. That is r=(100/50)^(1/4)-1, or in terms of the cell references in the model, the formula in cell G6 is:

This is then applied within the IFS function, to reflect the effect of the cumulative effect of the appropriate period:

The result is then that the changes in each period of the tapering are of the same percentage amount, as shown by the figures underneath the following image:

This is known as proportional, geometric or exponential tapering.

Proportional tapering is intuitively and logically often more appealing than linear tapering. However, its main disadvantage is the values used must not be zero or of opposite signs.

Example (IV): Combined Effects of Two Geometric Tapers

It is possible to combine the effects of two tapers. For example:

The unit cost expressed in real terms (without inflation) may decrease in some future period to reflect the productivity increase from an expect technological breakthrough.

The inflation rate may be expected to taper upwards in the future.

Thus, the total nominal cost (i.e. the actual $ value in that period) would need to reflect these two competing trends (one which reduces real terms cost, whilst the other increases cost due to inflation). The following image shows an example, in which the inflation factor is dominant in the early and ending periods of the model, whilst the productivity improvements are dominant in the middle part:

This is based on the following assumptions and model calculations: