Chapter 15: Using Formulas for Financial Analysis
15
Both EFFECT and NOMINAL take two arguments: the rate to be converted and the npery
argument. The rate to be converted is the effective rate for NOMINAL and the nominal rate
for EFFECT. The npery argument is the number of compounding periods in the nominal rate
period. In this example, the nominal rate is annual because the term APR was used. There
are 12 months in a year, so there are 12 compounding periods in our nominal rate. If, for
example, you had a loan with an APR that compounded daily, the npery argument would be
365.
Computing effective rate with FV
The effective rate can also be computed with the FV function. With a handy function such
as EFFECT, there’s no need to resort to FV, but it can be instructive to understand the rela-
tionship between EFFECT and FV.
=FV(3.75%/12,12,0,-1)-1This formula computes the future value of a $1 loan at 3.75% compounded monthly for one
year, and then it subtracts the original $1. If you were to take this loan, you would pay
back $1.03815 after the year was over. This means you’d owe an additional $0.03815 more
than you borrowed, or, effectively, 3.815%.
Creating a loan payment calculator
Excel’s PMT worksheet function is used to calculate your monthly payment on a loan. You
can hard-code the values, such as the loan amount and interest rate, into the function’s
arguments, but by entering those values in cells and using the cells as the arguments, you
can easily change the values to see how the payment changes.
Figure 15.12 shows a simple payment calculator. The user enters values in C2:C4, and the
payment is calculated in C6 with the following formula:
=PMT(C3/12,C4*12,C2,0,0)FIGURE 15.12
A simple loan payment calculator
The PMT function takes three required arguments and one optional argument:
Rate (required) The rate argument is the annual nominal interest rate divided by the
number of compounding periods in a year. In this example, the interest compounds
monthly, so the interest rate in C3 is divided by 12.