Calculating daily interest

thebowman

New Member
Joined
Jun 26, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Hello,
I have someone to whom I've been lending money at random times for the last 3 years, sometimes is $600, sometimes can be $2000. It's a friend so I've never paid much attention to how much this money would have given me if I had it in the bank or invested. But now the debt is considerable and although he pays me back whatever he can, he's still a long way to pay me back the balance.
I have been keeping an xls sheet where I simply have the date, amount and balance and I keep track of the loans and the payments.
Since every amount is on a different date over 4 years, how can I calculate the daily interest that I would have accumulated to date if the money had been sitting in the bank or invested.
For this exercise let's assume a yearly rate of 2%. I can always change it.
Attached is an example of what the sheet looks like. I'd like to have the last column to reflect the interest that each amount would have produced as of today, unless you can see a more practical way to have the total interest to date.
What happens when he makes a payment? Is it deducted from the oldest loans? ...
In a way is like a line of credit of a bank I guess where you can withdraw money and make payments periodically, the only difference is that the payments are scheduled on a fixed period usually monthly whereas here my friend pays me whenever he can.
Some help or advice would be appreciated.

interest.jpg
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,856
Office Version
  1. 2010
Platform
  1. Windows
Please download and install XL2BB following this link so people don't have to retype you data:

XL2BB
 

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
675
Office Version
  1. 2010
Platform
  1. Windows
check this post here in the forum
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,759
Office Version
  1. 365
Platform
  1. Windows
Maybe something like this:

Book1
ABCDEFGHI
1Yearly Rate2%
2Daily Rate0.00005479
3SimpleCompoundAccumulative
4DateLoanDueDaysInterestInterestSimpleCompound
55/31/2017 700.00
610/11/2017 800.00 134 5.14 5.16 5.14 5.16
78/18/2018 2,800.00 312 13.68 13.79 18.82 18.95
82/20/2019 2,300.00 187 28.69 28.84 47.51 47.79
96/18/2019 3,300.00 119 15.00 15.05 62.50 62.84
107/12/2019 3,495.00 25 4.52 4.52 67.02 67.36
118/6/2019 3,795.00 26 4.98 4.98 72.00 72.34
129/13/2019 3,165.00 39 8.11 8.12 80.11 80.46
1312/8/2019 3,210.00 87 15.09 15.12 95.20 95.58
141/2/2020 5,210.00 26 4.57 4.58 99.77 100.16
151/13/2020 4,410.00 12 3.43 3.43 103.20 103.59
161/16/2020 4,522.00 4 0.97 0.97 104.17 104.55
176/12/2020 4,972.00 149 36.92 37.07 141.09 141.62
18 141.09 141.62
Sheet1
Cell Formulas
RangeFormula
E2E2=E1/365
E6:E17E6=A6-A5+1
F6:F17F6=(E6*$E$2)*D5
G6:G17G6=(D5*(1+$E$2)^E6)-D5
H6:H17H6=SUM($F$6:F6)
I6:I17I6=SUM($G$6:G6)
F18:G18F18=SUM(F6:F17)
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,021
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

@thebowman.... Try the following design. See comments below.

You can copy this into a new worksheet by clicking on the copy icon under "f(x)" on the upper-left, and pasting into A1.

running loan bal.xlsx
ABCDE
1Annual rate2.00%Int to DateBalance
2Last transaction6/12/2020$5,112.48
3Bal to date6/26/2020$3.92$5,116.40
4Total interest$144.40
5
6TransactionAmountInt to DateBalance
75/31/2017Loan A$700.00$700.00
810/11/2017Loan B$100.00$3.93$803.93
98/18/2018Loan C$2,000.00$13.84$2,817.77
102/20/2019Payment-$500.00$28.70$2,346.47
116/18/2019Loan D$1,000.00$15.11$3,361.58
127/12/2019Purchases$195.00$4.42$3,561.00
138/6/2019Domain renewal$300.00$4.88$3,865.88
149/13/2019Payment-$630.00$8.05$3,243.93
1512/8/2019Canada Post$45.00$15.28$3,304.21
161/2/2020Loan$2,000.00$4.53$5,308.74
171/13/2020Payment-$800.00$3.20$4,511.94
181/16/2020Various$112.00$0.74$4,624.68
196/12/2020Various$450.00$37.80$5,112.48
Sheet1
Rich (BB code):
Formulas:
B2: =LOOKUP(1E+300,A:A)
B3: =TODAY()
B4: =ROUND(SUM(D3,D8:D10000),2)
D3: =MAX(ROUND(IFERROR(FV($B$1/12,DATEDIF(EOMONTH(B2,0),EOMONTH(B3,-1),"m"),
     0,-E2*(1+(EOMONTH(B2,0)-B2)*$B$1/365))*(1+DAY(B3)*$B$1/365)-E2,
     E2*(B3-B2)*$B$1/365), 2), 0)
E3: =LOOKUP(1E+300,E:E)
E4: =ROUND(E2+D3,2)
D8: =MAX(ROUND(IFERROR(FV($B$1/12,DATEDIF(EOMONTH(A7,0),EOMONTH(A8,-1),"m"),
     0,-E7*(1+(EOMONTH(A7,0)-A7)*$B$1/365))*(1+DAY(A8)*$B$1/365)-E7,
     E7*(A8-A7)*$B$1/365), 2), 0)
E7: =C7
E8: =ROUND(E7+C8+D8,2)
Copy D8:E8 into D9:E19


To add a transaction after row 19, I copy the last transaction and paste it into the next row, ignoring errors. Then I enter the relevant data into columns A:C of the new row.

