Excel NOMINAL Function
NOMINAL is an Excel function that calculates the nominal annual percentage rate given the effective rate of interest and number of compounding periods per year.
Let’s you invested $100,000 in a bank deposit paying 10% nominal interest rate compounded semiannually. Your interest income for the first half of the year will be $5,000 [=$100,000 × 10% × 6/12] and your investment balance will be $105,000 [=$100,000 + $5,000]. For the second half of the year, your interest income will be $5,250 [=$105,000 × 10% × 6/12] and your investment balance will be $110,250 [=$105,000 + $5,250]. If you apply the nominal interest rate to the initial investment balance for $100,000 your investment value after the first year turns out to be $110,000 [=$100,000 × (1+10%)]. It turns out that the nominal interest rate doesn’t reflect the effect of multiple compounding period, but effective interest rate does which in this case is 10.25% [use Excel EFFECT function i.e. EFFECT(10%,2)]. If you apply 10.25% for one year to $100,000 initial investment balance, you will get $110,250, which is the exactly the value you get after multiple compounding at the nominal interest rate.
Investment and loan products may quote an effective interest rate, i.e. the rate which when applied to an investment or loan balance gives a future value equivalent to the one obtained after periodic compounding at the nominal interest rate. In such a situation you might be interested in finding the nominal interest rate. Excel NOMINAL does just that.
Syntax
NOMINAL function syntax is:
NOMINAL(effect_rate, npery)
Effect_rate means the annual effective interest rate as explained above.
NPERY refers to the number of compounding periods per year.
Example
Your company obtained an asset worth $2 million on a 5-year lease on 1 July 2017. Lease payments of $122,332 are due each quarter and the effective interest rate on the lease is 8.25%. Your CFO has asked you to work out the interest expense and lease principal repayment for the first and second quarter.
Your assistant prepared the following lease amortization schedule:
He doesn’t seem to get it right, so he has asked you to review it. Identify what’s wrong with the above amortization table and rectify it.
Looking at cell E4 you can see that your assistant applied 8.25% the effective interest rate to the beginning lease liability. Since effective interest rate represent the rate that accommodates the compounding effect each year, interest rate is overstated in the above schedule. You need to find out the nominal lease rate which you can worked out using NOMINAL function:
Using the nominal interest rate, you can rework the amortization table as follows:
by Obaidullah Jan, ACA, CFA and last modified on