Any loan payment has two components: principal repayment and interest charged. Interest charges are almost always front-loaded, which means that the interest component is highest at the beginning of the loan and gradually decreases with each payment.

This means, conversely, that the principal component increases gradually with each payment. How can you tell what the principal and interest components are for any given period of a loan? You can use the PPMT() and IPMT() functions, respectively:

PPMT(rate, per, nper, pv[, fv][, type])
IPMT(rate, per, nper, pv[, fv][, type])

rate The fixed rate of interest over the term of the loan.
per The number of the payment period (where the first payment is 1 and the last payment is the same as nper).
nper The number of payments over the term of the loan.
pv The loan principal.
fv The future value of the loan (the default is 0).
type The type of payment. Use 0 (the default) for end-of-period payments;
use 1 for beginning-of-period payments.

Figure 5.14 shows a worksheet that applies these functions to a loan. The data table shows the principal (column E) and interest (column F) components of the loan for the first ten periods and for the final period.

Note that with each period, the principal portion increases, and the interest portion decreases. However, the total remains the same (as confirmed by the Total column), which is as it should be because the payment remains constant through the life of the loan.

When deciding whether to take out a mortgage, car loan, or other form of debt, it’s a good idea to know how much interest you’ll pay over the term of the loan. The easiest way to calculate this is to multiply the monthly payment by the number of payments to get the total cost of the loan and then subtract the principal:

=(Monthly Payment * Total Payments) – Principal

To calculate the loan payment, use the PMT() function:

PMT(rate, nper, pv[, fv][, type])

rate The fixed rate of interest over the term of the loan.
nper The number of payments over the term of the loan.
pv The loan principal.
fv The future value of the loan.
type The type of payment. Use 0 (the default) for end-of-period payments;
use 1 for beginning-of-period payments.

In Figure 5.14, for example, given the annual interest rate (cell B2), the period in years (B3), and the loan principal (B4), here’s the formula in cell B5 that calculates the monthly payment:

=PMT(B2 / 12, B3 * 12, B4)

In Figure 5.15, I’ve added a Total Interest cell (B6) that uses the payment, period, and principal to calculate the total interest costs over the life of the loan.

There are many business scenarios where you need to know not only the principal and interest you pay each period and the total interest over the loan term, but also how much principal or interest you’ve paid up to a given period.

For example, if you sign up for a mortgage with a five-year term, how much principal will you have paid off by the end of the term? Similarly, a business might need to know the total interest payments a loan requires in the first year so that it can factor the result into its expense budgeting.

You can solve these kinds of problems by using a couple of functions:

CUMPRINC(rate, nper, pv, start_period, end_period, type)
CUMIPMT(rate, nper, pv, start_period, end_period, type)

rate The fixed rate of interest over the term of the loan.
nper The number of payments over the term of the loan.
pv The loan principal.
start_period The first period to include in the calculation.
end_period The last period to include in the calculation.
type The type of payment. Use 0 for end-of-period payments; use 1 for beginning-of-period payments.

The main difference between CUMPRINC() and CUMIPMT() and PPMT() and IPMT() is the start_period and end_period arguments. For example, to find the cumulative principal or interest in the first year of a loan, you set start_period to 1 and end_period to 12; for the second year, you set start_period to 13 and end_period to 24. Here are a couple of formulas that calculate these values for any year, assuming that the year value (1, 2, and so on) is in cell D2:

start_period: (D2 – 1) * 12 + 1
end_period: D2 * 12

In Figure 15.6, I’ve updated the loan model to include columns for the Cumulative Principal and Cumulative Interest.

• 15 vs 30 Year Mortgages - Longer is better! I read an interesting post that really got me thinking today. It was written by a great blogger nicknamed Money...
• Lowering Your Student Loan Payments If your federal student loan payments are becoming difficult to make, there are some payment plans and options you may...
• Post Bankruptcy – Rebuilding Your Credit You knew it when you were going into it, but it was seemingly unavoidable: filing for bankruptcy. Now you have...
• 100 Quick Tennis Tips pt 2 Here are 100 quick and easy tennis tips to get your game going. We're going to spread this list out...
• What Fish Don’t Want You to Know By Frank P. Baron The practice of fishing has been around for millennia, but we as humans still don’t have it down pat. There...