Exploring Debt With Data Analysis

Alejandro Colocho
Oct 5, 2020 · 5 min read
Image for post
Image for post
Photo by: Avery Evans

This article will mix two of my favorite things, finance and data analytics.

Background

Let’s imagine a scenario in which 5 years ago we saved $20,000 to buy a car in full. In this scenario there is no taxes, fess, or any other extra cost associated with purchasing a car. We went to the dealership, and we bought that nice $20,000 car. It’s 2020 now, and we wonder if that was really the best way to go about it. I mean, we have no monthly payments, so thats good right? On the other hand, we could have done monthly payments on that car and kept the $20,000 a little bit longer. But, there is interest associated with that, so we sure have done the right decision. If we kept the $20,000 a little bit longer, then maybe we could have used that money for something else. Perhaps we could have bought stocks. In order to investigate what we could have done differently, we will use python to research if investing our money in the stock market would have been a better choice than to use it to pay the car in full.

Let’s Get Started

First, we will be importing all the libraries that we’ll need:

We’ll use pandas to work with data frames, Matplotlib to visualize data, and seaborn to visualize data. Now, we need to start by being able to calculate our monthly payments. We’ll code the following formula to python.

Image for post
Image for post

In order to better visualize what we’re paying and when, we will create a function that generates an amortization schedule. An amortization schedule shows us how much interest and how much principal we are paying. The principal is basically the loan amount. We can generate that using the following function

The previous function will generate an amortization schedule, and it will return a data frame to better view it. Now, we need to research what the average car loan rate was back when we bought our car. According to The Fed, the average rate was 4.05% for a 5 year loan. Now, let’s plug in some numbers in our functions.

We will get the following output.

Image for post
Image for post

Cool, so we see how payments look like for the first 5 periods. We can see the whole amortization schedule by removing the .head(). Now, let’s get monthly data on stock market returns. For this example, we will look at SPY returns. SPY is an ETF that tracks the S&P 500, so it is well diversified. It should expose us to a comfortable amount of risk and reward. We will be getting the data from Yahoo Finance as a CSV file, and we will be using pandas to manipulate it.

We will get this following output.

Image for post
Image for post

This helps us get familiar with how the data frame looks like. Now, we will get data from the past 5 years. For some reason, the data includes two data points for October of 2020, so we will get rid of one.

Now, we want to find out what the change of the stock was every period for the past 5 years, and we want to sum the total of our returns.

The previous function will return a list of the sum-to-date of our returns. We are assuming that we are not selling any stock from our portfolio, and we are making payments from our regular income. Now, let’s call our function to calculate our returns.

We have everything we need, so let’s visualize our results with the following code.

It will output the following.

Image for post
Image for post

Interpret The Graph

The orange line represents the balance of the loan, the blue line represents the sum of the interest paid, and the green line represents our stock market gains and lost. Both orange and blue line are expected. It is a linear relationship to the number of periods. As we pay our monthly payments, our total interest payment will go up and our balance on the loan will go down.

The green line is not as easy to understand, but we can tell that it generally goes up. One of the risks in investing in the stock market is that the future is uncertain, and we do not have any guarantees that we will make our money grow. For example, we can see that around the 50th period the stock we invested in took a sharp dive, but the stock gained it all back. Around that period is when coronavirus started. There was no way any one could have predicted that a pandemic was on the horizon, so the market was not prepared.

Overall, we would have gained $11,963.23 and paid $2,126.93 in interest. We can say that we would have turned a $9,836.3 profit.

The Wrap up

A $9,836.30 profit over 5 years is perhaps not a whole lot of money, but it is more money than we began with. However, when the sum of money increases, our profit will increase as well. Unfortunately, we made some assumptions that would not hold in real life. The concept that debt is bad is not always true. Debt is a financial tool to accomplish goals. The lower the cost of debt, the more of an attractive tool it is.

Besides the quick lesson in finance, this is a simple example of the power of data analytics. In the near future, data analytics will not be a competitive advantage, it will be a necessity to be in the business enviroment.

This article has limitations, and I am not a financial advisor. Do not take this article as financial advise.

Data Driven Investor

empower you with data, knowledge, and expertise

Sign up for DDIntel

By Data Driven Investor

In each issue we share the best stories from the Data-Driven Investor's expert community. Take a look

By signing up, you will create a Medium account if you don’t already have one. Review our Privacy Policy for more information about our privacy practices.

Check your inbox
Medium sent you an email at to complete your subscription.

Alejandro Colocho

Written by

Data Scientist | Gamer | Nature Lover | Gym Enthusiast

Data Driven Investor

empower you with data, knowledge, and expertise

Alejandro Colocho

Written by

Data Scientist | Gamer | Nature Lover | Gym Enthusiast

Data Driven Investor

empower you with data, knowledge, and expertise

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store