Loan Amortization with Microsoft Excel | TVMCalcs.com - Free Printable

Problem Description:

The task involves analyzing a loan amortization schedule. The provided table contains details about a loan, including the principal amount, loan term, interest rate, payment frequency, and monthly payments. The goal is to understand how the loan is being repaid over time, breaking down each payment into interest and principal components.

Given Data:

1. Original Principal: $200,000
2. Loan Term (Years): 30 years
3. Annual Interest Rate: 6.75%
4. Payments per Year: 12 (monthly payments)
5. Monthly Payment: $1,297.20

The table also shows the breakdown of each payment into interest and principal for the first six months.

Objective:

- Verify the correctness of the amortization schedule.
- Explain how the interest and principal portions of each payment are calculated.
- Ensure the balance is correctly updated after each payment.

---

Solution Approach:

#### Step 1: Understanding the Amortization Formula
For a fixed-rate loan, the monthly payment \( P \) can be calculated using the formula:
\[
P = \frac{r \cdot P_0}{1 - (1 + r)^{-n}}
\]
where:
- \( P_0 \) is the original principal ($200,000).
- \( r \) is the monthly interest rate (\( \frac{6.75\%}{12} = 0.005625 \)).
- \( n \) is the total number of payments (\( 30 \times 12 = 360 \)).

However, since the monthly payment is already given as $1,297.20, we will use this value to verify the amortization schedule.

#### Step 2: Breakdown of Each Payment
Each monthly payment is split into two parts:
1. Interest Portion: Calculated as the remaining balance multiplied by the monthly interest rate.
2. Principal Portion: The remainder of the payment after subtracting the interest portion.

The new balance is then updated by subtracting the principal portion from the previous balance.

#### Step 3: Verifying the First Few Months

##### Month 0:
- Balance: $200,000 (initial principal).

##### Month 1:
- Interest: \( 200,000 \times 0.005625 = 1,125.00 \)
- Principal: \( 1,297.20 - 1,125.00 = 172.20 \)
- New Balance: \( 200,000 - 172.20 = 198,827.80 \)

##### Month 2:
- Interest: \( 198,827.80 \times 0.005625 = 1,124.03 \)
- Principal: \( 1,297.20 - 1,124.03 = 173.17 \)
- New Balance: \( 198,827.80 - 173.17 = 198,654.63 \)

##### Month 3:
- Interest: \( 198,654.63 \times 0.005625 = 1,123.06 \)
- Principal: \( 1,297.20 - 1,123.06 = 174.14 \)
- New Balance: \( 198,654.63 - 174.14 = 198,480.49 \)

##### Month 4:
- Interest: \( 198,480.49 \times 0.005625 = 1,122.08 \)
- Principal: \( 1,297.20 - 1,122.08 = 175.12 \)
- New Balance: \( 198,480.49 - 175.12 = 198,305.37 \)

##### Month 5:
- Interest: \( 198,305.37 \times 0.005625 = 1,121.09 \)
- Principal: \( 1,297.20 - 1,121.09 = 176.11 \)
- New Balance: \( 198,305.37 - 176.11 = 198,129.26 \)

##### Month 6:
- Interest: \( 198,129.26 \times 0.005625 = 1,120.10 \)
- Principal: \( 1,297.20 - 1,120.10 = 177.10 \)
- New Balance: \( 198,129.26 - 177.10 = 197,952.16 \)

---

Step 4: Comparing with the Provided Table

The calculations above match the values in the provided table, confirming the accuracy of the amortization schedule.

---

Final Answer:

The amortization schedule is correct. Each payment is accurately split into interest and principal, and the balance is updated accordingly. The process ensures that the loan is fully repaid over the 30-year term.

\boxed{\text{The amortization schedule is verified and correct.}}