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.
You can download the example worksheet to use for yourself.