Using Excel for Business and Financial Modelling, 3rd Edition

CHAPTER 9Common Uses of Tools in Financial Modelling

Now that we know how to use many of the useful tools, functions, and features of Excel, let's take a look at actually using these tools to solve common problems, calculations, and situations encountered in financial models.

ESCALATION METHODS FOR MODELLING

There are several different ways of applying a growth, indexation, or escalation method over a period of time to a principal amount. We often need to be able to include escalation in our models for the purpose of forecasting sales growth year-over-year, for example, or increasing costs by an inflation amount. Calculating a growth amount might seem straightforward initially, but there are several different methods that can be used, and when applying growth rates in a model you need to be clear about which method you are using.

Whilst we can use functions to calculate future values based on set or varying growth rates, we'll first calculate them manually so that we understand the mechanics of the calculations, and then use the functions.

Using Absolute (Fixed) Growth Rate

Here we have compound growth over five years at a fixed interest rate. The capital grows each year by 5 percent. At the simplest level, our formula is The reason we are adding 1 (effectively 100 percent) to the growth rate (5 percent) is that we want the capital sum returned along with the accrued interest. ...