How to Use the NOMINAL Function

Understanding interest rates is key to accurate financial modeling in Excel. In this guide, we’ll explore the NOMINAL function, which helps us convert an effective rate (APY) into a nominal rate (APR). Once we make this conversion, we can generate more precise projections using Excel’s other financial functions like FV (Future Value) and PMT (Payment).

Video

Step-by-step Tutorial

This blog post provides practical, step-by-step instructions to help make your workbooks more accurate and reliable. Let’s break down how the NOMINAL function works, when to use it, and how it ties into your savings and financial goal calculations.

What Does the NOMINAL Function Do?

Excel’s NOMINAL function converts an effective annual rate (often advertised as APY – Annual Percentage Yield) into a nominal annual rate (APR – Annual Percentage Rate) based on a specified number of compounding periods per year. This is particularly helpful because Excel’s other financial functions often require a nominal rate divided by periods (also called the periodic rate).

The syntax is simple:

=NOMINAL(effect_rate, npery)

• effect_rate: The effective annual rate (APY).
• npery: The number of compounding periods per year (e.g., 12 for monthly, 365 for daily).

Understanding the Difference: APY vs APR

• APY (Effective Rate): Reflects the effect of compound interest during the year. Often used in marketing materials.
• APR (Nominal Rate): The yearly interest rate before compounding. Useful in calculations like PMT and FV.

Step-by-Step: Convert APY to APR in Excel

Exercise 1: Convert an Effective Rate to a Nominal Rate

Say we have an advertised APY of 3.5%, and the interest is compounded daily. Let’s convert this into a nominal rate using the NOMINAL function:

=NOMINAL(0.035, 365)

Result: 3.44% – this is the nominal rate.

Note: if interest is compounded monthly, we would use 12 as the second argument. If quarterly, 4. In other words, this argument represents the number of compounding periods per year.

EFFECT Function: The Opposite of NOMINAL

Just like NOMINAL converts APY to APR, the EFFECT function does the inverse. If we start with a nominal rate of 3.44% compounded daily:

=EFFECT(0.0344, 365)

Result: 3.5% – just as expected!

Exercise 2: Estimate Future Value of Savings

We want to calculate the future value of saving $300 per month, for 10 years, at an APY of 5.12% compounded daily.

Step 1: Set up the input cells

In order to make it easy to change assumptions, we create our input cells like this:

Step 2: Calculate the nominal rate

We write the following function into C10:

=NOMINAL(C9, 365)

Nominal Rate = 4.99%

Step 3: Use the FV function

We write the following formula into C11:

=FV(C10/12, C8*12, C7)

This returns a result of approximately $46,569 – an estimate of how much we’ll have in 10 years.

Note: the FV function assumes interest compounds at the period specified, in this case, monthly. In reality, the interest by the bank compounds daily in this example. This means the result will be a conservative estimate (you’ll actually earn more). The difference isn’t much, but, something to be aware of.

Exercise 3: Back Into Monthly Savings Needed to Hit a Goal

Let’s say we want to hit a savings goal of $100,000 in 10 years with the same APY of 5.12% compounded daily. We want to know how much we need to save each month. So, we’ll set up some input cells to make it easy to change assumptions at will:

Step 1: Convert APY to Nominal Rate

We write the following formula into C10:

=NOMINAL(C9, 365)

Step 2: Use PMT Function

Next, we write the following formula into C11:

=PMT(C10/12, C8*12, 0, C7)

This gives us a monthly payment of approximately $644. This is what we need to save each month to reach our goal.

Note: These functions assume the compounding period is consistent with the other arguments, which in this example is monthly. In this exercise, the bank compounds interest daily, and as such, the result is conservative.

Summary

• The NOMINAL function in Excel converts an effective annual rate (APY) to a nominal annual rate.
• This conversion is essential for using other financial functions like FV and PMT.
• Use FV when you know how much you’re saving monthly and want to know your future balance.
• Use PMT when you have a future goal and want to calculate how much to save each month.

Knowing how to accurately convert an effective rate to a nominal rate lets us improve our financial models and projections.

We hope this guide has given you the confidence to use the NOMINAL function effectively in your financial modeling. It’s a small but powerful tool in any Excel user’s toolkit. Until next time, keep exploring!

Download Sample Workbook

FAQs

1. What’s the difference between APY and APR?
   APY includes compounding effects; APR does not. APY is higher if there’s compounding more than once per year.
2. Why should I use the NOMINAL function in Excel?
   The NOMINAL function allows us to convert APY into the periodic rate needed for Excel’s financial functions like PMT or FV.
3. What does “compounded daily” mean?
   It means interest is calculated based on the account balance every day, increasing the effect of compounding.
4. Can NOMINAL handle different compounding frequencies?
   Yes. You simply change the second argument (compounding periods per year). Use 12 for monthly, 365 for daily, 4 for quarterly, etc.
5. How do I convert APY to a periodic monthly rate?
   First convert APY to nominal using NOMINAL, then divide that nominal rate by 12.
6. Does the FV function take APY directly?
   No. It requires the periodic nominal rate. That’s why converting APY using the NOMINAL function is important.
7. Why does FV return a negative number?
   Excel sees the monthly savings as an outflow. If you want to display it as a positive number, multiply by -1 or use a leading minus sign.
8. Can I use NOMINAL to calculate mortgage interest?
   Not usually. Mortgage rates are often already nominal and quoted as such.
9. Does Excel round the NOMINAL function results?
   Excel returns full precision results, but formatting can visually round the output. Increase decimal places if needed.