Loan Interest Calculator in Excel – Multiple Payments / Receipts (Free)

Direct download - Free

Loan Interest Calculator in Excel – Multiple Payments (Free)

Did you lend money and get it back in parts? Or take a loan and repay it in instalments on different dates? Working out the interest by hand is slow and easy to get wrong. Every payment falls on a different date, leap years change the day count, and interest is usually added to the balance once a year.

This Excel workbook does it for you. Enter your receipts and payments with their dates and the interest rate. It builds a bank-style statement and shows the final amount, marked TO RECEIVE or TO PAY.

How it works

1. Set the basics. On the Input sheet, enter the date up to which interest should be calculated and the yearly rate.
2. Enter transactions in date order. Put each one in Receipt (money you received) or Payment (money you paid). Use one column per row. You can enter up to 100 transactions.
3. The Statement sheet builds itself. It lists every transaction, every yearly interest date and the final date, with the days, interest and balance for each.
4. Read the result. You get the final amount, TO RECEIVE or TO PAY, the principal outstanding and the total interest.

[Insert Input sheet screenshot here]

The rules it follows

• Interest = balance × rate × days ÷ days in that year (365 or 366).
• Each receipt or payment changes the balance from its own date, so interest changes from that day.
• Interest is added to the balance once a year (annual rest), on the anniversary of the first transaction date. It is not compounded inside the year.
• A first transaction on a month end keeps its yearly dates on month ends, including 28/29 February.

Worked example

You paid ₹50,000 to a friend on 24-Mar-2022. On 23-Jan-2023 you received ₹25,000 back. The rate is 15% a year, and you want the position on 25-Apr-2026.

Date	What happened	Days	Interest	Total owed to you (₹)
24-Mar-2022	You paid 50,000	–	–	50,000.00
23-Jan-2023	You received 25,000	305	6,267.12	31,267.12
24-Mar-2023	Yearly interest added	60	616.44	31,883.56
24-Mar-2024	Yearly interest added	366	4,782.53	36,666.10
24-Mar-2025	Yearly interest added	365	5,499.91	42,166.01
24-Mar-2026	Yearly interest added	365	6,324.90	48,490.91
25-Apr-2026	Final date	32	637.69	49,128.60

The first interest amount is ₹50,000 × 15% × 305 ÷ 365 = ₹6,267.12. The 2024 period uses 366 days because it includes 29 February.

Result: TO RECEIVE ₹49,128.60, made up of ₹25,000 principal and ₹24,128.60 interest. The sheet shows amounts in your favour with a minus sign.

Advantages

• Many dated payments in one sheet. Handles up to 100 receipts and payments.
• Every step is visible. The statement reads like a bank ledger, so both sides can check each line.
• Actual-day accuracy. Uses real days and real year length, including leap years.
• A clear answer. It says TO RECEIVE or TO PAY, so you don't have to interpret signs.
• No macros. It is plain formulas in an .xlsx file.
• Built-in data check. It warns you if dates are out of order, a row has both Receipt and Payment, or an amount has no date.
• Tested. The workbook was checked against an independent day-by-day calculation in 18 test cases, including leap years and month-end dates.

Running balance or transaction-wise?

This workbook uses the running-balance method: a payment reduces the balance straight away, and interest is added once a year. That is how most loan statements work. My other workbook, The two can differ slightly, so use the method your agreement states.

Things to know

• One interest rate applies to the whole period.
• Enter transactions in date order, with no blank rows between them.
• Interest is added once a year. If your agreement compounds monthly or quarterly, this is not the right method.