Loan summaries made easy in Excel

Calculating cumulative interest

Excel’s built-in finance functions allow you to perform interest and repayment calculations quickly and easily. The PMT function allows you to calculate the monthly repayment on a personal loan or a mortgage.

All you need is the principal value, an interest rate and the period of the loan. Excel will do the rest. See rows 1 to 7 in the image below. Yellow cells are for input.

Changing the input cells (B2:B4) changes the repayment. Note the monthly repayment is shown as negative in cell C5.

I received an email question regarding creating a summary table for interest on a loan. I remembered a function I had read about, but never used. I had a play with it and I’m impressed.

CUMIPMT function

There is a function that calculates the cumulative interest between two periods. This function CUMIPMT is perfect for summarising a loan over its life. It makes it easy to create an annual summary table rather than doing a full table with every repayment (That’s what I’d done in the past).

Rows 9 to 16 above have an example summary table which uses the CUMIPMT function and is based on the input cells above.

The syntax of the CUMIPMT function is

=CUMIPMT(Rate, Nper, Pv, Start_period, End_period, Type)

Rate – the interest rate per period

Nper – total number of payment periods for the whole loan

Pv – loan value

Start_period – the starting month number of the loan period to summarise

End_period – the final month number of the loan period to summarise

Type – specifies the timing of the payment.  0 (zero) means payment at end of the period . 1 means payment at the start of the period.

The formula in cell D10 is


This formula has been copied to cells D11, D12, D13 and D16.

The formula in cell D15 is


Mortgage CUMIPMT Example file.


Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.