Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
The previous examples in this chapter used a simplified method of converting interest rates. They either used a nominal rate that matched the payment terms nicely, or the rates were estimated. The nominal rates were assumed to compound with the same frequency as the payment-say monthly. No conversion was necessary in that case.
In the discounting examples where discount rates where estimated (such as assuming an 8% return on your IRA), it makes no sense to convert those rates. Converting an estimated interest rate in those examples makes it appear that there is some level of accuracy in the rate–and there isn't. In some situations, however, you may need to convert a rate. This section describes different types of rates and how to convert them.
Methods of Quoting Interest Rates
The three commonly used methods of quoting interest rates are
-
Nominal rate: This is the quoted rate. It is quoted on an annual basis, along with a compounding frequency per year-for example, 6% APR compounded monthly.
-
Annual effective rate: This is the actual rate paid or earned annualized. For example, a nominal rate of 6% APR compounded monthly results in $61.68 of interest on a $1,000 loan. That's an effective rate of 6.168%.
-
Periodic effective rate: This rate is applied to the principal over the compounding period, usually less than a year. For example, 6% APR compounded monthly implies a periodic rate of .5% per month.
Conversion Formulas
An interest rate quoted using any of these three methods can be converted to any of the other three methods. Excel provides two functions, EFFECT and NOMINAL, to aid in conversion. The periodic rate is simply the nominal rate divided by the stated compounding period, so no special function is provided for it. The syntax for NOMINAL and EFFECT is
EFFECT(nominal_rate,npery) NOMINAL(effect_rate,npery)
| Note | Most banks and financial institutions quote interest on a nominal basis compounded monthly. However, when reporting returns from investments or when comparing interest rates, it's common to quote annual effective returns, which makes it easier to compare rates. For example, you know that 12% per year compounded monthly is more than 12% per year compounded quarterly-but you don't know (without an intermediate conversion calculation) how much more it is. |
A nominal rate of 12% compounded monthly is converted to a periodic rate as follows:
=.12/12
That results in .01, meaning 1% per month. To convert it to an effective rate, use this formula:
=EFFECT(.12,12)
| On the CD | A file named |
The result of 12.6825% represents the actual interest that's paid or earned in a year. You can also use the FV function to determine the effective rate using a present value of –1, such as
=FV(0.12/12,12,0,-1)-1
If you know you paid $56.41 in interest last year on a $1,000 loan, you can compute the nominal interest with the following formula:
=NOMINAL(56.41/1000,12)
This calculation results in a 5.5% APR compounded monthly.
Категории