Excel functions for calculating loan repayments

Banks generally set repayments on loans and mortgages in equal payments over a fixed period of time. Within these regular payments the mix of interest and principal changes as time passes. A single formula can easily calculate the repayments on a loan of £x,000 at y% over z years. The real world is often more complicated and interest rates change at irregular intervals, often part of the way through a particular repayment period.

-	A	B	C
1	1. Basic data about loan
2	Start date	01/May/00
3	Loan OS	35,000
4	Periods	12
5	Periods per year	12
6	Annual interest	6.000%	0.500%
7	Regular payment		3,012.33

The initial stage of this example is simple. £35,000 is borrowed and to be repaid over 1 year in equal monthly payments at an interest rate of 6%.

The Excel PMT() function is used in cell C7 to calculate the monthly repayment. It takes the form: PMT(InterestRate, NumberOfPeriods, Principal, FutureValue, PaymentsDue). The interest rate should relate to a single repayment period and not the annual rate. In this example it is 1/12^th of 6%.

Note that in this example a negative operator (-) is placed in front of the function in order to return a positive value. By default Excel will display repayments as negative amounts.

For loans which are to be repaid over a long period of time it is possible that the interest rate will change and the monthly repayments will be revised to reflect the new rate. Calculating an interest rate change part way through a period can be done in different ways and I don't know whether there is a standard procedure adopted by all banks. The result can vary depending upon factors such as whether they treat all months as equal 12ths of a year or as a variable number of days. The Excel CUMPRINC function can only deal with whole months (or periods) and treats any period value as an integer.

We can make the example more complicated and apply a new rate of interest (5.0%) that comes into effect after 12 February. February is in period 10 of the loan. It is necessary to perform a separate calculation for February (when the rate changes part way through the month) and then for the subsequent months. For the purposes of calculations, the unpaid balance of the loan can then be treated as a new loan to be repaid in equal amounts over the remaining number of periods.

A weighted average of the two interest rates determines the combined rate for February (0.452%). This is based upon the number of days at which each rate is charged.

-	A	B	C	D
10	2. Composite rate for month of change
11	New rate	12/Feb/01	5.00%	0.417%
12		Days	Split	Rates
13	Old rate	12	42.86%	0.500%
14	New rate	16	57.14%	0.417%
15		28	100.00%
16	Weighted average rate for period			0.452%

The new rate of 5% (=0.417%) will then apply to the whole months of March and April.

Two new functions can be used to calculate the interest and the principal amounts for any period (or range of periods) of the loan. The sum of the interest and the principal equals the result of the PMT() function.

The CUMPRINC function calculates the amount of principal repaid in between a specified range of periods. It requires the following series of arguments: CUMPRINC(InterestRate, NumberOfPeriods, Principal, Start_period, End_period, PaymentsDue). Similarly the CUMIPMT function returns the interest paid in between a specified range of periods. CUMIPMT(InterestRate, NumberOfPeriods, Principal, Start_period, End_period, PaymentsDue). In February the number of periods will be 3 (Feb - Apr) and the start and end period will both be 1 because the interest rate applies to that period only. The principal will be the remaining unpaid amount (£8,947) and not the original £35,000.

Note that in common with many other Excel financial functions, these both have a final parameter (PaymentsDue) which refers to whether the payment is made at the beginning or end of the period. In this example the value of '0' indicates the end of the period.

8 Hover with mouse to view formulas in this example
MeadInKent - Make HTML with Hover Nov17.xlsm	A	B	C	D	E	F	G	H	I
18	3. Calculation of payments	From month	To month	Rate	Balance	Interest	Principal	Repaymts	Monthly
19	Before the change	1	9	0.500%	35,000	(1,058)	(26,053)	(27,111)	=H19/(C19-B19+1)(3,012)
20	Month of change	10	10	0.452%	8,947	(40)	(2,969)	(3,009)	(3,009)
21	After the change	11	12	0.417%	5,978	(37)	(5,978)	(6,016)	(3,008)
22							(35,000)

[F19] =CUMIPMT(D19, C21-B19+1, E19, 1, C19-B19+1, 0)
[G19] =CUMPRINC(D19, C21-B19+1, E19, 1, C19-B19+1, 0)
[H19] =G19+F19
[I19] =H19/(C19-B19+1)
[E20] =E19+G19

The formula in cell [F21] might be easier to understand if shown as =CUMIPMT(0.417%, 2, 5978.4, 1, 2, 0)

The sum of the 3 Principal values (in column G) exactly equals the original value of the loan (£35,000).

This methodology is a sound accounting basis for deriving interest charges and repayment terms. They may not however precisely match the calculations of any particular lender - there are a lot of complicating factors and variables. Of key importance in any such arrangements is the frequency or periods at which the interest is calculated. Completely different values will be obtained for a loan over 1 year if the repayment periods are either annual, monthly or daily.

Home	Excel	Access
\| Feedback \| Financial Functions \| Links \|

Calculating interest and repayments on a loan