Jake is looking at refinancing his mortgages and paying off his credit cards at the same time. Currently, his debt situation is as follows:
First mortgage: 6%, 30 years due 20xx (15 years left) $269,238
Second mortgage: 8%, interest only due in 15 years $80,000
Credit cards: 21%, minimum payments made $12,000
Total refinanced: $361,238
Jake is trying to decide between:
a. 5.5% loan with a 15- or 30-year mortgage with 1 point
b. 6% loan with a 15- or 30-year mortgage with 0.5 points
c. 6.5% loan with a 15- or 30-year mortgage with no points
Calculate the monthly payment for all six mortgages and the interest that will be paid over the life of the mortgage to decide which is Jake’s best option and what amount he will save with this option. You know that Jake’s take-home pay is $8,332 per month and the new financing should not be more than 36 percent of that value.
Procedure/Steps:
1. Use the time value of money to calculate the mortgage payment on all loans.
2. Calculate the total interest expense of each new loan by using the amortization schedule in Microsoft Excel.
3. Calculate the total interest expense on his current loans for the credit cards; assume that he pays interest for only 15 years.
4. Use the time value of money to calculate which loan saves Jake the most money.
5. Compare the best alternative to how much his current loans cost.
Outcome:
• You should be able to use the time value of money to determine mortgage payments.
• You should be able to calculate the total interest expense of a mortgage loan.
• You should be able to compare old and new financing costs.
• You should be able to perform a mortgage loan amortization schedule in Excel.
• You should be able to compare interest savings on different types of loans.
Note: If you don’t have the amortization schedule in Excel, go to microsoft and download Loan amortization schedule. You can download the schedule from this site for free.
To calculate the monthly payment and total interest expense for each loan option, follow these steps:
Step 1: Calculate the monthly payment using the time value of money formula:
First, let's calculate the monthly payment for each loan option:
a. 5.5% loan with a 15-year mortgage with 1 point:
Loan amount = $361,238
Interest rate = 5.5%
Loan term = 15 years
You can use the formula for a fixed-rate mortgage payment:
P = L[c(1 + c)^n]/[(1 + c)^n - 1]
Where:
P = Monthly payment
L = Loan amount
c = Monthly interest rate (annual interest rate divided by 12)
n = Total number of monthly payments (loan term in years multiplied by 12)
c = 5.5% / 12 = 0.00458 (monthly interest rate)
n = 15 years * 12 = 180 (total number of monthly payments)
P = $361,238 * (0.00458 * (1 + 0.00458)^180) / ((1 + 0.00458)^180 - 1)
Repeat the above calculation for each loan option, varying the interest rate, loan term, and points accordingly.
b. 6% loan with a 15-year mortgage with 0.5 points
c. 6.5% loan with a 15-year mortgage with no points
a. 5.5% loan with a 30-year mortgage with 1 point
b. 6% loan with a 30-year mortgage with 0.5 points
c. 6.5% loan with a 30-year mortgage with no points
By performing the above calculations for each loan option, you will get the monthly payments.
Step 2: Calculate the total interest expense of each new loan using an amortization schedule in Excel:
Using Microsoft Excel, you can create an amortization schedule to calculate the total interest expense for each loan option. The amortization schedule will show the breakdown of monthly payments, principal payments, and interest payments over the life of the loan.
You can download a loan amortization schedule template from Microsoft's website or use any other amortization schedule tool available online.
Once you have the amortization schedule, input the loan amount, interest rate, and loan term for each loan option. The schedule will automatically calculate the monthly payment, total interest expense, and remaining balance for each month.
Step 3: Calculate the total interest expense on Jake's current loans for the credit cards:
Assuming Jake pays interest on the credit cards for only 15 years, you can calculate the total interest expense using the same formula as in Step 1:
P = L[c(1 + c)^n]/[(1 + c)^n - 1]
Where:
P = Monthly payment
L = Loan amount (credit card debt)
c = Monthly interest rate (21% divided by 12)
n = Total number of monthly payments (15 years multiplied by 12)
By plugging in the values for the credit card debt, interest rate, and loan term, you can calculate the monthly payment and total interest expense for the credit card debt.
Step 4: Use the time value of money to calculate which loan saves Jake the most money:
To determine which loan option saves Jake the most money, compare the total interest expense for each new loan option calculated in Step 2. The loan option with the lowest total interest expense will save Jake the most money.
Step 5: Compare the best alternative to how much his current loans cost:
Compare the total interest expense for the best loan option calculated in Step 4 with the total interest expense on Jake's current loans. The difference between the two amounts will represent the amount he will save with the best loan option.
By following these steps and performing the necessary calculations, you will be able to determine the best loan option for Jake and the amount he will save with that option.