Almost all of us either know about loans or have taken out a loan in our lives. The premise of a loan is simple: a sum of money is fronted to the borrower up front for an market price interest rate (at least in theory the market price) for the loan duration. Over the duration of the loan, the borrower must make regular scheduled payments to the lender to pay back the amount loaned to them (otherwise known as the principal). The lenders are on the hook for the money upfront, but in exchange they can make a profit from the loan by charging the borrower more money for the loan than they would get by investing it in something else. For financial wizards out there, this is call the interest rate spread.
The way that loans work become very fascinating, however, when they are broken down and analyzed by looking at the interest paid and principal paid. Today we are in a low interest rate environment and have been for quite a number of years now. This means that while the rate of return on fixed investments (like CD’s) is not good for those investing in them, it also means that mortgage rates are at some of their all-time lows which can be a good thing for home buyers. Many of those who bought homes in the 1980’s remembers double digit mortgages, which are a far cry from today’s rates.
In the Microsoft Power BI example, I created, a theoretical loan of $100,000 can be compared on three dimensions: interest rate, duration, and n-th payment. There are three sets of visualizations in this dashboard that make three different sets of comparisons between permutations of loan versions.
- Comparison between a monthly 15 year loan and a monthly 30 year loan. Because the 30 year loan accumulates interest for a longer time (and often at a higher rate), it has a lower payment, but costs more for the borrower over the duration of the loan (total interest paid amount to the lender is higher).
- Comparison of loan performance by selecting an interest rate and duration and seeing the breakdown of the principal and interest paid amounts. Note that principal paid is the SAME for all loans being analyzed in this dashboard because the loan amount is $100,000 for all cases. For loans with higher interest rates, not only is the payment every month higher (though this is an even amount each month), but also a much higher proportion of the payment goes to the interest paid piece than the principal paid, especially at the beginning of the life of the loan. This is because the interest paid is calculated by multiplying the monthly effective rate by the remaining loan balance (see the loan balance chart over time).
- Comparison of interest rates by selecting a duration and a month to analyze the payment breakdown. One of the visualizations shows how much both the payment amount changes as the interest rates go up, and analyzes how the interest paid and the principal paid components make up that total monthly payment. Additional, the amount paid by the very end of the loan is shown, illustrating the load of the interest paid in very high interest rate loans!
Explore the dashboard by choosing the loan details and see how this effects the intrinsic components of the loan, it’s quite fascinating!
-HW