Excel Mid Year discounting and inflation

We recently received some questions on why inflation in a financial model was calculated using


The issue was the use of 0.5 instead of the number of years in whole numbers.

What is happening here is that the developer is using the concept of mid year discounting (or in this case inflating). Normally you would grow it by full years (so in the formula it would say ^1 for year 1, ^2 for year 2 etc.).

If 0.5 is used then it means that we are expecting inflation to gently increase evenly through the year. So at the beginning of the year there is no inflation but at the end of the year it is 6%. During the  year, on average, the costs will only increase by about half inflation (assuming that costs also are evenly spread).

The key if you see 0.5 in the first year calculation is to make sure that from then on it increases by a full year if you are doing an annual model. So the next formula should be ^1.5, and then ^2.5 etc.

Below some more on the functions you can use for mid year discounting in Excel

To learn more about Time Value of Money calculations (especially with the new IFRS requirements) look at the Time Value of Money in Excel online course.

XNPV and effective rate