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.
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.
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 base amount*(1+growth). 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. Multiplying an amount by (1+growth) is a very common calculation in ...