Money Lent Tracker in Excel: Simple Interest by Days, Total Due and Overdue Loans

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: money-lent-simple-interest-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

Ravindra's loans on 17 October 2026 (made-up values agreed between friends):

₹1,85,000 is lent out and ₹9,393.97 of interest has built up. All four loans together add ₹38.90 of interest every day. If Shahrukh repays on his due date, the interest will be exactly ₹9,000 (one full year at 9%).

2. Build it step by step

Step 1. The loans

1. In A5:J5 type Borrower, Amount, Given on, Interest % per year, Due on, Days so far, Interest so far, Total to get back, Days past due, Status.
2. In B3 type the as-of date (=TODAY() in your copy).

Step 2. Days so far

1. In F6 type =$B$3-C6: 199 days for Amir. Dates are numbers, so subtracting gives days (date functions).

Step 3. Simple interest

1. In G6 type =B6*D6*F6/365: ₹50,000 × 8% × 199 ÷ 365 = ₹2,180.82.
2. This is simple interest: interest is worked out on the amount lent only, never on earlier interest.
3. In H6 type =B6+G6.

Step 4. Overdue

1. In I6 type =MAX(0,$B$3-E6): 16 days for Amir, 0 for loans not yet due.
2. In J6 type =IF(I6>0,"Overdue",IF(E6-$B$3<=30,"Due in 30 days","Running")) (nested IF).

Step 5. Totals and summary

1. In B10, G10 and H10 use =SUM(...) of rows 6 to 9.
2. In M8 type =SUMIF(J6:J9,"Overdue",H6:H9) for the overdue amount (SUMIF).
3. In M9 type =SUMPRODUCT(B6:B9,D6:D9)/365: ₹38.90 of interest per day (SUMPRODUCT).

Step 6. Colours

1. Select A6:J9 > New Rule > Use a formula =$J6="Overdue" red and =$J6="Due in 30 days" amber (formula rules).

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. Typing 8 instead of 8%. With 8 the interest is 100 times too high. Type 8% or 0.08.
2. Dividing by 12 instead of 365. The formula uses days, so divide a yearly rate by 365.
3. Negative days past due. $B$3-E6 is negative for loans not yet due. MAX(0, …) shows 0.
4. Mixing simple and compound interest. This sheet is simple interest. If you agreed on compounding, the numbers will differ.
5. No written agreement. The sheet only does the arithmetic. Write down the amount, rate and dates and both sign it.

5. More real examples

Example 1: Partial repayments. Add a Repaid column and work out interest on the remaining amount from the repayment date.

Example 2: Interest-free family loans. Type 0%; the sheet still tracks days and overdue status.

Example 3: Money you borrowed. Use the same sheet for loans you took from someone, to know exactly what to return.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate simple interest by days in Excel?

=amount*rate*days/365, with days = today − date given.

What is the difference between simple and compound interest?

Simple interest is on the original amount only; compound interest also earns interest on earlier interest.

How do I count days between two dates?

Subtract them: =B3-C6. Both must be real dates.

How do I show only positive overdue days?

Use =MAX(0,today-due date).

How much interest builds up per day?

Amount × yearly rate ÷ 365. For all loans: =SUMPRODUCT(amounts,rates)/365.

7. Quick quiz

Ten questions on this page. Sign in to save your score.