# Calculating daily interest

#### thebowman

##### New Member
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.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

XL2BB

#### montecarlo2012

##### Well-known Member
check this post here in the forum

#### AhoyNC

##### Well-known Member
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

##### Banned user

@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
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

##### Banned user
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

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
Glad we could help. Thanks for the feedback and welcome to the forum.

#### Dave Patton

##### Well-known Member
You can calculate the simple interest with

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

#### Dave Patton

##### Well-known Member
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)

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,489
Messages
5,837,634
Members
430,506
Latest member
TonyIbbs

### 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.

### Which adblocker are you using?

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

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