Financial models are often driven by percentages in one way or another.

Two common ones are the inflation rate and interest rates on debt. When you get an input for one of these it is important to understand whether the percentage given is a nominal or effective rate. In models that stretch over many years, this can have a material effect on the results.

Follow Along File- [ Ссылка ]
------------------------------------------------------------------------------------------------------------------------------------------------------

✅- Free check of your Excel Skill Level- [ Ссылка ]
🏅- Test employee Excel skill levels- [ Ссылка ]
🏆- Improve you Excel Skill Levels- [ Ссылка ]
🇿🇦- Our main website- [ Ссылка ]

------------------------------------------------------------------------------------------------------------------------------------------------------
⏲Time Stamps
00:00 Introduction to nominal and effective rates
00:12 Explaining nominal and effective rates
01:16 Use GOALSEEK to determine effective/ nominal rate
01:57 the Excel functions NOMINAL
02:38 the Excel function EFFECT
03:10 Effective and nominal inflation rates

To follow along go to 17.1 and 17.2

As an example, say you borrow $100 at an effective 12% compounded monthly. If you treat this effective rate as a nominal rate you will calculate that at the end of the year you will receive $112.68. In reality though you will only get $112.

The effective rate is the amount you will get no matter what compounding method is used i.e. if you put $100 in the bank and get 12% EFFECTIVE compounded monthly you will get $112 at year end.

However, if you do the detailed calculation and assume 1% per month you get a different number as shown below.

nominal vs effective

This is because an effective rate takes into account the number of compounding. So in period 1 we get $1 on our $100. But in period 2 there now is $101 in the account, so the 1% is on a higher amount. So we need to use a lower amount per month so that by period 12 we end up with the correct value of $112. Let’s try a 0.95% per month as shown below.

nominal vs effective

This gets us significantly closer.

We can calculate the exact number by converting an effective rate into a nominal rate using the NOMINAL function. You will see below that 12% compounded over 12 periods is a nominal rate of 11.3865515%.

nominal vs effective

We can now use this 11.39% number in our calculation but remember you need to divide it by 12 first.

nominal vs effective

Note that you can do the same thing the other way i.e. convert Nominal to Effective using the EFFECT function.

Creating Factors- Linear or Step Up?
The easiest way to build in an item like inflation is to create a factor that we can multiply against. In period 1 for example sales of $100 would be multiplied by 1 as no inflation has set in (yet). In period 2 we may need to multiply by 1.06 if inflation was 6%. In period 2, this would be 1.1236 (don’t forget the effect of compounding).

This is easy enough if you only have an annual model. But what happens if you have a monthly model and you are told that inflation is 6% effective?

You first need to decide how your factor will be treated. Are revenues and costs affected only once a year with inflation (so all increase happen in January only) or do they occur evenly through the year?

If the increases happen once a year it effectively steps up annually and this is easy to model. But if the inflation occurs evenly throughout the year then there is a linear growth. But we know that at the end of the year the factor must get back to 1.06.

Given what was shown under the Effective and Nominal section, we realise that the effective inflation needs to be converted into a nominal number to allow for the compounding effect. The net effect is shown below where the linear method touches the same point every 12 months as the Step Up method but has a steady increase through the year.

nominal vs effective

свернуть