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.

Download Practice Workbook

Download this workbook and practice while going through the article.

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.

Practical Example of Calculating Nominal Interest Rate Using NOMINAL Function

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

Read More: How to Use NPER Function in Excel (3 Relevant Examples)

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.

Read More: How to Use RRI Function in Excel (5 Suitable Examples)

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

Read More: XIRR vs IRR in Excel (Differences with Examples)

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.


In conclusion, we have discussed some easy steps of how to use the NOMINAL function in Excel with an example. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please, leave any further queries or recommendations in the comment box below.

Related Articles

Yousuf Khan

Yousuf Khan

Hello! This is MD Yousuf Khan. I am a graduate & post-graduate in Information Technology from Jahangirnagar University, Bangladesh. Currently, I am writing articles for ExcelDemy. I am an independent, self-motivated person with enthusiasm to learn new things, and always try to do my best in any work assigned to me.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF