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.

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)

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)

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

=FV(12%/12,12,0,–1)–1