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
interest earned = principal * [(1+interest rate)n -1]
you can make it more exact like bread. but in simple terms heres what i woul do.
i’ll calculature monthly interest for the balance. for eexample.
14700*(.06)/12 = 74 bucks for april 2011.
60500*(.06)/12 = 302 bucks for may 2011.
so you’ll get that interest value for the month. then you’ll have to extrapolate that shit to 2019.
74*(1.06)7 = 111 bucks for april 2011.
454 for may 2011.
then once you get future value tally that shit up. and you’ll have your total interest., add that upp with cumulative principal. and this is how much he owes!
The best way will be calculate is in days instead of month end balance … as you have mentioned there are receipts… this method is accurate and can’t be challenged by the borrower. For this enter all receipts as -ve … make days product of the balances o/s up to date of calculation of interest… and apply interest on the sum total of all days product… the effective rate is int. rate/ day.
The receipts above are assumed to be paid for the principal. If not the case can be adjusted against interest dues prior to the receipts.
if not considering above… days product can be applied to month end balances.
You guys seem to miss the point. I know how to calculate interest from a single point to another. I just had trouble doing it in Excel, with multiple “starting points”, especially when the rate is variable.
But I figured it out. It involves taking last months end-of-month balance, grossing it up by the current interest rate, then adding new charges to get a new end-of-month balance.
ooooo thats a better way. so you can see it as it grows.