The power of excel can be used to overcome its few

This preview shows page 1. Sign up to view the full content.

This is the end of the preview. Sign up to access the rest of the document.

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

This note was uploaded on 09/19/2010 for the course ACCT 220 taught by Professor Ullmann during the Fall '10 term at University of Nebraska Kearney.

Ask a homework question - tutors are online