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:

  • rate
  • nper
  • pv
  • fv
  • type

"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.

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments

day counting

Scotton | | Permalink

Any input to use this - or any other excel formula - to expand to a day counter facility? I have a need to compute the IRR for asset finance transactions with the payment and interest due dates being non matched. i.e.: external/internal loan commencing day 2 [monthly cycle] and borrower repayments on any day in month [monthly cycle but not necessarily matched to loan  cycle dates] but with a potential for an up front weighting - e.g. 6 + 30 or 3 + 33 months. Currently using a proprietary software facility and it would be great if we can substitute excel if at all possible.