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.


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 images 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. ...

Get Using Excel for Business and Financial Modelling, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.