NOMINAL is the Excel function you use when you have an effective annual interest rate and need the stated rate behind it. You give it the effective rate and how many times the interest compounds in a year, and it returns the nominal annual rate.
Savings accounts and CDs are usually advertised with their APY, which already includes the interest you earn on interest. But calculations like monthly interest or a loan payment need the nominal rate instead.
In this article, I’ll show you how to use NOMINAL with five examples, from converting a single APY to building a conversion table and getting a mortgage payment right.
Follow along with the example file
NOMINAL Function Examples.xlsx
NOMINAL Function Syntax
Here is the syntax of the NOMINAL function:
=NOMINAL(effect_rate, npery)
- effect_rate – the effective annual interest rate. For a savings account or a CD, this is the APY.
- npery – the number of times the interest compounds in a year. Use 12 for monthly, 4 for quarterly, 2 for semi-annually, and 365 for daily.
Both arguments are required.
If npery has decimals, NOMINAL quietly drops them. So a value of 12.9 gives you the same result as 12, which matters when the number of periods comes from another formula.
When npery is 1, NOMINAL returns the same rate you gave it. A rate that compounds only once a year has no extra interest on interest, so its effective and nominal rates are the same.
An effect_rate of 0 or less, or an npery below 1, returns the #NUM! error. A blank rate cell counts as 0, so it gives #NUM! too, while text such as “Monthly” in either argument returns the #VALUE! error.
The NOMINAL function works in Excel 365, 2024, 2021, 2019, and 2016 on Windows, and in Excel 365, 2024, and 2021 for Mac.
When to Use the NOMINAL Function
Use this function when you need to:
- Find the nominal rate behind the APY on a savings account or CD
- Get a monthly or daily rate to work out the interest on a balance
- Build a table that converts APYs into nominal rates
- Convert a rate from one compounding frequency to another
- Use the right periodic rate in a loan or mortgage payment formula
Let me show you a few practical examples of how to use this function.
Example 1: Convert an APY to the Nominal Interest Rate
Let’s start with a simple example.
Below I have a savings account with an APY of 4.30% that compounds monthly, and I want its nominal rate in cell B5.
Here is the formula:
=NOMINAL(B2,B3)
This returns 4.22%, which is the interest rate a bank would list next to the 4.30% APY. The account earns 4.30% over the year, but 4.22% is the rate it applies before any compounding.
In the back end, NOMINAL finds the monthly rate that grows to 4.30% over 12 months and multiplies it by 12. As a formula, that’s =B3*((1+B2)^(1/B3)-1), which gives the same 4.22%.
NOMINAL does the reverse of the EFFECT function. EFFECT takes a nominal rate and gives you the APY, while NOMINAL takes the APY and gives you back the nominal rate.
Important: Make sure the APY goes in as 4.30% or 0.043, not as 4.3. If B2 holds 4.3, NOMINAL doesn’t show an error, it returns 178.92%, which is completely wrong.
Example 2: Convert APYs for a List of Accounts
Now let’s convert a whole list of accounts with one formula.
Below I have eight bank accounts, from savings and money market accounts to CDs and checking, each with its APY and the number of times it compounds per year, and I want the nominal rate of each account in column D.
Here is the formula:
=NOMINAL(+B2:B9,+C2:C9)
This one formula in D2 spills the nominal rate for all eight accounts. The plus signs before B2:B9 and C2:C9 are what make that work.
NOMINAL started out in the old Analysis ToolPak add-in, which is why it doesn’t accept a range reference by itself. The plus sign turns each range into a list of numbers that NOMINAL can work with, so the results spill.
Important: If you use plain ranges, as in =NOMINAL(B2:B9,C2:C9), you get the #VALUE! error instead of a spill.
I’ve covered this trick in more detail in my guide on Excel functions that won’t spill.
Now look at the gap between the APY and the nominal rate in each row. High-Yield Savings compounds daily, so its 4.35% APY drops to a 4.26% nominal rate, a gap of 0.09 points.
The 24-Month CD compounds only twice a year, so its 4.10% APY becomes 4.06%, a gap of just 0.04 points. The more often an account compounds, the further its nominal rate sits below its APY.
If you are using Excel 2019 or earlier, enter the below formula in cell D2 and then copy it down to D9:
=NOMINAL(B2,C2)
Example 3: Calculate the Monthly Interest on a Savings Balance
The nominal rate is also what you need to work out the interest for a single month.
Below I have $15,000 in a savings account with a 4.40% APY that compounds monthly, and I want the interest it earns in one month in cell B6.
Here is the formula:
=B2*NOMINAL(B3,B4)/B4
This returns $53.92, which is the interest the account pays for the month.
How this formula works:
NOMINAL(B3,B4)converts the 4.40% APY into a nominal annual rate of about 4.31%./B4divides that annual rate by 12, which gives the monthly rate.B2*multiplies the monthly rate by the $15,000 balance, which turns it into dollars.
If you divided the 4.40% APY by 12 instead, you’d get $55.00. That’s too high, because the APY already counts the interest earned on interest over the whole year.
This is the interest for the first month. Each month’s interest gets added to the balance, so the months after that earn a little more, which is how compound interest works.
Example 4: Build an APY to APR Conversion Table
Let’s step it up and build a full reference table with a single formula.
Below I have a list of APYs from 3.00% to 6.00%, and I want the nominal rate of each one for semi-annual, quarterly, monthly, and daily compounding in columns B to E.
Here is the formula:
=NOMINAL(+A2:A8,{2,4,12,365})
This one formula in B2 fills the whole table, seven rows by four columns. You can use it as an APY to APR chart, where APR means the nominal annual rate.
How this formula works:
- +A2:A8 passes the seven APYs as a vertical list, with the plus sign for the same reason as in Example 2.
- {2,4,12,365} is an array constant, which is a list of numbers typed straight into the formula. The commas make it a horizontal list, so each number lines up with one column.
- NOMINAL pairs every APY with every number of periods and spills one nominal rate for each pair.
The numbers in the formula are what drive the table. The numbers in parentheses in the column headers are only labels, so if you change a frequency, change it in both places.
I’ve formatted the results with three decimals because the differences between columns are small. For a 5.00% APY, the nominal rate only moves from 4.939% with semi-annual compounding to 4.879% with daily compounding.
If you are using Excel 2019 or earlier, enter the below formula in cell B2 and copy it down to B8, then do the same in columns C, D, and E with 4, 12, and 365 in place of the 2:
=NOMINAL($A2,2)
Example 5: Get the Right Monthly Payment When Interest Compounds Semi-Annually
For the last example, let’s use NOMINAL together with EFFECT and PMT to get a loan payment right.
Below I have a $350,000 mortgage quoted at 5.40% compounded semi-annually, paid monthly over 25 years, and I want the monthly payment in cell B9.
The rate compounds twice a year, but you pay every month, so you can’t just divide 5.40% by 12. First, you convert it into the nominal rate for monthly compounding in cell B8.
Here is the formula to convert the rate:
=NOMINAL(EFFECT(B2,B3),B4)
This returns 5.34%. EFFECT turns 5.40% compounded twice a year into an effective annual rate of about 5.47%, and NOMINAL turns that back into a nominal rate that compounds 12 times a year.
Both rates grow a balance by exactly the same amount over a year. The 5.34% is simply the version that matches monthly payments.
And here is the formula for the monthly payment:
=PMT(B8/B4,B5*B4,-B6)
This returns $2,116.04. B8/B4 is the monthly rate, B5*B4 is the 300 monthly payments, and the minus sign before B6 makes PMT show the payment as a positive number.
Important: If you skip the conversion and divide the quoted 5.40% by 12, as in =PMT(B2/12,B5*12,-B6), you get $2,128.45. That’s $12.41 a month more than the correct payment.
If you want to learn more about the payment part of this formula, see my guide on the PMT function.
So these are five ways to use the NOMINAL function in Excel, with savings account, CD, and mortgage data.
Whenever you start with an APY or any other effective rate and need a monthly or daily rate for a calculation, convert it with NOMINAL first.
I hope you found this tutorial helpful.
Other Excel Articles You May Also Like:
