Analyze the average interest rate over a 20-year period and

predict the future interest rate using a linear trend model.

Part A: Construct a Time Series Plot

Step 1: Install and Load Required Libraries

# install.packages("ggplot2")
# install.packages("readxl")
library(readxl) # allows us to import Excel files
library(ggplot2) # used for creating time series plots

Step 2: Load the data

df <- read_excel("Mortgage.xlsx")

Step 3: # Descriptive statistics

summary(df)

##       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

Interpretation: On average, the interest rate across the dataset is 5.08%.

###Step 3: Construct a time series plot

ggplot(df, aes(x = Period, y = Interest_Rate)) +
  geom_line(color = "blue") +
  geom_point() +
  xlab("Period (Years)") +
  ylab("Interest Rate (%)") +
  ggtitle("Time Series Plot of 30-Year Fixed-Rate Mortgage Interest Rates")

Interpretation: The time series plot shows a decreasing trend in interest rates over the 24-year period.

Part B: Develop the Linear Trend Equation.

Step 1: Build the Linear Trend Model

model <- lm(Interest_Rate ~ Period, data = df)
summary(model)

## 
## Call:
## lm(formula = Interest_Rate ~ Period, data = df)
## 
## 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

Interpretation:
Result - estimated linear trend equation: 6.7 - 0.13 * Period OR  
T_hat = 6.7 - 0.13 * t  
The R-squared value is 0.45 (Moderately fits the data).  
The overall model is significant as p-value is 0.0004 (p-value < 0.05).

Step 2-5: Calculate Fitted Values, Residuals, MSE & MAPE

df$Predicted_Interest_Rate <- predict(model)  
df$Residuals <- df$Interest_Rate - df$Predicted_Interest_Rate
mse <- mean(df$Residuals^2)
cat("Mean Squared Error (MSE):", mse, "\n")

## Mean Squared Error (MSE): 0.989475

df$percentage_error <- abs(df$Residuals / df$Interest_Rate) * 100
mape <- mean(df$percentage_error, na.rm = TRUE)
cat("Mean Absolute Percentage Error (MAPE):", mape, "%\n")

## Mean Absolute Percentage Error (MAPE): 15.79088 %

Interpretation:
The MSE of 0.9895 indicates low variance in the residuals,
while the MAPE of 15.79% reflects a moderate average forecasting error in percentage terms.

Part C: Forecast Interest Rate for Period 25 (2024)

Step 1: Forecast Using the Linear Trend Equation

forecast_period_25 <- predict(model, newdata = data.frame(Period = 25))
forecast_period_25

##        1 
## 3.472942

Interpretation: Using the linear trend equation, the forecasted interest rate for 2024 (Period 25)
is approximately 3.47%.