Step-by-step guide: Creating a loan amortization schedule for your home loan

If you’ve ever taken a housing loan, you know that monthly payments aren’t just random numbers. Behind every installment is a calculation that tells you exactly how much of your money goes toward interest and how much reduces your loan principal. That’s where an amortization schedule comes in.

In this guide, I’ll walk you through how to create a loan amortization schedule step by step, whether you’re working with Excel, Google Sheets, or just pen and paper.

What Is a Loan Amortization Schedule?

An amortization schedule is a table that breaks down each monthly installment of your home loan into two parts:

• Interest portion – the cost of borrowing money
• Principal portion – the amount that actually reduces your outstanding balance

This schedule helps you see how much of your payment is interest-heavy in the early years and how it gradually shifts toward principal repayment later.

Step 1: Gather Your Loan Details

To start building your amortization table, you’ll need:

• Loan amount (principal)
• Annual interest rate
• Loan tenure (number of years/months)
• Repayment type (most home loans in Bangladesh use reducing balance)

For example:

• Principal: BDT 50,00,000
• Interest rate: 9% annually
• Tenure: 20 years (240 months)

Step 2: Calculate Your Monthly Installment (EMI)

The standard EMI formula is: EMI=P×r×(1+r)n(1+r)n−1EMI = \frac{P \times r \times (1+r)^n}{(1+r)^n – 1}

Where:

• PP = Loan amount
• rr = Monthly interest rate (annual rate ÷ 12)
• nn = Number of months

For our example:

• P=50,00,000P = 50,00,000
• r=0.75%r = 0.75\% (0.0075 in decimal)
• n=240n = 240

Result: EMI ≈ BDT 44,986

💡 Quick hack: In Excel or Google Sheets, use:

=PMT(rate, nper, -pv)

Step 3: Structure Your Amortization Table

Your schedule should include these columns:

1. Month
2. Opening Balance
3. EMI (same every month)
4. Interest = Opening Balance × Monthly Rate
5. Principal = EMI – Interest
6. Closing Balance = Opening Balance – Principal

Example (First 2 Months):

Month	Opening Balance	EMI	Interest	Principal	Closing Balance
1	50,00,000	44,986	37,500	7,486	49,92,514
2	49,92,514	44,986	37,444	7,542	49,84,972

Repeat this calculation until the balance reaches zero at month 240.

Step 4: Visualize Your Loan Progress

Creating a chart helps you understand repayment better:

• Line graph of outstanding balance declining
• Bar chart of principal vs. interest portions each year

These visuals make it clear why the first years of your housing loan feel “interest heavy.”

Step 5: Download or Automate with a Template

Instead of calculating manually, you can build your own template with formulas:

• Use IPMT in Excel to calculate the interest portion: =IPMT(rate, period, nper, -pv)
• Use PPMT for principal portion: =PPMT(rate, period, nper, -pv)

For convenience, you can also try our installment loan calculator or interest calculator to generate a full schedule instantly.

What If You Make Extra Payments?

Adding extra money toward your loan—even occasionally—shortens your tenure and reduces total interest. For example, paying an extra BDT 20,000 every year can save you lakhs over time.

You can model this easily by adjusting the principal column in your schedule. Try it with a housing loan repayment calculator to see the impact.

FAQs on Loan Amortization

1. Why do banks use amortization schedules?
To standardize repayment and ensure borrowers understand how loans are paid down.

2. Can I make my own amortization schedule without Excel?
Yes—using formulas, a calculator, and a simple table.

3. How do extra payments affect amortization?
They reduce the principal faster, which lowers total interest.

4. Are flat-rate loans different?
Yes. In flat-rate loans, interest is charged on the full principal throughout the term. See our guide on reducing balance vs flat interest loans for details.

5. Where can I get a free template?
Most banks provide one, but Excel/Google Sheets with PMT, IPMT, and PPMT functions is usually enough.