Calculate interest receivable based on variable rate / month by number of days remaining in period

[marissa2]

I have a party that refunds me interest for a set term in days based on a varying rate per month. They tend to drop the ball and under pay me. I would like a formula to calculate how much they owe me. I know how many days they owe, with a table showing the rate for each month, the amount they owe me on, etc. I have everything except the formula because rates change and days owing can flow into multiple months. For instance, in the sample data, for the second row (unit 50185), I am expecting to receive 1 day in July @ 2.41% plus 31 days in August @ 2.41% plus 22 days in September @ 2.43%. I've tried nested if statements based on EOMONTH*VLOOKUP but for some months the formula goes on for miles. I'm wondering if a nested IPMT might do or if there's a better way?

 

[BSALV]

can you give an example with the XL2BB-tool because an image doesn't work great.
Is the interest rate something fixed per month or is it per unit&month ? Where can you find that ? That pivottable or somewhere else ?
How is your interest rate defined ? is it 2.41% per year, thus 2.41/365 % per day or is it compounded interest per day?

 

[BSALV]

it can become something like this
Marissa.xlsx

Map2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1				month	01/01/22	02/01/22	03/01/22	04/01/22	05/01/22	06/01/22	07/01/22	08/01/22	09/01/22	10/01/22	11/01/22	12/01/22
2				interest (year)	1,726%	2,351%	2,488%	1,509%	2,307%	2,225%	1,792%	2,314%	1,869%	2,492%	1,807%	1,777%
3				interest (day)	0,005%	0,006%	0,007%	0,004%	0,006%	0,006%	0,005%	0,006%	0,005%	0,007%	0,005%	0,005%
4
5				total	3,66 €	4,51 €	11,08 €	9,30 €	15,96 €	15,54 €	12,93 €	16,70 €	13,06 €	17,99 €	12,47 €	17,33 €
6	item	price	startdate	endate	month1	month2	month3	month4	month5	month6	month7	month8	month9	month10	month11	month12
7	1	5.000,00 €	03/15/22	03/14/23	- €	- €	5,79 €	6,20 €	9,80 €	9,14 €	7,61 €	9,83 €	7,68 €	10,58 €	7,42 €	7,55 €
8	2	2.500,00 €	01/01/19	07/22/25	3,66 €	4,51 €	5,28 €	3,10 €	4,90 €	4,57 €	3,80 €	4,91 €	3,84 €	5,29 €	3,71 €	3,77 €
9	3	1.000,00 €	05/12/22	11/27/22	- €	- €	- €	- €	1,26 €	1,83 €	1,52 €	1,97 €	1,54 €	2,12 €	1,34 €	- €
10	4	123.456,00 €	12/31/22	12/15/24	- €	- €	- €	- €	- €	- €	- €	- €	- €	- €	- €	6,01 €
11
Blad1

Cell Formulas
Range	Formula
F1:P1	F1	=EDATE(E1,1)
E2:P2	E2	=VLOOKUP(E1,Tabel1,2,0)
E3:P3	E3	=+E2/365
E5	E5	=SUM(Tabel2[month1])
F5	F5	=SUM(Tabel2[month2])
G5	G5	=SUM(Tabel2[month3])
H5	H5	=SUM(Tabel2[month4])
I5	I5	=SUM(Tabel2[month5])
J5	J5	=SUM(Tabel2[month6])
K5	K5	=SUM(Tabel2[month7])
L5	L5	=SUM(Tabel2[month8])
M5	M5	=SUM(Tabel2[month9])
N5	N5	=SUM(Tabel2[month10])
O5	O5	=SUM(Tabel2[month11])
P5	P5	=SUM(Tabel2[month12])
E7:P10	E7	=MAX(0,MIN(F$1,$D7+1)-MAX(E$1,$C7))*E$3*$B7

 

[marissa2]

Thanks! Yes, the percentages are annual and change every month having nothing to do with the actual units involved. What you've laid out is almost exactly what I came up with. I used the MAX MIN formula to calculate days (I found that in another thread...works beautifully), but then used a SUMPRODUCT of the # of days per month * daily interest rate * price. I'm liking the visual display of facts over a miles long formula now that I'm looking at it more.

Thank you for the reply. It's always great to have your thought process validated.

 

[BSALV]

to show you the difference between annual rate / 365 or compounded interest

test-rus (1).xlsm
	F	G	H	I	J
80				interest
81	2%	divided by 365	0,00548%	1,6438 €
82		compounded intrest	0,00543%	1,6289 €
83				0,0149 €	difference
84		amount	1.000 €
85		days	30
RUST

Cell Formulas
Range	Formula
H81	H81	=+F81/365
H82	H82	=+POWER(1+F81,1/365)-1
I81	I81	=+$H$84*H81*H85
I82	I82	=+H84*(POWER(1+H82,H85)-1)
I83	I83	=+I81-I82