Excel tip: Calculate loan costs with CUMIPMT
One of the most consistently popular pages on AccountingWEB is an Any Answers question going back to 2001 on ways to calculate the payments on a repayment mortgage in Excel.
The question elicited a variety of responses, but its continuing popularity highlighted the demand for this feature among accountants as well as ordinary finance customers.
To follow up the original post, Excel tutor David H Ringstrom recently posted a tutorial on our sister site AccountingWEB.com that covers the issue in more detail. In this article, David takes the answer to new levels of efficiency by demonstrating how you can use the CUMIPMT function to calculate interest expenses for a loan, whether for a month, a year, or the length of the loan in a single worksheet cell.
Before tackling CUMIPMT, it’s worth considering the PMT function, which calculates the payment amount for a loan. PMT has 3 required and 2 optional arguments:
"Either you work Excel, or it works you!" says David Ringstrom CPA, the head of Atlanta-based software and database consultancy Accounting Advisors. He presents Excel training webcasts for CPE Link and contributes articles on Excel to AccountingWEB and Microsoft Professional Accountant's Network newsletter. He can be reached by email at david[AT]acctadv.com. You can find further ExcelZone tutorials from David H Ringstrom here.