Excel NOMINAL Function

The Excel NOMINAL function has the syntax shown below.

NOMINAL(effect_rate, Npery)
Arguments used in the Excel NOMINAL function
NOMINAL = Nominal annual rate (i)
effect_rate = Effective annual rate (r)
Npery = Number of compounding periods in a year (m)

Use of the Excel NOMINAL Function

The Excel NOMINAL function is used to calculate the nominal annual rate based on the effective annual rate and the number of compounding periods. The nominal rate does not allow for the effect of compounding whereas the effective rate does.

Excel NOMINAL Function and Semi Annual Compounding

What nominal annual rate compounded semi annually gives an effective annual rate of 8%?

The nominal annual rate is given by the Excel NOMINAL function as follows:

Nominal annual rate = NOMINAL(effect_rate, npery)
effect_rate = 8%
npery = 2 ( 6 month period compounding)
Nominal annual rate = NOMINAL(8%,2)
Nominal annual rate = 7.846%

The required nominal annual rate is lower than the resulting effective annual rate as it does not include the effects of compounding two times within a year.

Excel NOMINAL Function and Quarterly Compounding

What nominal annual rate compounded quarterly gives an effective annual rate of 8%?

The nominal annual rate is given by the Excel NOMINAL function as follows:

Nominal annual rate = NOMINAL(effect_rate, npery)
effect_rate = 8%
Npery = 4 (quarterly compounding)
Nominal annual rate = NOMINAL(8%,4)
Nominal annual rate = 7.771%

The required nominal annual rate is lower than the resulting effective annual rate as it does not include the effects of compounding four times within a year.

Excel NOMINAL Function and Monthly Compounding

What nominal annual rate compounded monthly gives an effective annual rate of 8%?

The nominal annual rate is given by the Excel NOMINAL function as follows:

Nominal annual rate = NOMINAL(effect_rate, npery)
effect_rate = 8%
Npery = 12 (monthly compounding)
Nominal annual rate = NOMINAL(8%,12)
Nominal annual rate = 7.721%

The required nominal annual rate is lower than the resulting effective annual rate as it does not include the effects of compounding twelve times within a year.

Excel NOMINAL Function and Daily Compounding

What nominal annual rate compounded daily gives an effective annual rate of 8%?

The nominal annual rate is given by the Excel NOMINAL function as follows:

Nominal annual rate = NOMINAL(effect_rate, npery)
effect_rate = 8%
Npery = 365 (daily compounding)
Nominal annual rate = NOMINAL(8%,365)
Nominal annual rate = 7.697%

The required nominal annual rate is lower than the resulting effective annual rate as it does not include the effects of compounding 365 times within a year.

Excel NOMINAL Function and Biennial Compounding

What nominal annual rate compounded biennially gives an effective annual rate of 8%?

The Excel NOMINAL function cannot deal with situations where the number of compounding periods is less than 1 and returns the #NUM! error.

To calculate the nominal annual rate in these circumstances the nominal interest rate formula is used as follows:

Nominal annual rate = m x ((1 + r)1/m - 1)
r =  Effective annual rate = 8%
m = number of compounding periods in a year = 0.5 (2 year compounding)
Nominal annual rate = 0.5 x ((1 + 8%)1/0.5 - 1)
Nominal annual rate = 8.320%

In this instance, the required nominal annual rate (8.320%) is higher than the resulting effective annual rate (8%), as compounding takes place less than once per year.

Excel NOMINAL Function and Annual Compounding

Finally, what nominal annual rate compounded annually, gives an effective annual rate of 8%?

The nominal annual rate is given by the Excel NOMINAL function as follows:

Nominal annual rate = NOMINAL(effect_rate, npery)
effect_rate = 8%
Npery = 1 (annual compounding)
Nominal annual rate = NOMINAL(8%,1)
Nominal annual rate = 8.000%

As expected, as the compounding is taking place once per year, the nominal annual rate is the same as the effective annual rate.

The Excel NOMINAL function, is used to convert an effective annual rate, which allows for compounding, into a nominal annual rate which does not allow for the compounding effect.

As seen in the examples above, the more frequent the compounding the lower the required nominal annual rate will be for any given effective annual rate. In the special situation of annual compounding, the nominal annual rate and the effective annual rate are the same.

The Excel NOMINAL function is one of many Excel financial functions used in time value of money calculations, discover another at the links below.

You May Also Like