Page 96 - CalcBus_Flipbook

11-2 Credit with MS Excel CH 11]

Use the Standard Loan Payment Equation
The loan payment equation can be used to calculate any type of conventional loan
including mortgage, consumer, and business loan. The equation does not differ based on
what the money is spent on, but only when the terms of repayment deviate from a
standard fixed amortization.
r
Pmt = PV - n
1 — (1 + r)

This equation is especially valuable for planning and budgeting to determine what
payment structure best fits your finances. Note that a fully amortized loan will pay down
both interest owed and principal with a level payment each month; a payment amount
that does not change. Having a level pre-determined payment structure that offers and
allows the budgeter to know what their budget charges will be and thus is able to make
better financial choices for their money.
When paying loans it is important to remember that with each payment Interest on
the loan is paid first and the balance of the payment then reduces the loan amount.

Building a Loan Payment Calculator in Excel

Example: Bryan Dunman negotiated a price on equipment of $12,399. He put $1,240
down and agreed on a four year loan to be payed 12 times a year with an
interest rate on the loan of 17.5% per annum. He needs a loan payment
calculator to determine the monthly payments to be made, the total value of
principal and interest and the total number of payments to be made.
Solution algorithm: Create an excel spread sheet for this problem using the PMT
equation.

1. Launch your Microsoft Excel® spreadsheet program. Assign labels in column B,
rows 2-13. The labels are Loan Calculator, Principal Loan, Annual Interest Rate,
Life of Loan Years, Payments per year, Interest Rate/Period, Interest Rate Factor
and Regular Payment.

2. At location F5 type Number of Payments, at F10 type Total Cost and at F12 type
Total Interest. You can expand the cell by clicking the line on the right side of the
column number, A, B, C, etcetera. Analyzing the graphic column E width was set at
3.5 points. The column G width was widened to 10.4 points. Arrows will then show
that you can manipulate lines. The labels you typed are for your reference only.

3. At location F15 type Table 1 Data. At location E16 Type Loan Amount Factor: LAF.
At location E17 type Interest Rate Factor: IRF. Then at location F18 type Regular
Payment.

Copyrighted Material