This preview shows page 1. Sign up to view the full content.
Unformatted text preview: positive, the payment will be negative. Examine the “Loan” data file to see an example of
how a loan table can be constructed with these formulas. The “PPMT” formula is contained within
column D of the “Loan” and “Balloon Payment Loan” worksheet matrix on the “Loan” data file. It looks
to column A to see if the line is applicable to the loan –
unneeded lines utilize the “null value” to appear clear
and clean. The “Null Value” is addressed in its own
section elsewhere in this book.
The “PPMT” formula will assist you in determining
how much of a loan payment will reduce the obligation
of the loan with the payment. Cumulative Payment To Principle
The formula “CUMPRINC” will generate the
cumulative principle paid or received to date if all
payments were made or received appropriately. The
dialog box for the “CUMPRINC” formula is shown Chapter 15, Page 95
here. The input values are similar to other loan associated formulas and functions. While this is an easy
formula to set up, it does not take into account or have the capability of addressing loans with balloon
payments. To overcome this situation, the “Balloon Payment Loan” worksheet of the “Loan” data file
simply uses a “Sum” formula from rows 23 on down in column H. The technique within the “Balloon
Payment Loan” worksheet is sum from an absolute reference of row 23 through its relational row as
dragged through the matrix so the formula in column H on the “Balloon Payment Loan” worksheet looks
like SUM($D$23:D23) since column D contains the payment to principle values. When the formula, with
its added “If” statement to provide the null value for a clean appearance if the period is not applicable to
the loan, is dragged down the relational “D23” cell reference will increment to the row so the formula in
row 24 will appear to be SUM($D$23:D24), thus summing the values of column D rows 23 and 24. The
power of Excel can be used to overcome its few faults and weaknesses.
The “CUMPRINC” (Cumulative Payment To Principle) formula is shown in...
View Full Document
- Fall '10