This guide will discuss how to use the NOMINAL function in Google Sheets.
When we need to calculate the annual nominal interest rate given the effective rate and number of compounding periods per year, we can easily do so using the NOMINAL function in Google Sheets.
Table of Contents
The rules for using the NOMINAL function in Google Sheets are the following:
- The number of compounding periods per year must be a positive integer.
- The values for the effective interest rate or the number of compounding periods per year must be positive.
Google Sheets has several built-in functions that let us accurately perform complex tasks. Moreover, we can use several available functions to perform financial calculations easily.
One of these is the NOMINAL function, which calculates the nominal interest rate based on the effective interest rate and number of compounding periods per year.
The NOMINAL function is often used in financial calculations to convert the effective interest rate to the nominal interest rate. Furthermore, we can use the NOMINAL function to compare interest rates and calculate investment returns.
In this guide, we will provide a step-by-step tutorial on how to use the NOMINAL function in Google Sheets. We will explore the syntax and a real example of using the function.
Great! Let’s dive right in.
The Anatomy of the NOMINAL Function
The syntax or the way we write the NOMINAL function is as follows:
=NOMINAL(effective_rate, periods_per_year)
- = the equal sign is how we start any function in Google Sheets.
- NOMINAL() is our
NOMINALfunction. This function is used to calculate the annual nominal interest rate. Additionally, we need to have an effective interest rate value and the number of compounding periods per year. - effective_rate is a required argument. This refers to the effective interest rate per year. The value must be a positive integer.
- periods_per_year is also a required argument. This refers to the number of compounding periods per year. The value must also be a positive integer.
Common Mistakes in Using NOMINAL Function
The NOMINAL function only has two required arguments, which makes it simple to use. Nevertheless, there are a few things to consider when using the function to avoid errors.
Firstly, the effective_rate argument and the periods_per_year argument must have a positive integer. The NOMINAL function will not support negative values in these arguments.
Secondly, make sure to input an effective interest rate, and not an annual interest rate. If we only have the annual interest rate, we need to convert it to an effective interest rate per period before inputting it into the formula.
To convert the value, we can use the formula:
Effective Interest Rate = (1 + (Annual Interest Rate / n))^n - 1
In the formula above, n refers to the number of corresponding periods per year.
Lastly, make sure to input the correct value in the periods_per_year argument. We must use the number of periods based on the frequency of compounding. For instance, we will type in 12 for monthly or 4 for quarterly.
A Real Example of Using NOMINAL Function in Google Sheets
Let’s say we have a list of investments with their effective annual interest rate and compounding periods. Our initial data set would look like this:
In the spreadsheet above, we can see the investment, each investment’s effective annual interest rate, and their compounding interest. Our goal is to compare investments.
We can easily do this using the NOMINAL function:
=NOMINAL(B2,12)
Since each investment has different values, each cell’s formula will be different. For example, the first investment has an effective annual interest rate of 7% and compounds monthly.
We will select the effective annual interest rate cell as our effective_rate argument. Thus, the first part of our formula is B2. Next, we will input 12 as our periods_per_year argument since it compounds monthly.
Similarly, the formula for the other investment will follow the same structure but have different values depending on their effective annual interest rate and compounding period.
Our final data set would look like this:
You can make your own copy of the spreadsheet above using the link below.
Amazing! Now we can dive into the steps of using the NOMINAL function in Google Sheets.
How to Use NOMINAL Function in Google Sheets
1. First, we will create a new column in the data set to display the nominal interest rate based on the effective annual interest rate and the number of compounding periods per year of each investment.
2. We will select the first empty row to input our formula. To start, we will type in an equal sign and the function name. This will give us the formula “=NOMINAL(”.
3. Next, we will select the cell containing the effective annual interest rate. In this case, our formula would be “=NOMINAL(B2”.
4. Then, we will input the corresponding number of compounding periods of the investment. Since it compounds monthly, we will input “12”. Our final formula would be “=NOMINAL(B2,12)”.
5. We will press the Enter key to return the result.
6. Afterward, we can follow the same steps for the other investments. First, we will select the cell containing the effective annual interest rate. Then, we will input the number of compounding periods (12 for monthly, 4 for quarterly, etc.).
7. Alternatively, we can create a separate column for the compounding periods. We can place each value’s corresponding number of compounding periods per year.
8. Then, we can simply select the cell containing the number of compounding periods. Our formula would become “=NOMINAL(B2,D2)”.
We will press the Enter key to return the result.
10. Now, we can simply drag down the Fill Handle tool to copy the formula and apply it to the other cells.
And tada! We have successfully used the NOMINAL function in Google Sheets.
You can apply this guide whenever you need to calculate the annual nominal interest rate. You can now use the NOMINAL function and the various other Google Sheets formulas available to create great worksheets that work for you.
FAQs
1. I only have the annual interest rate. What do I do?
You must first convert the annual interest rate to an effective interest rate to use the value in the NOMINAL formula.
To do this, you can use the formula:
Effective Interest Rate = (1 + (Annual Interest Rate / n))^n - 1
In the formula above, n refers to the number of corresponding periods per year.
2. How do I calculate the effective interest rate based on the nominal interest rate and the number of compounding periods per year?
You can use the EFFECT function to perform the reverse process of the NOMINAL function. Thus, you will calculate the effective interest rate based on the nominal interest rate.
3. What are other functions related to the NOMINAL function?
The INTRATE function calculates the effective interest rate generated when an investment is purchased at one price and sold at another with no interest generated by the investment itself.
Other related functions are the EFFECT function, the IPMT function, and the PPMT function.
That’s pretty much it! Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
