Question

Dave takes out a 23-year mortgage of 290000 dollars for his new house. Dave gets an interest rate of 14.4 percent compounded monthly. He agrees to make equal monthly payments, the first coming in one month. After making the 70th payment, Dave wants to buy a boat, so he wants to refinance his house to reduce his monthly payment by 400 dollars, and to get a better interest rate. In particular, he negotiates a new rate of 7.2 percent compounded monthly, and agrees to make equal monthly payments (each 400 dollars less than his original payments) for as long as necessary, followed by a single smaller payment. How large will Dave's final loan payment be?

Answer 1

First we will calculate the EMI amount paid by Dave

Tenure = 23*12 months (as interest compound monthly)

Rate = 14.4/12 per month (as interest compound monthly)

Loan amount = $290000

by using PMT function of Excel EMI will be $3614.34

Now after 70th Payment, he want to reduce the EMI by $400, so new EMI will be (3614.34 - 400) = $3214.34

Interest rate as per refinancing = 7.2%/12 per month (as interest compound monthly)

Remaining loan Amount ?

Tenure ?

First we will calculate the principal amount paid till 70th installment with the help of CUMPRINC function of the excel

So principal paid till 70th installment is $14607.47, so remaining principal will be $290000 - $14607.47 = $275392.53

So for calculation of new tenure

Loan = $275392.53

Rate = 7.2%/12 per month (as interest compound monthly)

EMI = $3214.34 (after reducing $400 in current EMI)

Now we will calculate loan tenure with the help of NPER function in excel

Note : please keep the value of principal in minus for calculation purpose

so the new tenure will be Approx 121 months of 10 years.

Answer 2

To find out the size of Dave's final loan payment, we need to calculate the remaining balance on his mortgage after making the 70th payment. Then, we can determine the additional payments required at the new interest rate to pay off the remaining balance. Let's break down the calculations step by step.

Step 1: Calculate the remaining balance after the 70th payment. Using the formula for the remaining balance on a mortgage, we can calculate the balance after the 70th payment. Let: P = Principal amount (original loan amount) r = Monthly interest rate n = Total number of payments

P = $290,000 r = 14.4% / 100 / 12 (convert to monthly interest rate) n = 23 years * 12 (convert to the number of months)

Using the formula for the remaining balance on a mortgage:

Remaining balance = P * [(1 + r)^n - (1 + r)^m] / [(1 + r)^n - 1]

where m is the number of payments made.

After the 70th payment, m = 70, and we can calculate the remaining balance.

Step 2: Calculate the new loan amount and the number of payments required at the new interest rate to cover the remaining balance.

New interest rate = 7.2% / 100 / 12 (convert to monthly interest rate) New monthly payment = Original monthly payment - $400 Remaining balance from Step 1 = Principal amount for the new loan

Using the formula for calculating the number of payments required to pay off a loan:

Number of payments = log(1 - (r * Loan amount) / Payment) / log(1 + r)

where Loan amount is the remaining balance, and Payment is the new monthly payment.

Step 3: Determine the size of the final loan payment. After calculating the number of payments required in Step 2, we can determine the size of the final loan payment.

Let's perform the calculations to find the final loan payment for Dave.