Calculating daily interest

[thebowman]

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.

 

[yky]

Please download and install XL2BB following this link so people don't have to retype you data:

XL2BB

 

[montecarlo2012]

check this post here in the forum

calculate interest per day

Hello all, Total loan amount is 200,000.00 Interest rate is 6.95% Interest for 15 days is 38.6111 per day My question is what would the formula be to arrive at 38.6111 for 15 days? Thanks for any help James

www.mrexcel.com

 

[AhoyNC]

Maybe something like this:

Book1
	A	B	C	D	E	F	G	H	I
1				Yearly Rate	2%
2				Daily Rate	0.00005479
3						Simple	Compound	Accumulative
4	Date		Loan	Due	Days	Interest	Interest	Simple	Compound
5	5/31/2017			700.00
6	10/11/2017			800.00	134	5.14	5.16	5.14	5.16
7	8/18/2018			2,800.00	312	13.68	13.79	18.82	18.95
8	2/20/2019			2,300.00	187	28.69	28.84	47.51	47.79
9	6/18/2019			3,300.00	119	15.00	15.05	62.50	62.84
10	7/12/2019			3,495.00	25	4.52	4.52	67.02	67.36
11	8/6/2019			3,795.00	26	4.98	4.98	72.00	72.34
12	9/13/2019			3,165.00	39	8.11	8.12	80.11	80.46
13	12/8/2019			3,210.00	87	15.09	15.12	95.20	95.58
14	1/2/2020			5,210.00	26	4.57	4.58	99.77	100.16
15	1/13/2020			4,410.00	12	3.43	3.43	103.20	103.59
16	1/16/2020			4,522.00	4	0.97	0.97	104.17	104.55
17	6/12/2020			4,972.00	149	36.92	37.07	141.09	141.62
18						141.09	141.62
Sheet1

Cell Formulas
Range	Formula
E2	E2	=E1/365
E6:E17	E6	=A6-A5+1
F6:F17	F6	=(E6*$E$2)*D5
G6:G17	G6	=(D5*(1+$E$2)^E6)-D5
H6:H17	H6	=SUM($F$6:F6)
I6:I17	I6	=SUM($G$6:G6)
F18:G18	F18	=SUM(F6:F17)

 

[joeu2004]

@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
	A	B	C	D	E
1	Annual rate	2.00%		Int to Date	Balance
2	Last transaction	6/12/2020			$5,112.48
3	Bal to date	6/26/2020		$3.92	$5,116.40
4	Total interest	$144.40
5
6		Transaction	Amount	Int to Date	Balance
7	5/31/2017	Loan A	$700.00		$700.00
8	10/11/2017	Loan B	$100.00	$3.93	$803.93
9	8/18/2018	Loan C	$2,000.00	$13.84	$2,817.77
10	2/20/2019	Payment	-$500.00	$28.70	$2,346.47
11	6/18/2019	Loan D	$1,000.00	$15.11	$3,361.58
12	7/12/2019	Purchases	$195.00	$4.42	$3,561.00
13	8/6/2019	Domain renewal	$300.00	$4.88	$3,865.88
14	9/13/2019	Payment	-$630.00	$8.05	$3,243.93
15	12/8/2019	Canada Post	$45.00	$15.28	$3,304.21
16	1/2/2020	Loan	$2,000.00	$4.53	$5,308.74
17	1/13/2020	Payment	-$800.00	$3.20	$4,511.94
18	1/16/2020	Various	$112.00	$0.74	$4,624.68
19	6/12/2020	Various	$450.00	$37.80	$5,112.48
Sheet1

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]

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.

 

[thebowman]

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]

Glad we could help. Thanks for the feedback and welcome to the forum.

 

[Dave Patton]

You can calculate the simple interest with

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

 

[Dave Patton]

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
	A	B	C	D	E
1					2%
2				Daily	0.00548%
3					2.00%
4	Date		Amount	Interest	Balance
5	31-May-17		700.00		700.00
6	30-Jun-17			1.15	701.15
7	31-Jul-17			1.19	702.34
8	31-Aug-17			1.19	703.53
9	30-Sep-17			1.16	704.69
10	11-Oct-17		100.00	0.42	805.12
11	31-Oct-17			0.88	806.00
12	30-Nov-17			1.32	807.32
13	31-Dec-17			1.37	808.69
14	31-Jan-18			1.37	810.07
15	28-Feb-18			1.24	811.31
16	31-Mar-18			1.38	812.69
17	30-Apr-18			1.34	814.03
18	31-May-18			1.38	815.41
19	30-Jun-18			1.34	816.75
20	31-Jul-18			1.39	818.14
21	18-Aug-18		2,000.00	0.81	2,818.94
22	31-Aug-18			2.01	2,820.95
23	30-Sep-18			4.64	2,825.59
24	31-Oct-18			4.80	2,830.39
25	30-Nov-18			4.65	2,835.04
26	31-Dec-18			4.82	2,839.86
27	31-Jan-19			4.82	2,844.68
28	20-Feb-19		-500.00	3.12	2,347.80
29	28-Feb-19			1.03	2,348.83
30	31-Mar-19			3.99	2,352.82
31	30-Apr-19			3.87	2,356.68
32	31-May-19			4.00	2,360.69
33	18-Jun-19		1,000.00	2.33	3,363.02
34	30-Jun-19			2.21	3,365.23
35	12-Jul-19		195.00	2.21	3,562.44
36	31-Jul-19			3.71	3,566.15
37	06-Aug-19		300.00	1.17	3,867.32
38	31-Aug-19			5.30	3,872.62
39	13-Sep-19		-630.00	2.76	3,245.38
40	30-Sep-19			3.02	3,248.40
41	31-Oct-19			5.52	3,253.92
42	30-Nov-19			5.35	3,259.27
43	08-Dec-19		45.00	1.43	3,305.70
44	31-Dec-19			4.17	3,309.86
45	02-Jan-20		2,000.00	0.36	5,310.22
46	13-Jan-20		-800.00	3.20	4,513.42
47	16-Jan-20		112.00	0.74	4,626.17
48	31-Jan-20			3.80	4,629.97
49	29-Feb-20			7.36	4,637.33
50	31-Mar-20			7.88	4,645.20
51	30-Apr-20			7.64	4,652.84
52	31-May-20			7.90	4,660.74
53	12-Jun-20		450.00	3.06	5,113.81
54	30-Jun-20			5.04	5,118.85
55			4,972.00	146.85
2a_

Cell Formulas
Range	Formula
E2	E2	=E1/365
E5:E33	E5	=N(E4)+C5+D5
A6	A6	=EOMONTH(A5,1)
D6:D54	D6	=(A6-A5)*E5*$E$2
E34:E54	E34	=+E33+C34+D34
C55:D55	C55	=SUM(C5:C54)