BYU Student Author: @Keanu_Gauthier
Reviewers: @Kylie_Larsen17, @Jason_Nguyen
Estimated Time to Solve: 30 Minutes
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
Overview
Congratulations! You’ve just landed a job as a new audit associate at EY in Salt Lake City, earning $80,000 per year. You’re excited to purchase your first home, preferably near your workplace. However, as a first-time homebuyer, you need to analyze whether you can afford the mortgage payments and how much extra you can pay to reduce your loan term.
Your task is to research home prices and mortgage rates, then use the mortgage calculator Excel file provided to determine your monthly payment and potential savings with extra payments. Assume that your income taxes are 25% and your monthly total expenses are 30% of your after-tax income.
Instructions
1 - Calculate Your Monthly Disposable Income
Calculate how much you have left after taxes (25% of income taxes) and monthly expenses (30% of after-taxes income).
2 - Find a Home Near EY in Salt Lake City
Go online and search for a house that you like. You can use these websites to look for properties (Zillow, Realtor, Redfin). Open the mortgage calculator excel file, locate the cells you can modify (the yellow cells) and plug-in the price of the house in the cell “Home Price”.
3 - Find Current Mortgage Rates in Utah
Research the current mortgage interest rates for a 30-year fixed loan in Utah using the website Bankrate. You can type in Google, “Bankrate, mortgage rate in Utah”. Use the first rate under the column “Rate”. Plug-in the number you found in the cell “Interest Rate”.
4 - Calculate Your Mortgage Payment
Now that you have your house price and mortgage rate plugged in the mortgage calculator, plug-in the cells “Down Payment Percentage” and “Loan Period in Yrs” to 30 years. If the monthly mortgage payment exceeds your disposable income, review step 2.
- Create a separate sheet and create a table with the following line names:
- Monthly mortgage payment
- Extra Payment
- Total Interest paid (calculated by using the interest column in the mortgage calculator)
- Total payment towards the principal after 5 years (60 months, use both the “Payments” and “Extra “ column)
- Total payment towards the principal after 10 years (120 months, use both the “Payments” and “Extra “ column)
- Total payment towards the principal after 15 years (180 months, use both the “Payments” and “Extra “ column)
Fill up the table using the mortgage calculator sheet and call this column, “No extra payment”.
5 - Analyze Extra Payments
In the mortgage table sheet, plug-in an amount in the “Extra Payments” cell that doesn’t make your monthly total payment (monthly payment + extra payment) exceed your disposable income. The “Extra Payments” cell represents an additional amount added to your regular monthly mortgage payment, allowing you to pay off your loan faster and reduce the total interest paid over time. The goal here is to analyze the impact of having extra payments each month on your equity owned (payments towards the principal of the house) and compare it with the scenario with no extra payments. In the new sheet you previously created, record the new numbers under a new column named “With extra payment”.
- Monthly mortgage payment
- Extra Payment
- Total Interest paid (calculated by using the interest column in the mortgage calculator)
- Total payment towards the principal after 5 years (60 months, use both the “Payments” and “Extra “ column)
- Total payment towards the principal after 10 years (120 months, use both the “Payments” and “Extra “ column)
- Total payment towards the principal after 15 years (180 months, use both the “Payments” and “Extra “ column).
6 - Answer the following questions as an answer to this challenge:
- How much was the house you picked?
- How much was the mortgage rate and the monthly payment?
- How much total interest would you be paying by the end of the 30-year period with no extra payment?
- How much total payments towards the principal of your house would you pay after 5 years, 10 years, 15 years?
- Did you have enough in your budget to pay extras each month? How much did you plug-in?
- How much total interest did you save from paying extra each month?
- How many months early did you pay up your loan?
- Compare your payments to the principal of your house with and without paying extra after 5 years, 10 years, 15 years.
- What advantages or disadvantages is there in paying extra money each month?
- When your turn will come to buy your house, would you consider paying extra payments?
Data Files