calculating bank interest (basic interest + loyalty interest) per month for three years

[littlepete]

Hello all
i have columns D to O for each month,
row 6 for name of the month
row 8 basic interest for that month; row 9 loyalty interest for that month; row 10 total of 8+9

row 12 and next copy of 6 and following for second year
row 18 and next copy of 6 and following for third year

gained interest is added in april, july, october and january
loyalty interest is added for each amount available a full year

basic interest: 1,2% loyalty interest: 1,8% starting at 500 euro...

who can help me with the formulae ?
have a nice day !!!

link to my cloud for this file : intrest argenta.xlsm

 

[arun_eagertolearn]

Hi

Pls check this

Book3
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
3		2023												2024												2025
4		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
5		€ 500	€ 1,000	€ 1,500	€ 2,003	€ 2,503	€ 3,003	€ 3,514	€ 4,014	€ 4,514	€ 5,036	€ 5,536	€ 6,036	€ 6,536	€ 7,036	€ 7,536	€ 8,036	€ 8,536	€ 9,036	€ 9,536	€ 10,036	€ 10,536	€ 11,036	€ 11,536	€ 12,036	€ 12,536	€ 13,036	€ 13,536	€ 14,036	€ 14,536	€ 15,036	€ 15,536	€ 16,036	€ 16,536	€ 17,036	€ 17,536	€ 18,036
6	BASIS	€ 0.50	€ 1.00	€ 1.50	€ 2.00	€ 2.50	€ 3.00	€ 3.51	€ 4.01	€ 4.51	€ 5.04	€ 5.54	€ 6.04	€ 6.54	€ 7.04	€ 7.54	€ 8.04	€ 8.54	€ 9.04	€ 9.54	€ 10.04	€ 10.54	€ 11.04	€ 11.54	€ 12.04	€ 12.54	€ 13.04	€ 13.54	€ 14.04	€ 14.54	€ 15.04	€ 15.54	€ 16.04	€ 16.54	€ 17.04	€ 17.54	€ 18.04
7	GETROUW	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.75	€ 2.25	€ 4.50	€ 7.50	€ 11.26	€ 15.76	€ 21.03	€ 27.05	€ 33.82	€ 41.38	€ 49.68	€ 58.74	€ 68.54	€ 79.09	€ 90.40	€ 102.45	€ 115.26	€ 128.81	€ 143.12	€ 158.17	€ 173.97	€ 190.53	€ 207.83	€ 225.89
8	TOTAAL	€ 0.50	€ 1.00	€ 1.50	€ 2.00	€ 2.50	€ 3.00	€ 3.51	€ 4.01	€ 4.51	€ 5.04	€ 5.54	€ 6.04	€ 7.29	€ 9.29	€ 12.04	€ 15.54	€ 19.80	€ 24.80	€ 30.57	€ 37.09	€ 44.36	€ 52.41	€ 61.22	€ 70.77	€ 81.08	€ 92.13	€ 103.93	€ 116.49	€ 129.79	€ 143.85	€ 158.65	€ 174.21	€ 190.51	€ 207.56	€ 225.37	€ 243.92
Backup

