Finance Using Excel - Loans

This tutorial illustrates the use of functions @pmt, @ppmt and @ipmt. These may among the most important things you will learn while at Morgan.

Introduction.

Assume you borrow $10,000 over a period of 36 months at 7.0 percent interest. An inexperienced person may fall for the following arguement;

    7 percent per year * 3 years * $10,000 = $2100 interest

    Thus, monthly payment   = $10,000/36 + $2100/36
                            = $ 277.77 + $58.33 = $336.10

Indeed, lendors used to represent this as 7.0 percent interest. However logical it may seem, it is not. You should indeed pay a total of $2100 in interest if you held the full $10,000 for the full three years. But in making monthly payments on the principal, the principal is declining. You do not have the advantage of the the full $10,000 for the 36 months.

In fact, with your first payment, you are paying $58.33 for having $10,000 for but a single month which is indeed seven percent. However, on the last month, you have the benefit of only $277.77 for that one month. In paying $58.33 for that single month, you are paying $58.33 / $277.77 * 12 = 250 percent interest.

The point is, what seems logical, often is not and it can be costly.

This concept might be clearer with a simpler example. Assume you borrow $1000 for one year at 7.0 percent interest. The lender calculates the interest as $1000 * 0.07 = $70 and asks for two equal payments; the first after six months, the second after the full year. This is not 7.0 percent interest, as you really only had the use of an average of $750 for a year. In paying $70, your effective interest rate was closer to $70 / $750 = 9.33 percent.

When taking a loan, you should be only paying on the remaining balance. Payments in the beginning will contain more dollars in interest than later payments.

Thus, calculating constant payments is somewhat complex. What constant payment PAYMENT, consisting of PRINCIPAL_i and INTEREST_i over n periods results in a sum of PAYMENT_i which equals the amount of the loan, while the INTEREST_i is only the interest on the outstanding balance for that period. Of course, mathematicians have developed expressions to do this, but lay people have been left to use tables, page after page of tables for varying principal, interest and time.

Loan Functions using Excel.

However, you can do more by knowing three simple Excel functions.

@pmt(rate, nper, pv)
@ppmt(rate, per, nper, pv)
@ipmt(rate, per, nper, pv)

where;

rate is interest rate per period; e.g., 0.07/12.
nper is the number of equal payments; e.g., 36.
pv (present value) is the amount that is borrowed; e.g., $10,000.
per is the period under consideration; e.g., period 24.

In our example of $10,000 over 36 months at 7.0 percent interest;

Monthly payment is calculated;

@pmt(0.07/12, 36, 10000) equals $308.77

That portion of payment 12 which is principal;

@ppmt(0.07/12, 12, 36, 10000) equals $266.98.

That portion of payment 12 which is interest;

@ipmt(0.07/12, 12, 36, 10000) equals $41.79.

Note that for any period,

@ppmt + @ipmt = @pmt

The arguments may be cells, which may then easily be modified to examine various options; e.g.,

@pmt($b$1, $b$2, $b$3)

Tables.

Assume you desire a table, indicating month by month the principal and interest portions of the payment.

       A       B                        C

1   Interest =  0.07/12
2   N periods = 36
3   Loan Amt =  10000
4   Payment =   @pmt(b1, b2, b3)

9   Period  Principal                       Interest
10  1       @ppmt($b$1, a10, $b$2, $b$3)  @ipmt($b$1, a10 $b$2, $b$3)
11  +a10+1
12

Cell a11 may then be copied to a12..a45 so as to generate periods 1..35. Cells b10..c10 may be copied to b11..c45. You then have a table of payments, including the amount of principal and interest paid each month

You may also use the @sum function to calculate the total amount of principal (or interest) paid as a function of time.