# 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

`=CUMIPMT(\$B\$3/12,\$B\$4,\$B\$2,B10,C10,0)`

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

The formula in cell D15 is

`=SUM(D10:D13)`

``` Related Posts SUMIFS Wildcard Limitation Calculating YTD percentages in Excel Percentage Movement in Excel FILTER Function Technique Benford’s Law in Excel – Part Two ```

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

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