Nominal and effective rates are two ways of measuring the interest rate on a loan or investment. Nominal rate is the stated or quoted rate, without adjusting for inflation or compounding frequency. Effective rate is the actual rate, which takes into account the effects of inflation and compounding frequency. Effective rate is more accurate than nominal rate, because it reflects the true cost or return of borrowing or investing.
Basic Theory
Nominal Rate
The Nominal Rate is the stated or advertised rate on a loan or investment. It does not take into account the compounding frequency. For example, if a loan has a nominal rate of 8% per annum, it implies that the interest is 8% annually without considering how often interest is compounded.
Effective Rate
The Effective Rate, also known as the Annual Equivalent Rate (AER) or Annual Percentage Rate (APR), considers the impact of compounding on the nominal rate. It reflects the actual annual interest earned or paid, accounting for the compounding frequency. It is a more accurate measure for comparing the true cost or return on different financial products.
Procedures in Excel
Nominal Rate Calculation
The Excel formula to calculate the nominal rate is:
=RATE(NPER, PMT, PV, FV, TYPE)
- NPER: Number of periods (total number of payment periods).
- PMT: Payment made each period; it remains constant throughout the annuity’s life.
- PV: Present value, or the total amount that a series of future payments is worth now.
- FV: Future value, or a cash balance you want to attain after making the last payment.
- TYPE: The timing of payments; 0 for end-of-period payments, 1 for beginning-of-period payments.
Effective Rate Calculation
The Excel formula to calculate the effective rate is:
= (1 + Nominal Rate / Compounding Frequency) ^ Compounding Frequency - 1
Scenario: Loan with Monthly Compounding
Let’s consider a scenario where you have taken out a loan of $10,000 with a nominal rate of 6%, compounded monthly over 2 years.
Nominal Rate Calculation
=NOMINAL(0.06, 12)
This formula returns the nominal rate, considering 6% interest compounded monthly.
Effective Rate Calculation
=(1 + NOMINAL(0.06, 12)/12)^12 - 1
This formula calculates the effective rate based on the nominal rate and monthly compounding.
Result
Nominal Rate: 6.00%
Effective Rate: 6.17%
Other Approaches
Using the EFFECT Function
=EFFECT(NOMINAL(0.06, 12), 12)
The EFFECT function directly calculates the effective rate from the nominal rate and compounding frequency.
Data Table for Sensitivity Analysis
You can use Excel’s Data Table feature to perform sensitivity analysis on both nominal and effective rates, exploring various compounding frequencies and nominal rates to observe their impact on the effective rate.
