LOAN AMORTIZATION IN EXCEL

Loan Amortization
with Microsoft Excel

In this tutorial we will see how to create an amortization schedule for a fixed-rate loan using Microsoft Excel and other spreadsheets. Almost all of this tutorial also applies to virtually all other spreadsheet programs such as Open Office Calc and Google Docs & Spreadsheets. Spreadsheets have many advantages over financial calculators for this purpose, including flexibility, ease of use, and formatting capabilities.

You can download the example spreadsheet or follow the example and create your own.

Fully amortizing loans are quite common. Examples include home mortgages, car loans, etc. Typically, but not always, a fully amortizing loan is one that calls for equal payments (annuity) throughout the life of the loan. The loan balance is fully retired after the last payment is made. Each payment in this type of loan consists of interest and principal payments. It is the presence of the principal payment that slowly reduces the loan balance, eventually to $0.

An amortization schedule is a table that shows each loan payment and a breakdown of the amount of interest and principal. Typically, it will also show the remaining balance after each payment has been made.

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.

Using the Built-in Functions

We've now seen how the principal and interest components of each payment are calculated. However, you can use a couple of built-in functions to do the math for you. These functions also make it easier to calculate the principal and/or interest for any arbitrary payment.

The two functions from the Finance menu that we are going to use are the IPMT (interest payment) and thePPMT (principal payment) functions. These functions calculate the amount of interest or principal paid for any given payment. They are defined as:

IPMT(Rate, Per, NPer, PV, FV, Type)

PPMT(Rate, Per, NPer, PV, FV, Type)

So, using our data from above, we can calculate the amount of interest in the first payment with:

=IPMT(B4/B5,1,B3*B5,-B2)

and we get $1,125. The amount of the principal in the first payment is:

=PPMT(B4/B5,1,B3*B5,-B2)

which gives $172.20. Those answers match exactly the ones that we calculated manually above. Note that in both functions, we specified that Per (the payment period) is 1 for the first payment. We would specify 2 for the second payment, and so on. Obviously, we will use a cell reference in our amortization table.

Excel does not have a built-in function to calculate the remaining balance after a payment, but we can do that easily enough with a simple formula. Simply take the beginning balance minus the principal paid in the first payment and you will find that the remaining balance after one payment is $199,827.80:

Principal Balance After 1st Payment = 200,000 - 172.20 = $199,827.80

Creating an Amortization Schedule

As noted in the beginning, an amortization schedule is simply a listing of each payment and the breakdown of interest, principal, and remaining balance. For this loan, an amortization table for the first six months would look like this:

The first thing that we want to do is to set up the table starting with the labels in A8:E8. Now, in column A we want a series of numbers from 0 to 360 (the maximum number of payments that we are going to allow). To create this series, select A9 and then choose Edit » Fill » Series from the menus. This will launch the Series dialog box. Fill it in exactly as shown, and then click the Ok button.

At this point, we are ready to fill in the formulas. Start with the beginning principal in E9 with the formula: =B2. That will link it to the principal balance as given in the input area. Now, select B10 and enter the formula: =PMT(B$4/B$5,B$3*B$5,-B$2), and you will see that the monthly payment is $1,297.20 as shown above. In C10 we will calculate the interest portion of the first payment with the formula: =IPMT(B$4/B$5,A10,B$3*B$5,-B$2). The principal portion of the payment can be calculated, in D10 with: =PPMT(B$4/B$5,A10,B$3*B$5,-B$2). Finally, we calculate the remaining balance in E10 with the formula: =E9-D10.

Check your results against those shown above, being very careful to type the formulas exactly as shown (the $ are important because they freeze the cell references so that they don't change when we copy the formulas down). Once your results in row 10 match the picture, copy the formulas all the way down to the end of the table in row 369. (Note: The easiest way to do this is to select B10:E10 and then double-click the Auto Fill handle in the lower right corner of the selection. This will copy the formulas to the end of the current range, which is defined by the last data point in column A.)

You can now go into the input area (B2:B5) and change the loan terms. The amortization schedule will automatically recalculate.

Make the Amortization Schedule Fancy

Just for fun and some functionality, I fancied it up a bit by using some IF statements, conditional formatting, and creating a chart that shows the remaining balance over time. Even though these things are mostly for looks, they also improve the functionality of the spreadsheet. I'll go through each of these one by one.

Using IF Statements in the Formulas

The formulas that we entered above for the payment, interest, principal, and remaining balance will work most of the time. However, they can give funky answers under certain circumstances. For example, after the last payment is made the remaining balance may be displayed as 0, but Excel might think that it is really something like 0.0000000015. This is due to several factors, including the way that computers do math (in binary instead of decimal, and the conversions aren't always perfect). So, it is helpful to adjust the results of our formulas once the remaining balance is small enough to effectively be 0. If the remaining balance is small enough, then I'm going to tell the formulas to treat it as 0. To do this, I'm using the Round function to round the remaining balance to 5 decimal places to the right of the decimal point. The table below shows the formulas that you should enter into B10:E10 and then copy down the to the end of the table.

Cell	Formula
B10	=IF(ROUND(E9,5)>0,B$6,0)
C10	=IF(B10>0,IPMT(B$4/B$5,A10,B$3*B$5,-B$2),0)
D10	=IF(B10>0,PPMT(B$4/B$5,A10,B$3*B$5,-B$2),0)
E10	=IF(ROUND(E9,5)>0,E9-D10,0)

Again, the only change is that the formulas first check to see if the remaining balance is essentially zero. If not, then they calculate normally. If so, then they return 0 instead.

Use Conditional Formatting to Make it Pretty

Recall that we set up this spreadsheet so that it could handle a maximum of 30 years of monthly payments. What would happen if the loan term was less than that (say, 15 years)? Well, you would end up with a bunch of rows with zeros in them after the loan is paid off. Ugly.

We can fix this with the Conditional Formatting functionality that is built in to recent versions of Excel. Basically, we'd like to make those "empty" cells disappear. If would also be nice if we could underline the last payment as well.

First, select cells A10:E369 since we are going to apply the formatting to all of them at once. Now, go to Format » Conditional Formatting from the menus. That will launch the following dialog box.

Notice that I have set two conditional formats. The first (Condition 1) is the most important. It sets the text color to white for any cells after the last payment has been made. This effectively hides them, but the formulas are still there. We can determine if a cell is after the last payment by comparing the payment number (in column A) with the total number of payments (B3*B5).

I am using the "Formula Is" option, so select that from the drop-down list and then enter the formula: =$A10>($B$3*$B$5) and type it exactly. The $A10 is a relative reference so that in the next row it will change to $A11, then $A12, and so on. Now, press the Format button and set the font color to white.

The second conditional format simply underlines the very last payment. This way we get a visual signal that we have reached the end of the table. In this case we are going to use almost the same logic, except that we are testing to see if we are at the last payment, rather than after it. Press the Add >> button to add this condition. The formula is: =$A10=($B$3*$B$5). Again, type it exactly. Now, press the Format button, and go to the Border tab and set an underline border.

Press the OK button to finish the formatting and return to the spreadsheet. It should look like nothing has happened. Now, change the value in B3 (the number of years) to 15. Scroll down the worksheet and you should see an underline after payment 180 and that all of the cells below that are blank. Cool, huh?

Create a Chart

The final enhancement that I have made is to create a chart that shows the remaining balance declining over time. Basically, all you need to do is to select A8:A369 and E8:E369 and then create an XY Scatter chart. I've fancied it up a little bit with a live chart title and a scroll bar, but I'll leave those features to another tutorial. The final result is shown below.