Whenever you take out a home, car or personal loan; your repayments are separately servicing interest and principal repayment. The CUMIPMT and CUMPRINC functions enable you to calculate the proportion of each mortgage payment attributable to repaying the mortgage and the proportion attributable to interest servicing.
The arguments required for both functions are identical. Thus it makes sense to learn the two formulas side by side. The worked Excel example attached calculates the principal/interest repayments attributable to the first mortgage payment made on a loan, alongside the total principal/interest repayments due to be made over the entire term of the loan.
- rate – The interest rate applicable. Note that most stated interest rates are annual rates of interest with payments made monthly. The rate should therefore be entered as, for example, 2%/12. If payments were made annually, you would just utilise 2% as the interest rate.
- nper – Represents the total number of payments for the loan. For a 25 year loan with monthly repayments, you would enter 300 (i.e. 25 years * 12 months). If payments were made annually, you would enter 25.
- pv – The present value of the loan balance today (i.e. the total balance of the loan). Whilst perhaps unintuitive, this must be entered as a positive value.
- start_period – The start period for the calculation. Use 1 if you wish to start from the first period.
- end_period – The end period for the calculation. This will depend upon the ‘nper’ value you selected. In this instance, the total number of payments is 300. Therefore, to calculate total principal or interest repaid over the duration of the loan, the figure used should be 300. Alternatively, if you wish to calculate the proportions attributable to your first payment only, you can enter 1.
- type – If payments are made at the end of the period (i.e. in arrears), use type 0. If payments are made at the beginning of the period (i.e. in advance), use type 1.
Interest is higher in earlier periods
Bear in mind that due the decreasing total loan balance over time, payments made towards the start of a loan term will include a higher interest repayment than in later years. Within the example file provided, this is illustrated in the tabs ‘Payment over 25 years – annual’ and ‘Payment over 25 years – monthly’. In year 1, principal payments total £8128 and interest payments £5145. By year 10, the prinicipal repayment increases to £9729 whilst interest repayments fall to £3543.
The CUMPRINC function will return the cumulative principal paid on a loan between two periods, whilst the CUMIPMT function will return the cumulative interest.