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