Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel tip: Calculate loan costs with CUMIPMT

by
7th Nov 2012
Save content
Have you found this content useful? Use the button above to save it to your profile.

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 - The interest rate for the loan expressed as a monthly rate.
  • nper - The length of the loan in months.
  • pv - The amount being borrowed, also referred to as the present value.
  • fv - This optional argument allows you to specify a future value if a balloon amount is due at the end of the loan. Omitting this argument implicitly states that the loan is to be paid down to 0.
  • type - This optional argument allows you to specify if payments are made at the beginning of each period, or you can omit the argument to indicate that payments are made at the end of each period. You may also specify 0 in this position to explicitly indicate that payments are made at the end of each period.

Excel PMT function calculates the monthly payment for a loan

As shown above, a monthly payment of £586.04 for 36 months is required to pay back £20,000 at an interest rate of 3.5%. The PMT function always returns a negative amount because Excel sees the payment as an outflow. This example does not include the 2 optional arguments, so the PMT function assumes here that the loan is settled in full and payments are made at the end of each period.

You build formulae with CUMIPMT in the same way as PMT, but in this case, all six arguments are required:

  • rate - The interest rate for the loan expressed as a monthly rate.
  • nper - The length of the loan in months.
  • pv - The amount being borrowed, also referred to as the present value.
  • start_period - The starting month from which to calculate interest on the loan. Use 1 to calculate interest from the start of the loan, or 13 to calculate interest for just the second year of the loan.
  • end_period - The ending month through which to calculate interest on the loan. Use the same value as the nper argument to calculate interest for the life of the loan, or 24 to calculate interest for just the second year of the loan.
  • type - Specify 0 to indicate that payments are made at the end of the period, or 1 for payments made at the start of the period.

Excel CUMIPMT function used to calcuate total interest for a loan

As we see from the image above, CUMIPMT shows that borrowing £20,000 at 3.5% for 36 months will cost £1,097.50. The cost for the second year of the loan is £368.55, but the answer is not visible in the illustration. To calculate the principal paid back for a given portion of the loan, use CUMPRINC, which applies the arguments as CUMIPMT.

These worksheet functions can calculate interest and principal for all or part of a loan without constructing a full-scale amortisation schedule. If you need such a model, right-click on any worksheet tab and then choose Insert, as shown below. Click on the Spreadsheet Solutions tab, and then double-click on Amortization Schedule.

Add Amortization Schedule to Excel worksheet

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

Replies (1)

Please login or register to join the discussion.

avatar
By Scotton
19th Nov 2012 11:10

day counting

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.

Thanks (0)