Analyzing 50+ Years of Mortgage Rates in Excel: Insights and Trends

Posted on: 09/18/2024

Introduction

Understanding mortgage rates is crucial for anyone looking to buy a home or refinance their existing mortgage. In this comprehensive analysis, we'll dive deep into over 50 years of mortgage rate data using Microsoft Excel. We'll explore historical trends, calculate key statistics, and examine how changes in interest rates impact monthly payments. By the end of this article, you'll have a clearer picture of the mortgage rate landscape and some valuable Excel skills to apply to your own financial analysis.

This video was created after the Federal Reserve cut rates by 50 basis points on September 18, 2024. This was the first rate cut in over four years, lowering the benchmark short-term rate. Before the rate cut on September 18, 2024, the last time the Federal Reserve cut interest rates was in March 2020. This cut was part of the emergency measures taken in response to the economic impact of the COVID-19 pandemic.

Obtaining and Preparing the Data

Our journey begins with data collection. Here's how we gathered and prepared the mortgage rate information:

Visit the Federal Reserve Bank of St. Louis website (https://fred.stlouisfed.org/series/MORTGAGE30US#0)
Select "MAX" to view the full historical dataset (over 53 years)
Change the frequency to "Monthly" in the graph settings
Download the data in Excel format
Open the file and enable editing
Convert the file from compatibility mode to the full .xlsx format
Create a copy of the worksheet for analysis

YouTube Video - Mortgage Rates from 1971 to 2024.

Mortgage_Rates_over_50_years

Mortgage FED over 50 years (Size: 78.7KB)

Excel file from the Federal Bank with over 50 years of Mortgage Data. Two worksheets. One worksheet to follow along with the video. The second sheet shows the chart and analysis.

Formatting the Data in Excel

To make our data more usable, we'll perform some basic formatting:

Remove unnecessary rows (1-10)
Rename column headers to "Date" and "30 Year Mortgage Rate"
Format the date column as desired (e.g., short date format)
Convert the data into an Excel table for easier manipulation (Ctrl + T)

Creating a Visual Representation

A chart can help us quickly grasp trends in the data. Here's how to create an effective visualization:

Select the entire data range
Go to Insert > Recommended Charts
Choose a line chart option
Adjust the vertical axis to start at 2.5% for better visibility
Customize colors and styles as desired

Analyzing Mortgage Rate Data

Now that our data is organized and visualized, let's calculate some key statistics:

Basic Calculations

Total Records: 642 (as of September 2024)
Average Mortgage Rate: 7.723%
Median Mortgage Rate: 7.38%
Lowest Rate: 2.684%
Highest Rate: 18.454% (likely in the 1980s)\
Interest Rate Distribution

To better understand the frequency of different rate ranges:

Rates below 3%: 12 occurrences (2% of all records)
Rates between 3-4%: 71 occurrences (11% of all records)

Using Conditional Formatting

We can visually highlight these rate ranges in our data:

Select the rate column
Use Conditional Formatting > Highlight Cell Rules
Set green fill for rates below 3%
Set yellow fill for rates between 3-4%

Calculating Mortgage Payments

To understand the practical impact of changing rates, let's calculate monthly payments for a typical home loan:

Assumptions

Median home price: $400,000
Down payment: 20% ($80,000)
Loan amount: $320,000
Loan term: 30 years

Payment Calculation

Use the PMT function in Excel to calculate the monthly principal and interest payment:

Formula: =PMT(B2/100/12, 360, -320000)

This formula takes into account:

Monthly interest rate (annual rate divided by 12)
Total number of payments (30 years * 12 months)
Loan amount (as a negative number)

Recent Articles

4 Great NEW Microsoft Teams Features: November & December 2024

Microsoft has been extremely busy with new Teams features in November and December 2024. Here is a quick overview of What's New - Name Pronunciation, New Chat and Channels Experience, Improved People Search, and the Refreshed View for Teams and Channels.

Teams: Improved People Search - November 2024

Microsoft Teams has rolled out an enhanced people search feature, providing users with more efficient ways to find and filter content related to specific individuals. This update, released in November 2024, offers a streamlined approach to searching for messages, files, and conversations associated with team members. Let's explore how to make the most of this new functionality.

Speed Up Data Entry and Accuracy with Excel Data Validation Lists

In today's fast-paced work environment, efficiency and accuracy in data entry can significantly impact productivity. One of the best tools to achieve this in Excel is the Data Validation List feature. This guide will walk you through the process of setting up and using Data Validation Lists to streamline your data entry tasks in Microsoft Excel.

How to Remove Duplicates Running Left to Right in Excel Using Power Query

Removing duplicates in Excel is a common task, but it can be challenging when the duplicates are running horizontally (left to right) instead of vertically. In this blog post, we'll explore an advanced Excel technique using Power Query to efficiently remove duplicates across rows. This method is particularly useful when dealing with complex data structures where traditional duplicate removal methods fall short.

Follow a Meeting" Feature in Microsoft Outlook

The "Follow a Meeting" option allows users to indicate that they cannot attend a meeting but still want to receive information about it. This feature is particularly useful for staying in the loop on important discussions and decisions without physically being present in the meeting.

Outlook on the web - 3 Calendar features the desktop version doesn't have

Outlook on the web has many features that Outlook desktop doesn't have. I'll cover three calendar features that Outlook on the web has that Outlook desktop doesn't provide.

Copilot in OneDrive: Revolutionizing Document Interaction

Copilot in OneDrive is here, and it promises to revolutionize how we interact with documents. With features like summarizing documents, creating FAQs, and asking questions about document content, Copilot offers a powerful toolset for enhancing productivity and efficiency.

OneDrive sync icons explained

OneDrive is great for syncing your files. OneDrive has a great feature called Files on Demand. Your files on your computer could have three possible status icons with Files on Demand running.

Chris Menard

Chris Menard is a Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Copilot, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events yearly, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.