Excel Index Functions
This tutorial demonstrates how to create an index from schedules of changing values. An excel index functions are commonly used to compare how data changes over time. An index allows easy cross-comparison between different periods and between different data sets.
For example, consumer price changes are recorded in an index in which the initial “shopping basket” is set to an index of 100. All subsequent changes are made relative to that base. Therefore, any two points show the cumulative effect of increases.
Perhaps the best approach is to use a two-step illustration:
1. Convert the second and subsequent data in the series to percentage increases from the previous item.
2. Set up a column in which the first entry is 100, and successive entries increase by the percentage increases previously determined.
Although a two-step approach is not required, a major advantage is that the calculation of the percentage changes is often useful data in its own right.
The example, shown in the image below, involves rentals per square foot of different types of space between 2010 and 2016. The raw data is contained in the first table. This data is converted to percentage changes in the second table and this information is used to create the indices in the third table.
The formulas for calculating the growth rates (in the second table) are simple. For example, the formula in cell C14 follows:
This formula returns 3.13%, which represents the change in retail space (from $89.4 to $92.3). This formula is copied to the other cells in the table (range C14:H18). This information is useful, but it is difficult to track overall performance between periods of more than a year. That’s why indices are required.
Calculating the indices in the third table is also straightforward. The 2010 index is set at 100 (column B) and is the base for the indices. The formula in cell C23 follows:
This formula is copied to the other cells in the table (range C23:H27).
These indices make it possible to compare the performance of, say, offices between any two years and to track the relative performance over any two years of any two types of property. So it is clear, for example, that industrial property rental grew faster than retail property rentals between 2013 and 2016.
The average figures (column I) are calculated by using the RATE function. This results in an annual growth rate over the entire period.
Here’s the formula in cell I23 that calculates the average growth rate over the term:
The nper argument is 6 in the formula because that is the number of years since the base date.