4

Several years ago, I loaned money to a family member. They borrowed different amounts at irregular intervals. They are now beginning to repay, so I want to establish exactly how much they owe. I have a spreadsheet that has a row for each time they borrowed or repaid an amount. We agreed upon an interest rate. How can I calculate how much they owe at any time?

Thus far I wrote a script (couldn't figure out how to do it in Excel) to take the existing spreadsheet and make a new one that has columns for date, cumulative amount loaned, interest, and amount owed. It has a row for each day from the date of the first loan to today. This assumes that payments go entirely against principal, which I'm pretty sure is not typical, but I'm not sure how else to do it. Is there an easier way to do this, a web app perhaps? I want a fair solution for both parties.

EDIT: Figured out how to do it in Excel, so I posted my spreadsheet on Google Drive here (note the two tabs). Is this calculation fair?

Improve this question
3
  • I don't see a full transaction. 3 lines would do it. Starting sum, next line showing accrued interest and payment, then current balance. I saw both tabs.
    – JTP - Apologise to Monica ♦
    Commented Jul 5, 2014 at 17:51
  • @JoeTaxpayer check out the new 'Copy of events' tab. Is that what you had in mind? You should be able to edit if you want.
    – Craig W
    Commented Jul 5, 2014 at 18:15
  • Edited first few lines. The idea is to have the line only show when there's a payment, else, the date can be left to float, e.g. just enter today to see current balance.
    – JTP - Apologise to Monica ♦
    Commented Jul 5, 2014 at 19:30

3 Answers 3

Reset to default
3

Your sheet reflects daily compounding of interest, which may be fair as long as all parties agree.

For 5% per year compounded annually, your daily rate should be (1.05)^(1/365)-1. Many loans compound monthly, in which case your daily rate would be (1+.05/12)^(1/30)-1.

The impact is minimal. Daily compounding still only makes the annual yield about 5.13% rather than 5%. But a written loan would be clear on this.

Improve this answer
2
  • Just a note that with your formula, the daily rate for compounding monthly comes out higher than the daily rate for compounding daily, because a month is really ~30.4 days and not 30. Not sure if banks account for this or not.
    – Craig W
    Commented Jul 6, 2014 at 3:01
  • @CraigW: yes, I fudged a bit for simplicity. It all depends on the loan contract details. Some compound "30/360", or assuming all months are 30 days. Some compound "Actual/Actual", using the exact number of days to compute a rate. I'm pretty sure home mortgages are typically the former; credit card advances are typically the latter.
    – user14446
    Commented Jul 6, 2014 at 20:48
1

enter image description here

The first col shows dates. Simple enough. The second shows you what the date looks like if you set the cell to show a number format. So here, A2 = A1 just formatted differently. You can use regular math to calculate the elapsed days since last payment and interest accrued since then. Here, C2 = B2-B1, and you can see the .53 means it's past noon here. (Days elapsed)/365 * annual interest * prin balance is what you need. Please advise if that does it for you.

Improve this answer
3
  • The problem with this is failure to account for compounding periods during which no payment is made.
    – Burt_Harris
    Commented Nov 29, 2015 at 19:17
  • My answer was to help OPs issue figuring out dates. I didn't touch rates at all, let alone compounding.
    – JTP - Apologise to Monica ♦
    Commented Nov 29, 2015 at 20:15
  • Understood, but I read OP's question as about fair interest calculation, not dates.
    – Burt_Harris
    Commented Nov 29, 2015 at 20:33
1

I don't think you can use shortcuts when calculating compound interest on irregular payments, each period must be calculated separately, including those where no payment was made. Luckily, with a tool like Excel, this is simple but repetitive math.

In a similar situation, I calculated interest with monthly compounding using Excel like this. The formulas needed are pretty simple and shown in Audit view below. Specific terms in a contract might call for slightly different calculations, but this is how I did it.

ExcelView
FormulaView

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .