Part II: Working with Formulas and Functions
Effective Rate This is the actual rate paid. If the nominal rate period is the same as the
compounding period, the nominal and effective rates are identical. However, as is usually
the case, when the interest compounds over a shorter period than the nominal rate period,
the effective rate will be higher than the nominal rate.
Figure 15.11 shows 12 compounding periods in the middle of a 30-year loan. The original
loan was for $165,000, has a nominal rate of 3.75% APR compounded monthly, and calls for
30 annual payments of $9,169.68 each.
FIGURE 15.11
A partial amortization schedule to compute the effective rate
In each period that the interest compounds but no payment is made, the balance goes up
by the amount of interest. When the payment is made, a little of it goes to the last month’s
interest, and the rest of it reduces the principal.
Cell F17 sums all of the interest compounded over the year, and cell F18 divides it by the
beginning balance to get the effective rate. Fortunately, we don’t have to create a whole
amortization schedule to convert interest rates. Excel provides the EFFECT and NOMINAL
worksheet functions to do that job.
Effective Rate
=EFFECT(F20,12)
Nominal Rate
=NOMINAL(F23,12)
