Simple interest is charged only on the original amount: =Principal*Rate*Years. Compound interest is charged on the amount plus the interest already added: =Principal*(1+Rate)^Years - Principal. For ₹2,00,000 at 12% a year, the two are equal after year one, and by year five simple interest is ₹1,20,000 while compound interest is ₹1,52,468.
Setting up the two columns
Say a cousin borrows ₹2,00,000 from you and you both agree 12% a year. Put the inputs at the top and the years below.
- B1 Principal: 200000.
- B2 Yearly rate: 12%.
- Row 4 headings: A Year, B Simple interest, C Compound interest, D Gap.
- A5 to A9: 1 to 5.
- B5: =$B$1*$B$2*A5, copied down.
- C5: =$B$1*(1+$B$2)^A5-$B$1, copied down.
- D5: =C5-B5, copied down.
What does the gap look like?
| Year | Simple interest | Compound interest | Gap |
|---|---|---|---|
| 1 | ₹24,000 | ₹24,000 | ₹0 |
| 2 | ₹48,000 | ₹50,880 | ₹2,880 |
| 3 | ₹72,000 | ₹80,986 | ₹8,986 |
| 4 | ₹96,000 | ₹1,14,704 | ₹18,704 |
| 5 | ₹1,20,000 | ₹1,52,468 | ₹32,468 |
The gap is zero at the end of year one and then widens faster each year. Stretch the table to 10 years and simple interest reaches ₹2,40,000 against ₹4,21,170 compounded. Time is what separates the two, so the longer an informal loan runs without being settled, the more the wording matters.
How can you check one row by hand?
Take year 2. Compound: 2,00,000 x 1.12 = 2,24,000 after year one, then 2,24,000 x 1.12 = 2,50,880, so the interest is 50,880. Simple: 2,00,000 x 12% x 2 = 48,000. The gap is 2,880, which is exactly the interest-on-interest earned in year two (12% of the 24,000 added in year one). If your sheet shows the same three numbers, the formulas are right and you can trust the later rows.
What does 2 rupees per hundred per month mean?
Informal loans are often quoted per month, such as ₹2 per ₹100 a month. That is 2% a month, which is 24% a year as simple interest. If the interest is added to the loan every month and itself earns interest, the yearly figure is higher: =(1+2%)^12-1 gives about 26.8%. Before any money moves, agree which of these is meant, and whether unpaid interest gets added to the amount.
What if repayments happen in between?
Part-payments change the balance interest is charged on. Add a row for each payment with its date and amount, reduce the outstanding amount, and charge interest on what is left. A simple version keeps a running balance column: =previous balance + interest for the period - payment. For uneven dates, the XIRR function in a separate sheet reads the effective rate back from the actual flows.
Which one should the agreement use?
Between relatives, simple interest is the easier to explain and to track, and it does not surprise anyone at the end. Compounding is how most savings products and card dues work, so people expect it there. Neither is right for every case; what matters is that both sides read the same sentence the same way. Show them this table before the money moves and the conversation about interest stays about numbers, not about who remembers what.
Quick checks
- Year 1 simple and compound must match; if they do not, a bracket is misplaced.
- The rate cell is a percentage, not 12.
- Compounding in the formula is yearly; for monthly compounding use =$B$1*(1+$B$2/12)^(12*A5)-$B$1.
- The 12% here is only an example; nothing in the sheet says what rate is right for a family loan.
A loan to a relative is also a transaction worth recording. If you pay or receive it through a bank account, importing that statement into SpendixAI and adding a category of your own for lending keeps it apart from everyday spending.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


