Are you a student? Did you know that Amazon offers students 6 months of Amazon Prime – free two-day shipping, free movies and other benefits -?
This is the first of a two-part tutorial on depreciation schedules. In this tutorial, we’ll see how to create a depreciation schedule for a fixed rate loan using Microsoft Excel and other spreadsheets (the next part explains how to handle additional capital repayments and also includes an example of a spreadsheet. calculation using these same examples of data). Almost all of this tutorial also applies to virtually all other computation programs, such as Open Office Calc and Google Document and Spreadsheets. Spreadsheets have many advantages over financial calculators for this purpose, including flexibility, ease of use, and formatting capabilities.
You can download the sample spreadsheet or follow the example and create your own.
Fully depreciable loans are quite common. Examples include mortgages on housing, car loans, etc. Generally, but not always, a fully depreciable loan requires equal payments (annuity) throughout the life of the loan. The loan balance is fully withdrawn after the last payment. Each payment in this type of loan includes interest and principal payments. It is the presence of the payment of the principal which slowly reduces the balance of the loan, possibly to 0 €. If additional capital payments are made, the remaining balance will fall faster than expected in the loan agreement.
An amortization table is a table showing each loan payment and a breakdown of the amount of interest and principal. As a general rule, the remaining balance will also be displayed after each payment.
Calculation of interest and principal in one payment
Let’s start by reviewing the basics with a loan example (if you already know the basics, you can go directly to Creating a depreciation schedule):
Imagine that you are about to take out a 30-year fixed rate mortgage. The loan conditions specify an initial capital balance (the borrowed amount) of € 200,000 and an APR of 6.75%. Payments will be made monthly. What will be the monthly payment? What proportion of the first installment will be interest and what will be the principal?
Our first priority is to calculate the amount of the monthly payment. We can do this more easily by using Excel’s PMT function. Note that since we make monthly payments, we will need to adjust the number of periods (NPer) and the interest rate (Rate) to monthly values. We will do it in the PMT function itself. Open a new worksheet 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 interest rate per period and NPer is the total number of periods. As in the illustration, we calculate the rate with B4 / B5 (0.5625% per month) and NPer is equal to 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 routed to an escrow account by the mortgage company.)
This answers our first question. We must now separate this payment into its main and interest components. We can do it by using some simple formulas (we will use some integrated 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 on the first payment = 200,000 x 0,005625 = 1,125 €
and the main payment is:
Main in 1st installment = 1,297.20 – 1,125 = 172,20 €
Note that the sum of interest and principal is the total payment amount:
1,125 + 172.20 = 1,297.20 €
This is the case for each payment over the life of the loan. However, as payments are made, the principal balance decreases. This in turn means that the payment of interest will be lower and the payment of the principal will be higher (because the total amount of the payment is constant), for each successive payment.
Using built-in functions
We have now seen how the capital and interest components of each payment are calculated. However, you can use some built-in functions to perform the calculation for you. These functions also facilitate the calculation of capital and / or interest for any arbitrary payment.
The two functions of the Finance menu that we will use are: IPMT (interest payment) and the PPMT functions (main payment). These functions calculate the amount of interest or principal paid for a given payment. They are defined as:
- IPMT (Rate, Par, NPer, PV, FV, Type)
- PPMT (Rate, Par, NPer, PV, FV, Type)
Thus, using our data above, we can calculate the amount of interest from the first payment with:
= IPMT (B4 / B5,1, B3 * B5, -B2)
and we get € 1,125. The principal amount in the first installment is:
= PPMT (B4 / B5,1, B3 * B5, -B2)
which gives 172,20 €. These answers correspond exactly to those we have 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. We will obviously use a cell reference in our depreciation table.
Excel does not have a built-in function to calculate the remaining balance after a payment, but we can do it quite easily with a simple formula. Just take the starting balance minus the principal paid during the first payment and you will find that the balance remaining after a payment is 199 827,80 €:
Balance of capital after the first payment = 200 000 – 172.20 = 199 827,80 €
Create a depreciation schedule
As stated at the beginning, a depreciation schedule is simply a list of each payment and a breakdown of interest, principal and balance. For this loan, a depreciation schedule for the first six months would look like this:
The first thing to do is configure the table starting with the labels of A8: E8. Now, in column A, we want a series of numbers from 0 to 360 (the maximum number of payments we will allow). To create this series, select A9, then choose Edit »Fill» Series from the menus. This will launch the Series dialog box. Fill it exactly as shown, then click the OK button.
At this point, we are ready to fill in the forms. Start with the principal beginning in E9 with the formula: = B2. This will link it to the main balance shown in the input box. Now select B10 and enter the formula:
= PMT (4 € B / 5 € B, 3 € B * 5 B €, -2 B €)
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 following formula:
= IPMT (B € 4 / B € 5, A10, B € 3 * B € 5, -B € 2)
The main part 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 balance in E10 with the following formula:
Check your results against those listed above, being careful to type the formulas exactly as shown (the € are important because they freeze cell references so that they do not change when we copy the formulas). Once the results in row 10 match the image, copy the formulas to the end of the array in row 369. (Note: the simplest method is to select B10: E10, then double-click the Auto Fill button handle in the lower right corner of the selection, which will copy the formulas to the end of the current range, defined by the last data point in column A.)
You can now go to the entry field (B2: B5) and modify the loan conditions. The depreciation schedule will be automatically recalculated.
Make the depreciation schedule fancy
Just for fun and some features, I had a bit of fun using IF statements, conditional formatting, and creating a graph that shows the balance remaining in time. Although these items are primarily for appearances, they also improve the functionality of the spreadsheet. I will review each of these points one by one.
Using IF statements in formulas
The formulas we have entered above for payment, interest, principal and remaining balance will work most of the time. However, they can give brilliant answers in certain circumstances. For example, after the last payment, the remaining balance can be displayed as 0, but Excel might think that it is actually something like 0.0000000015. This is due to several factors, including how computers calculate (in binary instead of decimal and conversions are not always perfect). It is therefore useful to adjust the results of our formulas once the remaining balance is small enough to actually be 0. If the remaining balance is small enough, I will tell the formulas to treat it as 0. To do this,, I use the Rounding function to round the remaining balance to 5 decimal places to the right of the separator. The table below shows the formulas to enter in B10: E10, and then copy them to the end of the table.
Again, the only change is that the formulas first check whether the remaining balance is essentially zero. Otherwise, they normally calculate. If so, they return 0 instead.
Use conditional formatting to make it pretty
Remember that we have implemented this spreadsheet so that it can process a maximum of 30 years of monthly payments. What would happen if the loan term was less than that (for example, 15 years)? Well, you’d end up with a bunch of lines with zeros after the loan repayment. Ugly.
We can solve this problem with the conditional formatting feature built into recent versions of Excel. Basically, we would like to make these “empty” cells disappear. It would be nice if we could highlight the last installment as well.
Start by selecting cells A10: E369, because we will apply formatting to all at the same time. Now go to Format »Conditional Formatting in Menus. This will launch the following dialog box.
Note that I defined two conditional formats. The first (Condition 1) is the most important. It sets the color of the blank text for all cells after the last payment. 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 use the “Formula Is” option, select it from the drop-down list, and enter the formula: = € A10> (€ B € 3 * € B € 5) and enter it exactly. The € A10 is a relative reference, so the next row goes to € A11, then to € A12, and so on. Now, press the Format button and set the font color to white.
The second conditional format simply highlights the latest payment. In this way, we get a visual signal that we have reached the end of the table. In this case, we will use almost the same logic, except that we test to see if we are at the last payment, rather than after. 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, go to the Border tab, and set an underlined border.
Press the OK button to finish formatting and return to the spreadsheet. It should seem that nothing has happened. Now, replace the value in B3 (the number of years) by 15. Scroll the spreadsheet and you should see an underline after the payment 180 and all the cells below are empty. Cool huh?
Create a chart
The last improvement I made is to create a chart that shows that the remaining balance is decreasing over time. Basically, all you have to do is select A8: A369 and E8: E369, then create an XY scatter plot. I have a little imagined with a real-time chart title and a scrollbar, but I’ll leave these features to another tutorial. The final result is presented below.