Alternatively, we could add some robustness to the formulas in columns D:E so that you could copy them down many otherwise-empty rows, like a template. Do you want that?

The "Int to Date" formula calculates interest on the previous balance up to that row's transaction.

The formula in D3 calculates interest on the last transaction up to the date in B3, ostensibly today's date.

The formula in E3 is the balance, including interest in D3, up to the date in B3.

The formula in B4 calculates ``interest that I would have accumulated to date``.

Re: `` What happens when he makes a payment? Is it deducted from the oldest loans?`` It is deducted from the "running balance". It does not matter which loan it applies to. The payment is against the outstanding balance in E3.

Normally, banks calculate simple interest on the daily balance, which is paid to the account each month. (I chose the end of the month.) Thus, interest compounds monthly.

So ostensibly, the formulas in D3 and D8 calculate interest as follows:

1. Simple interest on the preivous row's (or last transaction) balance up to the end of that month.
2. Plus compounded monthly interest on the previous end-of-month balance (including interest) up to the month before the current transaction.
3. Plus simple interest on month-before balance up to the current transaction.

The IFERROR function covers the special case when the previous and current transactions are in the same month. In that case, the formulas calculate simple interest between the previous and current transactions.

The MAX(...,0) expression covers another special case when a payment might exceed the outstanding balance on the same date. In that case, the calculated interest would be negative; but in fact, there should be no additional interest.

LOOKUP(1E+300,A:A) returns the last numeric value in the column. Similarly with =LOOKUP(1E+300,E:E).

The calculated interest is rounded to the cent.

Other rounding in B4, E4 and E8 is a prudent "good practice" in order to avoid anomalies of internal binary arithmetic.

Caveat: Technically, interest should be rounded on a monthly basis.

Thus, for example, the FV last parameter should be -ROUND(E7*(1+(EOMONTH(A7,0)-A7)*$B$1/365, 2).

But the FV function cannot round intermediate periodic calculations. And I do not believe we can construct a compounding formula that will.

If you require that degree of accuracy, I think we would need to provide a VBA function that emulates the FV function, but rounds intermediate periodic results. Do you want that?!

IMHO, it is not worth the trouble, especially for such short periods between transactions and such small interest amounts.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,021
Office Version
  1. 2010
Platform
  1. Windows
PS.... I started composing my previous response before AhoyNC posted his. And I didn't notice his posting before I pressed "post reply".

In some respects, I like the simplicity of his simple interest calculations. (Normally, interest does not compound daily in bank accounts.)

The greater precision of my calculations might be overkill for the OP's purposes.
 
Last edited:

thebowman

New Member
Joined
Jun 26, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

First of all, I sincerely apologize for not using XL2BB in my first post. I knew that we are not allowed to attach actual xls files and I should have read more carefully the forum rules.
Thank you everyone that responded. This is exactly what I was looking for !
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,759
Office Version
  1. 365
Platform
  1. Windows
Glad we could help. Thanks for the feedback and welcome to the forum.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,654
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You can calculate the simple interest with

=SUMPRODUCT(A6:A17-A5:A16+1,D5:D16)*E2
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,654
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Formula for the simple interest to June 30 or Today.
=SUMPRODUCT(A6:A18-A5:A17,D5:D17)*E2

A simple sheet that will calculate the interest and compound it each month follows.

Interest.xlsm
ABCDE
12%
2Daily0.00548%
32.00%
4DateAmountInterestBalance
531-May-17700.00700.00
630-Jun-171.15701.15
731-Jul-171.19702.34
831-Aug-171.19703.53
930-Sep-171.16704.69
1011-Oct-17100.000.42805.12
1131-Oct-170.88806.00
1230-Nov-171.32807.32
1331-Dec-171.37808.69
1431-Jan-181.37810.07
1528-Feb-181.24811.31
1631-Mar-181.38812.69
1730-Apr-181.34814.03
1831-May-181.38815.41
1930-Jun-181.34816.75
2031-Jul-181.39818.14
2118-Aug-182,000.000.812,818.94
2231-Aug-182.012,820.95
2330-Sep-184.642,825.59
2431-Oct-184.802,830.39
2530-Nov-184.652,835.04
2631-Dec-184.822,839.86
2731-Jan-194.822,844.68
2820-Feb-19-500.003.122,347.80
2928-Feb-191.032,348.83
3031-Mar-193.992,352.82
3130-Apr-193.872,356.68
3231-May-194.002,360.69
3318-Jun-191,000.002.333,363.02
3430-Jun-192.213,365.23
3512-Jul-19195.002.213,562.44
3631-Jul-193.713,566.15
3706-Aug-19300.001.173,867.32
3831-Aug-195.303,872.62
3913-Sep-19-630.002.763,245.38
4030-Sep-193.023,248.40
4131-Oct-195.523,253.92
4230-Nov-195.353,259.27
4308-Dec-1945.001.433,305.70
4431-Dec-194.173,309.86
4502-Jan-202,000.000.365,310.22
4613-Jan-20-800.003.204,513.42
4716-Jan-20112.000.744,626.17
4831-Jan-203.804,629.97
4929-Feb-207.364,637.33
5031-Mar-207.884,645.20
5130-Apr-207.644,652.84
5231-May-207.904,660.74
5312-Jun-20450.003.065,113.81
5430-Jun-205.045,118.85
554,972.00146.85
2a_
Cell Formulas
RangeFormula
E2E2=E1/365
E5:E33E5=N(E4)+C5+D5
A6A6=EOMONTH(A5,1)
D6:D54D6=(A6-A5)*E5*$E$2
E34:E54E34=+E33+C34+D34
C55:D55C55=SUM(C5:C54)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top