Monthly Mortgage Payments
Data Story Question:
As interest rates and home prices change, how have monthly payments changed over time for the average home with a 30 year fixed mortgage?
Answer:
Create this Graph
Start with an assumed home value of $119,000 on January 1 2000. This is an assumption. You should question all assumptions because they can impact the outcome of the findings. According to this article by CNBC, the median house value in 2000 was $119,000. Using the worksheet below, you should be able to recreate this graph with any value you choose
Download the Case-Shiller 20 City Composite Index data from 2000 till today
Calculate the percentage change in the index (provided in dataset)
Using the percentage change, I calculate the change in home values over time
Download the average 30 year fixed mortgage rate since 1/1/2000
Using the PMT function in Excel, calculate the payment using the interest rate, a loan amount equal to 80% of the average home price. This is only principle and interest payment. Differences in mortgage insurance, taxes, home insurance, or HOA fees are not considered. Here is the equation I used.
=-PMT(E2/12/100,12*30,B2*0.8,0,0)
Plotted the payment across time in the graph above
Caution: This does not take into consideration changes in CPI or changes in income across time. Home affordability is a function of the payment and income. A better measure might be payment to income ratio across time. I will add that to the list of graphs!
