How to Use NOMINAL Function in Excel

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is a handy software. We use Excel functions for our education, business, and financial analyses. Moreover, in light of the effective rate and the number of compounding periods per year calculating the nominal annual interest rate, we thoroughly use Excel NOMINAL function. With this in mind, we are going to learn how to use the NOMINAL function in Excel with an example.

Introduction to NOMINAL Function

The Excel Financial Functions section includes the NOMINAL Function. It determines the number of compounding periods per year and the nominal annual interest rate for a given effective interest rate. Alternatively, the NOMINAL function is the EFFECT function’s inverse calculation. The following equation describes their relation.

Equation based on NOMINAL and EFFECT relationships


  • Nominal Rate = The nominal annual interest rate.
  • Npery = The number of compound periods per year.

Therefore, NOMINAL determines the number of compounding periods per year and the nominal annual interest rate for a declared effective interest rate. When the effective rate and the number of compounding years are provided, the function will return the nominal yearly interest rate. In financial analysis, we frequently compare multiple bonds, therefore we’re curious to know which one offers the best overall return. Moreover, the NOMINAL function can be useful when comparing two bonds, one of which advertises a real interest rate and the other a nominal rate.

Syntax: The syntax of the NOMINAL function is as follows:

=NOMINAL(effect_rate, npery)

Syntax of NOMINAL Function

Arguments Explanation:

Arguments Required or Optional Explanation
effect_rate Required A numeric value between 0 & 1 or a percentage.
npery Required The number of compound periods per year.

Return Value: Returns the nominal annual interest rate.

For this article, I have used the Office 365 version. The NOMINAL function is available from Excel 2000 to the present version.

How to Calculate Nominal Interest Rate Using NOMINAL Function: Practical Example

Calculating the annual nominal interest rates can be cumbersome if we have a large dataset. However, with the help of the NOMINAL function, we can determine them quickly and effectively. After we obtain the decimal values as outputs, we will format them in percentages using the Format Cells feature. Let’s get into it. To demonstrate, we take a dataset that represents annual nominal rates and periods of a credit bank.

excel nominal function

Step 1: Create Dataset with Proper Parameters

The first step aims to introduce a proper dataset with correct information. Let’s add the following sub-headings as follows.

  • Include the headers Effective Rate, Compound Periods, and Nominal Rate in columns B, C, and D respectively.
  • Afterward, populate the headers with the correct values.
  • Thus, we obtain our desired dataset.

Create Dataset with Proper Parameters

Step 2: Apply NOMINAL Function

The objective of the second step is to utilize the NOMINAL function for the given values. As already discussed, the function determines the nominal interest rate and returns a decimal. Therefore, let’s follow the instructions below to implement the function properly.

  • First, type the following formula in cell D5,
  • Later, press the Enter or Tab keys.

Applying NOMINAL Function

  • As a result, we get an output in D5.
  • Next, use the AutoFill tool to drag the output cell down.
  • Hence, we obtain our desired nominal rates in our dataset.

Applying NOMINAL Function

  • The Effective rate should be between 0 and 1.
  • The Compound Periods have to be an Integer. If not, Excel will truncate the inputs.

Step 3: Use Format Cells Feature

In our last step, we will format the previously obtained decimal into percentages. To illustrate, we will use the Format Cells feature in Excel. To do so, follow the easy procedure below.

  • First, select the range D5:D10 and right-click on the selected range.

Use Format Cells Feature

  • Consequently, a context menu pops up.
  • There, locate the Format Cells option and tap on it.

Use Format Cells Feature

  • Subsequently, the Format Cells dialog box appears.
  • Further, go to Number → Percentage and then set the Decimal places to 2.
  • Lastly, click OK to complete the formatting.

Use Format Cells Feature

  • Finally, we can see the effective rates in our display.

Use Format Cells Feature

Reasons Behind NOMINAL Function Not Working in Excel

The common errors likely to appear are as follows.

  • #NUM! appears if the given effective rate is ≤ 0.
  • #NUM! also shows up if the given Npery is < 1.
  • #VALUE! pops up if we input non-numeric values.

Download Practice Workbook

Download this workbook and practice while going through the article.


In conclusion, we have discussed some easy steps of how to use the NOMINAL function in Excel with an example.  Please, leave any further queries or recommendations in the comment box below.

<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Yousuf Khan
Yousuf Khan

Yousuf Khan has been working with the ExcelDemy project for over one year. He has written 47+ articles for the ExcelDemy site. He also solved a lot of user problems with ExcelDemy Forum. He has interests in Excel & VBA, Desktop and mobile applications, and projects & templates. He completed his graduation and post-graduation in Information Technology from Jahangirnagar University. Currently, he works as a VBA & Excel Content Developer in ExcelDemy projects, writing unique and informative content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF