The Excel NOMINAL Function

Related Function:
EFFECT Function

Basic Description

The Excel Nominal function returns the nominal interest rate for a given effective interest rate and number of compounding periods per year.

The syntax of the function is:

NOMINAL( effect_rate, npery )

where the arguments are as follows:

effect_rate-The effective interest rate (a numerical value, between 0 and 1).
npery-The number of compounding periods per year (must be a positive integer).


Nominal Function Examples

In the following spreadsheet, the Excel Nominal function in used to calculate the nominal interest rate of three loans with different terms.

 Formulas:
 A
1=NOMINAL( 10%, 4 )
2=NOMINAL( 10%, 2 )
3=NOMINAL( 2.5%, 12 )
 Results:
 A
19.65%
29.76%
32.47%

Formatting the Result as a Percentage

If the result from your Nominal function is displayed as a decimal, or shows 0%, both of these problems are likely to be due to the formatting of the cell containing the function.

Therefore the problem can be fixed by formatting the cell as a percentage, with decimal places.

To do this:

  1. Select the cell(s) to be formatted as a percentage.
  2. Open up the 'Format Cells' dialog box using any one of the following methods:

    • Right-click on the selected cell or range and select the Format Cells ... option from the right-click menu;
    or
    • Dialog Box Launcher in the Number Group of the Excel Ribbon
      Click on the dialog box launcher in the Number grouping within the Home tab of the Excel ribbon (see rightabove);
    or
    • Use the keyboard shortcut CTRL-1 (i.e. Select the CTRL key and while holding this down, select the "1" (one) key).
  3. Within the 'Format Cells' dialog box:

    Excel Format Cells Dialog Box With Percentage Category Selected
    • Make sure that the Number tab at the top of the dialog box is selected.
    • Select Percentage from the Category list on the left side of the dialog box.

      This will cause further options to appear on the right hand side of the control box, which allow you to select the number of decimal places that you want to be displayed (see rightabove).
    • Once you have selected the number of decimal places that you want to display, click OK.

Further details and examples of the Excel Nominal function are provided on the Microsoft Office website.


Nominal Function Errors

If you get an error from the Excel Nominal function this is likely to be one of the following:

Common Errors
#NUM!-

Occurs if either:

  • The supplied effect_rate is ≤ 0
or
  • The supplied npery is < 1.
#VALUE!-Occurs if one or both of the supplied arguments are non-numeric.