Microsoft Office Tutorials and References
In Depth Information
Converting Interest Rates
h 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 results in an
effective periodic rate of .5% per month.
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
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:
That results in .01, meaning 1% per month. To convert it to an effective rate, use this formula:
A file named rate conversion.xlsx contains the examples in this section and can
be found on the companion CD-ROM.
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