“How do I calculate cumulative principal and interest for term loans? I have scoured the web for a function that will perform this task, with no avail. “
— Lake M.
This is an interesting question because it touches on at least four issues related to the time value of money:
1. How Do You Define a Term Loan?
The definitions vary. For example, here’s how different sources define a term loan:
- Business Dictionary: Asset based short-term (usually for one to five years) loan payable in a fixed number of equal installments over the term of the loan. Term loans are generally provided as working capital for acquiring income producing assets (machinery, equipment, inventory) that generate the cash flows for repayment of the loan.
- Wikipedia: A monetary loan that is repaid in regular payments over a set period of time. Term loans usually last between one and ten years, but may last as long as 30 years in some cases.
- Investopedia: A loan from a bank for a specific amount that has a specified repayment schedule and a fixed or floating interest rate.
2. Is the Loan’s Interest Rate Fixed or Floating?
I’m going to assume that the interest rate is fixed. I’ll cover floating rate loans at another time.
3. What Type of Loan is It? Even-Payment? Or Straight-Line?
In How to Create Even-Payment and Straight-Line Amortization Tables in Excel, I used these two figures to show the trends in periodic payments for each type of loan.
Here, as you can see, even-payment loans have the same payment every period. But straight-line loans pay the same amount of principal each period plus a declining amount of interest.
4. What Help Does Excel Provide for Each Type of Loan?
My visitor asked about cumulative payments of principal and interest. In this article, I’ll explain how to calculate those amounts for both types of loans. And in How to Create Even-Payment and Straight-Line Amortization Tables in Excel, I explain how to create the amortization tables I’ll show you and how to calculate the interest for any period.
How Even-Payment Loans Work
(You can download the files for this article here.)
An even-payment loan has constant payments over its life. In Excel, you use the PMT function to calculate that periodic payment. The function has this syntax:
=PMT(rate, nper, pv)
…where…
- rate is the periodic rate. With monthly payments, the rate argument for the PMT function equals the nominal rate (like 6% per year) divided by the number of periodic payments per year.
- nper is the total number of payment periods. So a five-year loan with monthly payments would have 60 payment periods.
- pv is the present value, which is the original loan amount.
If you were to set up an amortization schedule in Excel, your loan would look like this:
(My article, How to Create Even-Payment and Straight-Line Amortization Tables in Excel, explains how to set up this table.)
Excel provides a variety of worksheet functions for working with amortizing loans:
- PMT. Calculates the payment for a loan based on constant payments and a constant interest rate.
- FV. Returns the future value of a loan based on periodic, constant payments and a constant interest rate.
- IPMT. Returns the interest payment for a given period for a loan based on periodic, constant payments and a constant interest rate.
- NPER. Returns the number of periods for a loan based on periodic, constant payments and a constant interest rate.
- RATE. Returns the interest rate per period of an annuity.
- CUMIPMT. Returns the cumulative interest paid on a loan between a starting period and an ending period.
- CUMPRINC. Returns the cumulative principal paid on a loan between a starting period and an ending period.
- IPMT. Returns the interest portion of a periodic payment for any specific period.
How Straight-Line Loans Work
With a straight-line loan, you pay the amount of interest due each period plus a fixed amount for principal reduction. As a consequence, your payments decrease over time.
As you can see in this example, the total payment decreases each month as the amount of interest decreases while the principal stays the same.
Calculating Values for Straight-Line Loans
As explained in How to Create Even-Payment and Straight-Line Amortization Tables in Excel, Excel provides the ISPMT function to return the amount of the interest payment for any period of a straight-line loan. I’m not aware of any other functions that Excel provides specifically four straight-line loans.
Therefore, we must use spreadsheet formulas to calculate key information about these loans:
Principal payment:
= Loan / Nper
The principal amount each period is equal to the loan amount divided by the total number of periodic payments.
Interest payment at time Period:
=PdRate*(Loan-(Period-1)*PrinPmt)
The amount of the interest payment for a specified Period is equal to the balance of the loan for the previous period, multiplied by the periodic interest rate. The loan balance for the previous period is equal to the amount of the original loan multiplied by the current period minus 1, multiplied by the periodic principal payment.
Cumulative principal paid at time Period:
=PrinPmt*Period
The cumulative principal paid for a specific Period is equal to the periodic principal payment times the Period number.
Loan balance at time Period:
=Loan-Pmt*CalcPds
The remaining loan balance is equal to the beginning Loan amount minus the cumulative principal paid.
Cumulative interest paid at time CalcPds:
=PdRate*(Period*Loan – ((Period^2-Period)/2) * PrinPmt)
Until the final formula above, the term-loan calculations were easy. Let’s conclude this article by examining how this final formula was derived.
Calculating Total Interest Paid for a Term Loan
(You can download the files for this article here.)
When you work with periodic cash flows, and you want to derive a general formula about those cash flows, it often helps to show how each periodic amount is calculated. Then you look for a pattern.
To illustrate, the interest amounts for the first three interest payments are:
PdRate * (Loan – 0 * PrinPmt)
PdRate * (Loan – 1 * PrinPmt)
PdRate * (Loan – 2 * PrinPmt)
To calculate the total of these three interest payments, we can combine the terms, like this:
= PdRate * (3 * Loan – (0 + 1 + 2) * PrinPmt)
So in this table, the cumulative interest at period 3 is…
=10% * (3 * 4,000 – (0 + 1 + 2) * 1,000)
…which equals 10% * (3 * 3000), or 900.
You can check this calculation by adding up the interest amounts for the first three payments in the Term Loan Amortization table above.
To create a general formula to calculate the cumulative interest rate, we first must find a way to calculate the sum of an arithmetic series like this:
1 + 2 + 3 + 4 …
The story is that the mathematician Carl Gauss (1777 – 1855) derived the formula when he was a young student. His class was asked to add up the numbers 1 through 100. The other students laboriously added 1 + 2 + 3 and so on. But Gauss took a shortcut. He noticed that:
- 1 + 100 = 101
- 2 + 99 = 101
- 3 + 98 = 101
- and so on.
This pattern happens 50 times, so the total of all 100 numbers must be 50 times 101, or 5050.
Gauss then derived a general formula for the sum of any such series:
n * (n + 1) / 2.
That is, 100 * 101 / 2 = 5050.
So, with the help of a young student, we can find the cumulative interest for a term loan. After the number of months specified by CalcPds, the total interest paid is this…
=PdRate*(Period*Loan – ((Period-1)*((Period-1)+1)/2) * PrinPmt)
…which we can simplify to this…
=PdRate*(Period*Loan – ((Period-1)*Period/2) * PrinPmt)
…and then to this:
=PdRate*(Period*Loan – ((Period^2-Period)/2) * PrinPmt)
It would be nice if the Excel team could give us a worksheet function that would perform that operation. Perhaps they will some day.
