In this video, learn how to visualize the trade-off between principal and interest in your payments.
- [Instructor] When you pay back a fully amortized loan, every payment has two components: the interest for the period and enough principle to pay off the loan within a specified period. In this movie, I'll show you how to visualize the trade-off between principle and interest in your payments. My sample file is the Chart Principle and Interest workbook and you can find it in the Chapter03 folder of the Exercise Files collection. In this workbook, I have calculated the interest and principle components of a loan that we see details for on the left side of the worksheet.
So, this is a 24 period loan and I assume it's a two year construction loan paid off monthly. The rate for such a short-term loan is usually fairly high. In this case, I'm assuming 10.25%. The loan was for $350,000, we're going to pay it all off, and the payment is made at the end of a period. Then, in columns E and F, I have calculated the interest and principle components of each payment. What I'd like to do is to visualize how the interest and principle trade-off.
In other words, as I pay off more principle of the loan, how my interest payments go down and the principle component goes up. To do that, I will create a line chart using the interest and the principle columns as my data sets. So, I'll go to the Insert tab on the ribbon, then I will select cells E3 and F3, and, then, to select the remainder of the data, I'll press Control Shift down arrow. So, I've selected all the way down to row 27, which is all of my data.
Then, in the Charts group, I will click the Insert Line or Area Chart button, and click the first line option, and then I'll drag the chart over to the side, and edit the title, and call it Interest Versus Principle. And you can see in the chart that the principle component goes up as time goes by and the interest component goes down.
The reason for that is, as you pay off principle, there is less of that principle for interest to accumulate against. If you look at a longer-term loan, such as a home loan, you'll see where the curves for interest and principle actually cross. Where it happens in the loan depends on your interest rate, but you will often see some very interesting patterns. Of course, if you pay extra principle with each payment, then the change between the two will be even more dramatic.
- Calculating the effect of interest rates and inflation
- Finding the arithmetic and geometric means of growth rates
- Calculating the future and present value of an investment
- Calculating loan payments for a fully amortized loan
- Calculating the effect of paying extra principal with each payment
- Finding the number of periods required to meet an investment goal
- Calculating net present value and internal rate of return
- Building a cash tracking worksheet
- Visualizing cash flows using a waterfall chart