This is an interesting question. It touches on standard amortizing loans, and it even involves a bright young student who grew up to become a well-known mathematician.
To answer the question, I'll use a simple example. Suppose you were to borrow $100,000 for five years at 6% interest, with monthly payments. Let's see how amortizing and term loans would work with these facts.
How Standard Amortizing Loans Work
A standard amortizing loan has constant payments over its term. With this approach, a large percentage of your monthly payment is applied to interest in the early years of the loan. But in the later years, as the loan balance slowly declines, more and more of each month's payment is applied to the principle.
In Excel, you use the PMT function to calculate the periodic payment for a standard amortizing loan. It has the form:
=PMT(rate, nper, pv)
periodic rate. With monthly payments, the rate would be:
nper...The number of periods. In this example, we have 60 monthly periods.
pv...The present value, which is the original loan amount, or $100,000 in this example.
That is, your formula would be: =PMT(0.005,60,100000).
If you were to set up an amortization schedule in Excel, the first and last few periods of your loan would look like the figure shown here.
Again, notice that the principle payment increases each period as the amount of the interest declines.
Excel provides a number 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 an investment based on periodic, constant payments and a constant interest rate.
IPMT. Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
NPER. Returns the number of periods for an investment 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 start_period and end_period. (Analysis ToolPak)
CUMPRINC. Returns the cumulative principal paid on a loan between start_period and end_period.
How Term Loans Work
Term loans use a different technique. Each period, you pay the amount of interest due plus a fixed amount for principle reduction. As a consequence, your payments decrease over time.
Here, for example, the amount of the principle paid each period is equal to $100,000 divided by 60, or $1,666.67.
Also notice that the total payment decreases each month as the amount of interest decreases while the principle stays the same.
Excel doesn't provide worksheet functions to support term-loan
calculations. Therefore, we must use spreadsheet formulas.
Calculating Term Loan Values
With one exception, it's quite easy to calculate the values for a term loan. To illustrate, I'll use the following abbreviations. In parentheses I show the values from the example above.
Using these abbreviations, here are the formulas for a term loan:
Interest payment at time CalcPds:
Cumulative principle paid at time CalcPds:
Loan balance at time CalcPds:
Cumulative interest paid at time CalcPds:
Until the final formula above, the term-loan calculations were quite easy. Let's conclude this article by examining how this final formula was derived.