Amy is purchasing a two
-
bedroom apartment in Brisbane with an asking price of $
6
0
0
,
0
0
0
.
She makes a down payment of $
5
0
,
0
0
0
and finances the remaining amount through a mortgage fro

Question

Amy is purchasing a two
-
bedroom apartment in Brisbane with an asking price of $
6
0
0
,
0
0
0
.
 ﻿She makes a down payment of $
5
0
,
0
0
0
 ﻿and finances the remaining amount through a mortgage from CBA. The loan carries an annual interest rate of 
8
%
 ﻿and requires monthly repayments over the 
1
0
-
year term. 
(
i
)
 ﻿What is the monthly repayment required by this mortgage? 
(
ii
)
 ﻿Provide the repayment schedule using Excel that shows each month's opening balance, repayment, interest, principal, and closing balance in order.

(
iii
)
 ﻿In the repayment schedule, highlight in red the month when the principal component of the repayment first exceeds the interest component.

VIPSolutions · Accepted Answer

Let's go step by step for each part of your question.

---

### (i) **Monthly Repayment Calculation**

#### **Given:**
- **Purchase Price:** \$600,000  
- **Down Payment:** \$50,000  
- **Loan Amount:** \$600,000 - \$50,000 = **\$550,000**  
- **Annual Interest Rate:** 8% (compounded monthly)  
- **Monthly Interest Rate:** $ \frac{8\%}{12} = 0.6667\% = 0.0066667 $  
- **Loan Term:** 10 years = 120 months

#### **Formula for Monthly Repayment (PMT):**
$$
PMT = P \times \frac{r(1+r)^n}{(1+r)^n-1}
$$
Where:
- $ P $ = Principal = \$550,000  
- $ r $ = Monthly interest rate = 0.0066667  
- $ n $ = Number of payments = 120

Let's compute:

$$
PMT = 550,000 \times \frac{0.0066667(1+0.0066667)^{120}}{(1+0.0066667)^{120}-1}
$$

First, calculate $ (1 + 0.0066667)^{120} $:

$$
(1 + 0.0066667)^{120} \approx (1.0066667)^{120} \approx 2.2522
$$

Now, plug in values:

$$
PMT = 550,000 \times \frac{0.0066667 \times 2.2522}{2.2522 - 1}
$$

$$
= 550,000 \times \frac{0.0150147}{1.2522}
$$

$$
= 550,000 \times 0.011995
$$

$$
= \boxed{\$6,597.27}
$$

---

### (ii) **Repayment Schedule in Excel**

Create columns as follows:

| Month | Opening Balance | Repayment | Interest | Principal | Closing Balance |
|-------|----------------|-----------|----------|-----------|----------------|
| 1     | \$550,000.00   | \$6,597.27| \$3,666.67| \$2,930.60| =B2-E2         |
| 2     | =F2            | \$6,597.27| =B3*0.0066667| =C3-D3 | =B3-E3         |
| ...   | ...            | ...       | ...      | ...       | ...            |

#### **Excel Formulas:**

Assume:
- Row 2 is Month 1,
- A2: 1,
- B2: \$550,000,
- C2: \$6,597.27,
- D2: =B2 * 0.0066667,
- E2: =C2 - D2,
- F2: =B2 - E2

Drag formulas down for all 120 months.

---

### (iii) **Highlight the Month When Principal Exceeds Interest**

In Excel:

- In column E (Principal), the first month where E > D (Principal > Interest).
- For conditional formatting:
  - Select the rows,
  - Use a rule like: `=E2>D2`
  - Set fill color to red.

**Alternatively, manually:**
- Month 1: Interest = \$3,666.67; Principal = \$2,930.60
- Each month, interest decreases, principal increases.
- Find first month where principal > interest.

#### **Quick Calculation:**

The cross-over point happens when the principal portion for the month is just greater than the interest portion.

Alternatively, you can use this formula to find the month $ m $:

$$
P_m = PMT \times m - \text{Total Interest Paid by month } m
$$
But with the schedule, it's easy to just scroll down.

**Typically, for a 10-year 8% loan, this happens around month 60.**

---

## **Summary Table (First 3 Months):**

| Month | Opening Balance | Repayment | Interest | Principal | Closing Balance |
|-------|----------------|-----------|----------|-----------|----------------|
| 1     | \$550,000.00   | \$6,597.27| \$3,666.67| \$2,930.60| \$547,069.40   |
| 2     | \$547,069.40   | \$6,597.27| \$3,647.13| \$2,950.14| \$544,119.26   |
| 3     | \$544,119.26   | \$6,597.27| \$3,627.46| \$2,969.81| \$541,149.45   |
| ...   | ...            | ...       | ...      | ...       | ...            |

---

### **How to Do This in Excel:**

1. **Enter data in Row 2:**
    - A2: 1
    - B2: 550000
    - C2: 6597.27
    - D2: =B2*0.0066667
    - E2: =C2-D2
    - F2: =B2-E2

2. **Row 3:**
    - A3: =A2+1
    - B3: =F2
    - C3: =C2
    - D3: =B3*0.0066667
    - E3: =C3-D3
    - F3: =B3-E3

3. **Drag down to Row 121 (Month 120)**

4. **Conditional Formatting:**  
    - Select from Row 2 onward.
    - Conditional Formatting > New Rule > Use formula:
      ```
      =E2>D2
      ```
    - Set format to red fill.

---

**If you want, I can generate the first 12 lines as an example. Let me know!**

---

## **Final Answers:**

### (i) **Monthly Repayment**
$$
\boxed{\$6,597.27}
$$

### (ii) **Excel Schedule**
- See table and Excel formulas above.

### (iii) **Highlight in Red**
- Use conditional formatting to highlight the first month where the principal exceeds the interest.

---

**If you want a .xlsx template, let me know!**

✨ VIPSolutions

Question:

Final Answer:

📢 Instructions:

Study Help, Fast Answers

✨ VIPSolutions

Question:

Final Answer:

📢 Instructions:

Related Answers

Study Help, Fast Answers