How to track multiple bank loans with different interest rates in Excel?

[Andrewalwin]

I am trying to build an Excel sheet to track multiple loans taken from different banks. Each loan has different interest rates, repayment schedules, and maturity dates.

What would be the best way to structure this in Excel so I can easily track repayments, outstanding balances, and upcoming payment dates?

Should I use formulas like PMT/IPMT or is there a better financial model approach?

 

[shamimarita]

Hello Andrewalwin,

Tracking multiple loans (different rates, schedules, maturity dates) for repayments, balances, and due dates is straightforward with the right setup. No VBA needed, use PMT, IPMT, PPMT, CUMIPMT, etc., for calculations.

Check out our free Multiple Loan Amortization Schedule Excel Template. It's designed exactly for this: handling several loans at once, with inputs for principal, rate, term per loan, and it generates amortization details, totals, and summaries.

• Download the .xlsx file there and follow the included instructions (Loan Input sheet for entering your bank loans' details).

It supports different interest rates per loan and gives you a clear view of outstanding balances over time.

Quick alternatives if needed:

• For variable rates on one loan: Loan Amortization Schedule with a Variable Interest Rate in Excel
• Build your own: Use separate sections/sheets per loan with PMT for the monthly payment:

=PMT(annual_rate/12, term_years*12, -principal)
Then build amortization tables (period, beginning balance, interest = balance * rate/12, principal = payment - interest, ending = beginning - principal).

Dashboard: Sum balances with XLOOKUP or direct references, highlight due dates with conditional formatting (e.g., red if < TODAY()).

If your loans have irregular payments or moratoriums, other templates like irregular payments or moratorium ones might help too.