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

Copilot in Teams Control Access: Manage Recordings, Transcriptions & AI Insights

If you're a Microsoft Teams user with Copilot, exciting new features have been rolled out. You can now manage who has access to meeting recordings, transcriptions, and AI-generated insights as the meeting organizer. This enhances security and privacy in your Teams meetings.

Teams Calls vs. Meetings: What is the Difference?

Understanding the distinction between a Teams call and a Teams meeting is crucial for effective communication within an organization. Both have unique features and functionalities that cater to different needs. Let’s dive into the specifics of Teams calls and meetings.

Easily Combine Ranges with VSTACK and HSTACK in Excel

VSTACK and HSTACK are two great functions in Excel. We will use them to combine ranges.

Copilot in Excel: Conditional Formatting with Complex Functions

Copilot in Excel now supports Conditional Formatting with Formulas. This is an awesome feature. For example, Copilot can highlight an entire row where the State is Georgia and the city is Atlanta.

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.

Outlook - Manage your Inbox using Ignore, Archive, Block, Delete, and Clean Up

Your inbox in Microsoft Outlook can become overwhelming to manage. The average office worker receives around 121 emails every workday. Microsoft Outlook has some easy commands located on the Home tab to help manage your inbox.

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 certified Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. He runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events every year, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the Support Staff 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.