Understanding Wendy’s Housing Loan Repayment Scenario: Calculations and Payoff Strategy

Introduction

In this financial scenario, Wendy has accumulated a deposit of $300,000 and intends to use it as part of a down payment to purchase a home worth $900,000. To bridge the gap, she plans to take out a housing loan. The loan will be repaid in equal quarterly installments over a 15-year term. The bank has quoted an annual nominal interest rate of 4.2% compounded quarterly. This essay will delve into three key aspects of Wendy’s loan journey: determining the quarterly repayment amount, calculating the outstanding amount at the time of early payoff, and creating an Excel-based repayment schedule at the inception of the loan.

(i) Quarterly Repayment Calculation: To find out the quarterly repayment amount, we can use the formula for calculating the equal installment amount for a loan:

���=�×�×(1+�)�(1+�)�−1EMI=(1+r)n−1P×r×(1+r)n​

Where:

• $EMI$ is the equated monthly installment (quarterly in this case)
• $P$ is the principal amount (loan amount)
• $r$ is the quarterly interest rate
• $n$ is the total number of payments (number of quarters)

Given:

• $P=\$900,000-\$300,000=\$600,000$
• Annual nominal interest rate $=4.2\%$
• Quarterly interest rate =4.2%4=1.05%=44.2%​=1.05%
• Total number of payments $=15\times 4=60$ quarters

Substituting these values into the formula gives: ���=$600,000×0.0105×(1+0.0105)60(1+0.0105)60−1≈$5,395.46EMI=(1+0.0105)60−1$600,000×0.0105×(1+0.0105)60​≈$5,395.46

Therefore, Wendy’s quarterly repayment amount is approximately $5,395.46.

(ii) Outstanding Amount at Early Payoff: To determine the outstanding amount at the time of early payoff, we need to calculate the remaining balance of the loan after 5 years (20 quarters). One way to do this is by using the formula for the outstanding balance of a loan:

��������� �������=�×(1+�)�−(1+�)�(1+�)�−1RemainingBalance=P×(1+r)n−1(1+r)n−(1+r)t​

Where:

• $P$ is the principal amount (loan amount)
• $r$ is the quarterly interest rate
• $n$ is the total number of payments (number of quarters)
• $t$ is the number of payments already made (number of quarters)

Given:

• $P=\$600,000$
• Quarterly interest rate $=1.05\%$
• Total number of payments $n=60$ quarters
• Number of payments made $t=20$ quarters

Substituting these values into the formula gives: ��������� �������=$600,000×(1+0.0105)60−(1+0.0105)20(1+0.0105)60−1≈$383,229.82RemainingBalance=$600,000×(1+0.0105)60−1(1+0.0105)60−(1+0.0105)20​≈$383,229.82

So, the amount owed at the time of early payoff is approximately $383,229.82.

Repayment Schedule Using Excel: To create a repayment schedule using Excel, you can follow these steps:

Open Excel and create a table with columns: Payment Number, Beginning Balance, Payment Amount, Interest Payment, Principal Payment, Ending Balance.

Populate the Payment Number column from 1 to 60.

Calculate the Beginning Balance for each payment using the formula $BeginningBalance=PreviousEndingBalance$.

Calculate the Interest Payment for each payment using the formula $InterestPayment=BeginningBalance\times QuarterlyInterestRate$.

Calculate the Payment Amount using the given EMI of $5,395.46.

Calculate the Principal Payment using the formula $PrincipalPayment=PaymentAmount-InterestPayment$.

Calculate the Ending Balance using the formula $EndingBalance=BeginningBalance-PrincipalPayment$.

Repeat steps 3-7 for all 60 payments.

Conclusion

In this financial analysis, we’ve explored Wendy’s housing loan journey, including the calculation of the quarterly repayment amount, determination of the outstanding amount at the time of early payoff, and the creation of a repayment schedule using Excel. Understanding these aspects empowers Wendy to make informed decisions about her loan and financial strategy, ensuring that she manages her finances effectively over the loan term.