Cell Formulas
Range	Formula
C5	C5	=D1
D5:F5	D5	=C5+$G$1+IF(OR(D2=1,D2=4,D2=7,D2=10),1,0)*SUM($C8:C8)
G5:H5,J5:K5,M5:AL5	G5	=F5+$G$1
I5,L5	I5	=H5+$G$1+SUM($C8:H8)
C6:AL6	C6	=C5*$J$1/12
C7	C7	=IF(COUNTA(B4:$C4)<12,0,B7+MIN(#REF!)*$N$1/12)
D7:L7	D7	=IF(COUNTA($C4:C4)<12,0,C7+MIN(#REF!)*$N$1/12)
M7:AL7	M7	=IF(COUNTA($C4:L4)<12,0,L7+MIN(A5:L5)*$N$1/12)
C8:AL8	C8	=SUM(C6:C7)

 

[littlepete]

hello,
1.
you end up with a total intrest after three years of 243,92 euro. my page ends on 572,97 euro which is much more. AND : an official website where you can calculate intrests gives a total of over 760 euro...

2.
why is there " min(#ref!)* ... " in two of your formulae?

please if you want to help do give me good help... pfff

 

[arun_eagertolearn]

Hi

Apologies for the inconvenience
I had gone as per the instructions you had given, and not tried to match the figures

In the calculations you have done, interests have been added in May, Sep etc- whereas the instructions state it to be Apr, Jul, Oct etc
Finally, I believe the official website calculations would be correct

#REF issue
As the formula looks for minimum of the previous 12 months, there would be a IF - false condition which would not get activated for the first 12 months
That is where the min(#ref) may appear
As explained above, this would not get activated, as the IF condition before would not select that section

It would be good if you could get the figures for the different months from the official website- so that I could try and validate them

Also, in case any other element is missing

 

[littlepete]

hello
i did ask for those monthly numbers but they dont answer any of my questions... so i only have the end result, that's why i'm trying to make the monthly evaluation myself...
feel free to change my sheet , i do believe however my calculations are correct (except maybe for the trimestrial adding: do they mean jan feb mar apr and then adding intrest of those in may?
that's what i believe... or is it jan feb mar apr and adding intrest in apr already? i'm not sure...
good luck and thank you !

 

[arun_eagertolearn]

Hi

I tried some iterations, the max value I have been getting is 730

Probably, I am missing something

Interest- littlepipe.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1		STARTBEDRAG :	€ 500.00		MAANDELIJKS :	€ 500.00		BASISINTREST :	1.20%			GETROUWHEIDSINTREST :	1.80%
2	Month No	1	2	3	4	5	6	7	8	9	10	11	12	1	2	3	4	5	6	7	8	9	10	11	12	1	2	3	4	5	6	7	8	9	10	11	12
3		2023												2024												2025
4		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
5		€ 500	€ 1,000	€ 1,500	€ 2,005	€ 2,505	€ 3,005	€ 3,533	€ 4,033	€ 4,533	€ 5,116	€ 5,616	€ 6,116	€ 6,800	€ 7,300	€ 7,800	€ 8,659	€ 9,159	€ 9,659	€ 10,805	€ 11,305	€ 11,805	€ 13,387	€ 13,887	€ 14,387	€ 16,597	€ 17,097	€ 17,597	€ 20,681	€ 21,181	€ 21,681	€ 25,938	€ 26,438	€ 26,938	€ 32,746	€ 33,246	€ 33,746
6	BASIS	€ 0.50	€ 1.50	€ 3.00	€ 5.01	€ 7.51	€ 10.52	€ 14.05	€ 18.08	€ 22.61	€ 27.73	€ 33.35	€ 39.46	€ 46.26	€ 53.56	€ 61.36	€ 70.02	€ 79.18	€ 88.84	€ 99.64	€ 110.95	€ 122.75	€ 136.14	€ 150.03	€ 164.41	€ 181.01	€ 198.11	€ 215.71	€ 236.39	€ 257.57	€ 279.25	€ 305.19	€ 331.62	€ 358.56	€ 391.31	€ 424.56	€ 458.30
7	GETROUW	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.00	€ 0.75	€ 2.25	€ 4.50	€ 7.51	€ 11.27	€ 15.77	€ 21.07	€ 27.12	€ 33.92	€ 41.59	€ 50.02	€ 59.19	€ 69.39	€ 80.34	€ 92.04	€ 105.03	€ 118.77	€ 133.26	€ 149.47	€ 166.42	€ 184.13	€ 204.21	€ 225.04	€ 246.62	€ 271.52
8	TOTAAL	€ 0.50	€ 1.50	€ 3.00	€ 5.01	€ 7.51	€ 10.52	€ 14.05	€ 18.08	€ 22.61	€ 27.73	€ 33.35	€ 40.21	€ 48.51	€ 58.06	€ 68.87	€ 81.29	€ 94.95	€ 109.91	€ 126.77	€ 144.87	€ 164.35	€ 186.16	€ 209.22	€ 233.81	€ 261.35	€ 290.15	€ 320.74	€ 355.16	€ 390.83	€ 428.71	€ 471.61	€ 515.76	€ 562.77	€ 616.35	€ 671.18	€ 729.82
Intr backup (2)

Cell Formulas
Range	Formula
C2:AL2	C2	=MONTH(DATEVALUE(C4&1))
C5	C5	=D1
D5	D5	=C5+$G$1
E5:AL5	E5	=D5+$G$1+IF(OR(E2=1,E2=4,E2=7,E2=10),1,0)*SUM($C8:D8)
C6	C6	=C5*$J$1/12
D6:AL6	D6	=C6+D5*$J$1/12
C7	C7	=IF(COUNTA(B4:$C4)<11,0,B7+MIN(#REF!)*$N$1/12)
D7:K7	D7	=IF(COUNTA($C4:C4)<11,0,C7+MIN(#REF!)*$N$1/12)
L7:AL7	L7	=IF(COUNTA($C4:K4)<11,0,K7+MIN(A5:L5)*$N$1/12)
C8:AL8	C8	=SUM(C6:C7)

 

[littlepete]

hello
yesterday i had the luck to have a chance to test the chatgtp so i went step by step through this question and "we" ended up in the perfect result
one detail i forgot: when adding the monthly intrest, you should calculate that intrest on the basic amount + all gained intrest sofar...
i only calculated that intrest on the basic amount...
thank you for your replies !!! and have a great weekend tomorrow is election day here (belgium) !

 

[arun_eagertolearn]

Glad that you could solve it