Group items tagged

Calculating interest and repayments on a loan 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. 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/12th of 6%. [$C$7] = - PMT(C6, B4, B3, 0, 0) 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 in terms 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.

[C13] =B13/B15  [C14] =C15-C13  [D16] =SUMPRODUCT(C13:C14, D13:D14)  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.