Part II: Working with Formulas and Functions
nper (required) The nper argument is the number of payments that will be made over
the life of the loan. Since our user input asks for years and our payments are monthly, the
number of years in C4 is multiplied by 12.
pv (required) The pv argument, or present value, is the amount being borrowed. Excel’s
loan functions, of which PMT is one, work on a cash flow basis. When you think about
present value and payments as cash inflows and outflows, it’s easier to understand when
the value should be positive or negative. In this example, the bank is loaning us $215,000,
which is a cash inflow and thus positive. The result of the PMT function is a negative
because the payments will be cash outflows.
If you want the PMT function to return a positive value, you can change the pv argument to a negative number. That’s
like calculating the payment from the bank’s perspective: The loan is a cash outflow, and the payments are cash
inflows.
The most common mistake in financial formulas is a mismatch between compounding periods and payment fre-
quency. In this example, the rate is divided by 12 to make it a monthly rate, and the nper is multiplied by 12 to make
it a monthly payment. Both arguments are converted to monthly, so they match, and we get the correct result.
If we forgot to divide our rate by 12, Excel would think that we were entering a monthly rate, and the payment would
be way too high. Similarly, if we entered years for our nper and a monthly rate, Excel would think that we were paying
only once a year.
Excel doesn’t really know whether you enter months, years, or days. It only cares that the rate and nper match.
Creating an amortization schedule
With the payment amount calculated, we can create an amortization schedule that will
show how much of each payment is principal and interest and what the loan balance will be
after each payment. Figure 15.13 shows a portion of the amortization schedule.
The columns are of the amortization schedule are detailed here:
Pmt No The number of the payment being made. A 1 is entered into D11. The formula
=D11+1 is entered into D12 and copied down to D370 (our amortization schedule can handle
360 payments).
Pmt Amt The amount of the PMT calculation rounded to the nearest penny. While Excel
can calculate a lot of decimal places, we can write a check only for dollars and cents. This
means there will be a small balance at the end of the loan. The formula =-ROUND($C$6,2)
is entered in E11 and filled down through E370.
