A to Z of Excel Functions: The NOMINAL Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the NOMINAL function.

The NOMINAL function

This function returns the nominal annual interest rate, given the effective annual interest rate and the number of compounding periods per year.

The NOMINAL function employs the following syntax to operate:

The NOMINAL function has the following arguments:

• effective_rate: this is required and represents the effective interest rate
• npery: this is also required.  This is the number of compounding periods per year.

It should be further noted that:

• npery is truncated to an integer
• if either argument is nonnumeric, NOMINAL returns the #VALUE! error value
• if effective_rate ≤ 0 or if npery < 1, NOMINAL returns the #NUM! error value.

NOMINAL(effective_rate, npery) is related to the EFFECT (nominal_rate, npery) function through the identity

Please see my example below: 

These types of calculations give rise to common errors in financial spreadsheets.  One common issue is the inability of many analysts to convert an annual interest rate into a monthly or quarterly rate correctly (or vice versa).  Sometimes the error is immaterial; other times, it can cause major issues (e.g. for bank forecasts).  This isn’t so much an Excel issue as a mathematical problem, but it is still relevant to financial modellers and more often than not, it’s calculated incorrectly.

The key thing any modeller charged with this exercise must do is read the debt term sheet or deposit account prospectus to see what nominal rate is.  Accountants talk about nominal interest rates and such like, but the effective annual rate is the amount of interest expressed as a percentage of the opening debt or cash balance if interest were to be paid and calculated as per the terms of the underlying document.

It’s easier said than done.  And it doesn’t necessarily need either functions cited.

Let me demonstrate with an example: consider a loan of $100 where interest is calculated in arrears on a monthly compounding basis paid quarterly at the end of each quarter.  The effective rate is determined to be 12%.  The question is, what is the appropriate monthly rate be for cashflow calculations?

Who said 1%?  That’s a common answer, derived as 12% divided by the number of months in a year (12).  It sounds good, but if I crunch the numbers I will get the following result:

On a $100 balance with interest of 1% monthly paid quarterly, the total interest for the year will be $12.12 – that’s an effective annual rate of 12.12% rather than 12% (12.00%).  This is not correct, as interest is being rolled up at the end of each of the first two months in the quarter and is attracting further interest, i.e. interest is compounding and has not been taken into account by simply dividing the annual rate by the number of months in a year.

So, what about using the compounding formula instead?  Interest can then be calculated as

This equates to 0.9489% per month.  This will take into account compounding – well, sort of:

This hasn’t worked either: interest has been under-accrued (that’s a good word I’ve just made up) as only $11.49 has been computed for the year – an effective rate of 11.49%.

Here, the calculation is again too simplistic as it does not take into account that the interest is paid quarterly.  This means that compounding throughout the year does not occur, hence the shortfall of 0.51%.

The correct formula is:

If payments are made once every three months then there are four payments (equal to Months in Year / Payment Frequency or 12 / 3) each year.  At these points, compounding stops.  Therefore, the interest rate of 12% per annum is effectively 3% per quarter.  The compounding formula can then be applied to the quarterly rate to get the monthly rate accordingly:

This equates to 0.9902%.  This is correct, viz.

Success!

This is the formula that should be applied to calculate the appropriate monthly interest rate from a quoted effective annual one.  The attached Excel file  provides examples and a template that may be used. 

It should be noted that if interest is paid monthly, the formula above reduces to the simple interest rate method (i.e. simply divide the rate by 12).  Similarly, if interest is only paid at the end of the year, the compounding formula is correct too.  Essentially, the formula explained above is the “halfway house” between the two.

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here.