This preview shows page 1. Sign up to view the full content.
Unformatted text preview: tive, the payment would have been positive indicating that you are providing the principle to
someone else and someone is making period payments to you.
The “Fv” window is for future value, if the loan has a balloon payment, the value of that payment
would be placed in this window. As with principle value, Excel is sensitive as to whether the balloon
payment is cash out – negative, or cash in - positive, and this determination will affect the final results.
The “Type” window is where the schedule of payment, at the beginning or the end of the period, is
established. The default is 0 (zero) and is assumed to be payment at the end of the period if not provided.
If populated with the provided data, clicking on the OK button will post the formula,
=PMT(9.375%/4,10*4,10000,0), to the worksheet. A review of this formula confirms your data entry. A
quick check of the result, $387.95 X 40 payments indicates that you will be paying approximately
$15,518 in payments over the 10 years of the loan. This value seems reasonable and can be checked with
two other Excel formulas “PPMT” and “IPMT”. Examine the “Loan” data file on the data disk to see how
a loan table can be built with these formulas as an integral part. The “PMT” formula is found in cell F16
of the “Loan” worksheet and in cell F18 of the “Balloon Payment Loan” worksheet on the “Loan”
worksheet in the Loan data file and is referred to in the matrix. Payment To Principle
Payment to principle of a loan payment for a period can be determined by the Excel “PPMT” (Payment
To Principle) formula. The data windows for this formula are very similar to the data entry windows for
the “PMT” (Payment) formula detailed above except that “PPMT” asks for the period that you want the
payment to principle for. This can be period 1, period 2, period 40, or any other period in the applicable
range of the loan. On the “Loan” data file, this value is defined in column A. In the “Loan” data file, the
maximum life of the loan is 100 years with monthly payments. In this example, the range is from 1 to
(100 years X 12 months per year) 1,200. The output value is under the same logic as “PMT” – if the
View Full Document
- Fall '10