# Load the necessary libraries for data manipulation and visualization
library(readxl) # For reading Excel files
library(ggplot2) # For creating visualizations
# Load the data from an Excel file
mortgage_data <- read_excel("Mortgage.xlsx") # Prompt user to choose the file
mortgage_data # View the dataset
## # A tibble: 24 × 3
## Year Period Interest_Rate
## <dttm> <dbl> <dbl>
## 1 2000-01-01 00:00:00 1 8.05
## 2 2001-01-01 00:00:00 2 6.97
## 3 2002-01-01 00:00:00 3 6.54
## 4 2003-01-01 00:00:00 4 5.83
## 5 2004-01-01 00:00:00 5 5.84
## 6 2005-01-01 00:00:00 6 5.87
## 7 2006-01-01 00:00:00 7 6.41
## 8 2007-01-01 00:00:00 8 6.34
## 9 2008-01-01 00:00:00 9 6.03
## 10 2009-01-01 00:00:00 10 5.04
## # ℹ 14 more rows
summary(mortgage_data) # Get a summary of the data (mean, median, etc.)
## Year Period Interest_Rate
## Min. :2000-01-01 00:00:00 Min. : 1.00 Min. :2.958
## 1st Qu.:2005-10-01 18:00:00 1st Qu.: 6.75 1st Qu.:3.966
## Median :2011-07-02 12:00:00 Median :12.50 Median :4.863
## Mean :2011-07-02 18:00:00 Mean :12.50 Mean :5.084
## 3rd Qu.:2017-04-02 06:00:00 3rd Qu.:18.25 3rd Qu.:6.105
## Max. :2023-01-01 00:00:00 Max. :24.00 Max. :8.053
# Convert 'Period' column to numeric type
mortgage_data$Period <- as.numeric(mortgage_data$Period)
# Convert 'Interest_Rate' column to numeric type
mortgage_data$Interest_Rate <- as.numeric(mortgage_data$Interest_Rate)
# Create a time series plot using ggplot2
ggplot(mortgage_data, aes(x = Period, y = Interest_Rate)) +
geom_line(color = "blue") + # Add a blue line to connect points
geom_point(color = "red") + # Add red points for data points
xlab("Period") + # Label for the x-axis
ylab("Interest Rate") + # Label for the y-axis
ggtitle("Time Series Plot of 30-Year Fixed-Rate Mortgage") # Add title to the plot
# Interpretation: The data shows a trend pattern.
# Fit a linear regression model to estimate the trend
model <- lm(Interest_Rate ~ Period, data = mortgage_data)
summary(model) # Display the results of the regression model
##
## Call:
## lm(formula = Interest_Rate ~ Period, data = mortgage_data)
##
## Residuals:
## Min 1Q Median 3Q Max
## -1.3622 -0.7212 -0.2823 0.5015 3.1847
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 6.69541 0.43776 15.295 3.32e-13 ***
## Period -0.12890 0.03064 -4.207 0.000364 ***
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 1.039 on 22 degrees of freedom
## Multiple R-squared: 0.4459, Adjusted R-squared: 0.4207
## F-statistic: 17.7 on 1 and 22 DF, p-value: 0.0003637
# Linear trend equation:
# From the model summary, the equation is of the form:
# Interest_Rate = 6.70 - 0.13 * Period
# Add predicted Interest Rates to the dataset
mortgage_data$predicted_Interest_rate <- predict(model)
# Calculate residuals (difference between actual and predicted Interest Rates)
mortgage_data$residuals <- mortgage_data$Interest_Rate - mortgage_data$predicted_Interest_rate
# Calculate the Mean Squared Error (MSE)
mse <- mean(mortgage_data$residuals^2)
cat("Mean Squared Error (MSE):", mse, "\n") # Print the MSE
## Mean Squared Error (MSE): 0.989475
# Calculate the Mean Absolute Percentage Error (MAPE)
mortgage_data$percentage_error <- abs(mortgage_data$residuals / mortgage_data$Interest_Rate) * 100
mape <- mean(mortgage_data$percentage_error)
cat("Mean Absolute Percentage Error (MAPE):", mape, "%\n") # Print the MAPE
## Mean Absolute Percentage Error (MAPE): 15.79088 %
# Use the linear trend model to predict the Interest Rate for Period 25
forecast_period_25 <- predict(model, newdata = data.frame(Period = 25))
cat("The forecasted average interest rate for Period 25 (2024) is:", forecast_period_25, "\n")
## The forecasted average interest rate for Period 25 (2024) is: 3.472942