I am in the middle of helping somebody with a lawsuit. We need to calculate the interest on an unpaid note, but my Excel skills fail me.
Basically, the “borrower” has a longtime outstanding receivable with the “lender”. (Borrower didn’t actually borrow any money. He just didn’t pay his bills, and Lender covered them for him. Same concept.) Borrower racked up some debt every month, then paid some off every month. But in the aggregate, the total amount kept growing and growing.
The last time that Borrower and Lender were square was in April 2011. Since then, it has ballooned as high as $365k. As of September 2018 (when Lender finally sued Borrower), the balance was $280k. (This is principal only. Interest is yet TBD.)
I have been tasked with trying to determine the interest charges for the past 8+ years. I’m having a hard time figuring out how to model it in Excel. Can anybody help? (To make matters worse, there is some debate on which interest rate to use. 10% is the maximum that can be charged without being considered “usury”. 6% is the “default” rate if nothing else is specified in the contract. Prime + 1% is the “lowest” amount, per the agreement. I’m not in the business of figuring out what interest rate to use. That’s for the lawyers to argue. I’m in the business of coming up with a dollar amount.)
So if I have “Month end” in Column A and “Cumulative Balance” in Column B, how can I calculate the interest owed as of June 30, 2019? (Note - we are only using the end-of-month balance. We’re not going back to calculate each day.)
EG:
April 2011 - $14,700
May 2011 - 60,500
June 2011 - 102,800
and so on, and so forth