Calculating Interest and Principal in a Single Payment
Let’s start by reviewing the basics with an example loan (if you already know the basics, you can skip right to Creating an Amortization Schedule):
Imagine that you are about to take out a 30-year fixed-rate mortgage. The terms of the loan specify an initial principal balance (the amount borrowed) of $200,000 and an APR of 6.75%. Payments will be made monthly. What will be the monthly payment? How much of the first payment will be interest, and how much will be principal?
Our first priority is to calculate the monthly payment amount. We can do this most easily by using Excel’s PMT function. Note that since we are making monthly payments, we will need to adjust the number of periods (NPer) and the interest rate (Rate) to monthly values. We will do this within the PMT function itself. Open a new spreadsheet and enter the data as shown below:
Recall that the PMT function is defined as:
PMT(Rate,NPer,PV,FV,Type)
where Rate is the per period interest rate and NPer is the total number of periods. In this case, as shown in the picture, we calculate the Rate with B4/B5 (0.5625% per month), and NPer is B3*B5 (360 months). PV is entered as -B2 (-200,000, negative because we want the answer to be a positive number). You can see that the monthly payment is $1,297.20. (Note that your actual mortgage payment would be higher because it would likely include insurance and property tax payments that would be funneled into an escrow account by the mortgage service company.)
That answers our first question. So, we now need to separate that payment into its interest and principal components. We can do this using a couple of simple formulas (we will use some built-in functions in a moment):
Monthly Interest Payment = Principal Balance x Monthly Interest Rate
Monthly Principal Payment = Monthly Payment – Monthly Interest Payment
Using these formulas, we can see that the interest component of the first payment would be:
Interest in 1st Payment = 200,000 x 0.005625 = $1,125
and the principal payment is:
Principal in 1st Payment = 1,297.20 – 1,125 = $172.20
Note that the sum of the interest and principal is the amount of the total payment:
1,125 + 172.20 = $1,297.20
That is the case for every single payment over the life of the loan. However, as payments are made the principal balance will decline. This, in turn, means that the interest payment will be lower, and the principal payment will be higher (because the total payment amount is constant), for each successive payment.