This preview shows page 1. Sign up to view the full content.
Unformatted text preview: column H on the “Loan”
worksheet of the “Loan” data file in column H. However, no balloon payment data can be input on this
worksheet. Interest Payment
The payment to interest of a loan payment for a period
can be determined by the “IPMT” (Payment To Interest )
formula of Excel. The dialog box for this formula is very
similar to the data entry windows for the “PMT”
(Payment) function detailed earlier except that “IPMT”
asks for the period that you want the payment to interest
for. This can be period 1, period 2, period 40, or any
other applicable period within the range of the loan for
the loan example. In the “Loan” data file, the period is
defined in column A. The output value is under the same
logic as “PMT” – if the principle is positive, the payment
will be negative, if the principle is negative, the payment
will be positive indicating cash flows in and out.
This formula is contained within column E of the
“Loan” and “Balloon Payment Loan” matrix in the “Loan” data file. The “If” statement determines if the
period is applicable to the loan. If the period is not relevant to the loan, the “If” statement returns the null
value for a clean and clear presentation. If the period is applicable, the “IPMT” formula looks to the input
data through absolute reference to attain the values for the “IPMT” formula.
Examine the “Loan” data file to see an example of how a loan table can be constructed incorporating
The “IPMT” formula will assist you in determining how much of a loan payment will be the interest
cost value associated with the loan with the payment. Cumulative Payment To Interest
The “CUMIPMT” (Cumulative Payment to Interest) formula will generate the interest for a range of
periods rather than one specific period. Its dialog box is very similar to the “CUMPRINC” (Cumulative
Payment To Principle) discussed earlier. As with the “CUMPRINC” (Cumulative Payment To Principle)
formula, this formula does not take balloon payments into account. Due to the fact that the “Balloon
Payment Loan” worksheet was constructed to take balloon payments into account, this formula was not
usable in tha...
View Full Document
- Fall '10