Calculating interest for loan with monthly fee

[jaxon7au]

Hi,

I am having trouble trying to set up a spread sheet that will calculate the interest that will be charged to my loan each month.

The details are;

Original loan amount is $4,363.44 on the 10/01/2022.
Annual interest rate 9.99% p.a.
Monthly fee is $13.00
Loan term is 4 years and maturity date is 10/01/2026

Monthly repayment of $123.85 is due on the 10th of each month, but I pay weekly $30.96.
My first weekly payment was made on 19/01/2022.

Is anyone able to help out with this? I tried searching but could only find calculators that either did not have the monthly fee or only had monthly payments.

Thanks a lot

 

[BSALV]

jaxon
a months is 4.3 weeks !!!!

Map2
	A	B	C	D	E	F	G	H	I	J	K
10
11		yearly	monthly	weekly
12	interest rate	9,99%	0,83%	0,19%
13
14	date	saldo	payment	interest	fee		date	saldo	payment	interest	fee
15	10/01/22	4.363,44 €					10/01/22	4.363,44 €
16	10/02/22	4.288,92 €	123,85 €	36,33 €	13,00 €		17/01/22	4.343,85 €	30,96 €	8,38 €	2,99 €
17	10/03/22	4.213,78 €	123,85 €	35,71 €	13,00 €		24/01/22	4.324,23 €	30,96 €	8,35 €	2,99 €
18	10/04/22	4.138,01 €	123,85 €	35,08 €	13,00 €		31/01/22	4.304,57 €	30,96 €	8,31 €	2,99 €
19	10/05/22	4.061,61 €	123,85 €	34,45 €	13,00 €		7/02/22	4.284,87 €	30,96 €	8,27 €	2,99 €
20	10/06/22	3.984,57 €	123,85 €	33,81 €	13,00 €		14/02/22	4.265,13 €	30,96 €	8,23 €	2,99 €
21	10/07/22	3.906,89 €	123,85 €	33,17 €	13,00 €		21/02/22	4.245,35 €	30,96 €	8,19 €	2,99 €
22	10/08/22	3.828,56 €	123,85 €	32,52 €	13,00 €		28/02/22	4.225,54 €	30,96 €	8,16 €	2,99 €
23	10/09/22	3.749,58 €	123,85 €	31,87 €	13,00 €		7/03/22	4.205,69 €	30,96 €	8,12 €	2,99 €
24	10/10/22	3.669,95 €	123,85 €	31,22 €	13,00 €		14/03/22	4.185,80 €	30,96 €	8,08 €	2,99 €
25	10/11/22	3.589,65 €	123,85 €	30,55 €	13,00 €		21/03/22	4.165,87 €	30,96 €	8,04 €	2,99 €
26	10/12/22	3.508,68 €	123,85 €	29,88 €	13,00 €		28/03/22	4.145,90 €	30,96 €	8,00 €	2,99 €
27	10/01/23	3.427,04 €	123,85 €	29,21 €	13,00 €		4/04/22	4.125,89 €	30,96 €	7,96 €	2,99 €
28	10/02/23	3.344,72 €	123,85 €	28,53 €	13,00 €		11/04/22	4.105,85 €	30,96 €	7,93 €	2,99 €
29	10/03/23	3.261,71 €	123,85 €	27,84 €	13,00 €		18/04/22	4.085,77 €	30,96 €	7,89 €	2,99 €
30	10/04/23	3.178,01 €	123,85 €	27,15 €	13,00 €		25/04/22	4.065,65 €	30,96 €	7,85 €	2,99 €
31	10/05/23	3.093,62 €	123,85 €	26,46 €	13,00 €		2/05/22	4.045,49 €	30,96 €	7,81 €	2,99 €
32	10/06/23	3.008,52 €	123,85 €	25,75 €	13,00 €		9/05/22	4.025,29 €	30,96 €	7,77 €	2,99 €
33	10/07/23	2.922,72 €	123,85 €	25,05 €	13,00 €		16/05/22	4.005,05 €	30,96 €	7,73 €	2,99 €
34	10/08/23	2.836,20 €	123,85 €	24,33 €	13,00 €		23/05/22	3.984,77 €	30,96 €	7,69 €	2,99 €
35	10/09/23	2.748,96 €	123,85 €	23,61 €	13,00 €		30/05/22	3.964,46 €	30,96 €	7,66 €	2,99 €
36	10/10/23	2.661,00 €	123,85 €	22,89 €	13,00 €		6/06/22	3.944,11 €	30,96 €	7,62 €	2,99 €
37	10/11/23	2.572,30 €	123,85 €	22,15 €	13,00 €		13/06/22	3.923,72 €	30,96 €	7,58 €	2,99 €
38	10/12/23	2.482,86 €	123,85 €	21,41 €	13,00 €		20/06/22	3.903,29 €	30,96 €	7,54 €	2,99 €
39	10/01/24	2.392,68 €	123,85 €	20,67 €	13,00 €		27/06/22	3.882,82 €	30,96 €	7,50 €	2,99 €
40	10/02/24	2.301,75 €	123,85 €	19,92 €	13,00 €		4/07/22	3.862,31 €	30,96 €	7,46 €	2,99 €
41	10/03/24	2.210,06 €	123,85 €	19,16 €	13,00 €		11/07/22	3.841,76 €	30,96 €	7,42 €	2,99 €
42	10/04/24	2.117,61 €	123,85 €	18,40 €	13,00 €		18/07/22	3.821,17 €	30,96 €	7,38 €	2,99 €
Blad1

Cell Formulas
Range	Formula
C12	C12	=+B12/12
D12	D12	=+B12/52
H15	H15	=+B15
A16:A42	A16	=EDATE(A15,1)
B16:B42,H16:H42	B16	=+B15-C16+D16+E16
D16:D42	D16	=+ROUND(B15*$C$12,2)
G16:G42	G16	=IF(+G15+7<$A$63+6,G15+7,"-")
J16:J42	J16	=+ROUND(H15*$D$12,2)
K16	K16	=ROUND(13*12*7/365,2)
C17:C42,E17:E42,I17:I42,K17:K42	C17	=+C16

 

[jaxon7au]

Oh wow!! Thanks a lot for this

 

[BSALV]

You're welcome.

Did you understand why ?
A year is 12 months or 365(+1) days.
That's 52.14 (or 52.28) weeks per year.
Divide by 12 months is that 4.34 weeks per month.

You had to divide your monthly payment and fee by 4.34 instead of 4
128.35/4.34 = 29.5 (= -2.57)

The further reduction to 28.36 (= -1.16) is because you pay on weekly base and with 10% interest

Map1
	A	B	C	D	E	F
1		normal	leap year	average
2	1 year	365	366	365,25
3	months	12	12	12
4	weeks	52,14	52,29	52,18
5	weeks/month	4,35	4,36	4,35
6
7	your 128,35/4	32,09 €	32,09 €	32,09 €
8	128,35€/monthly	29,54 €	29,46 €	29,52 €		128,35 €
9	delta	2,55 €	2,63 €	2,57 €
10	excel			28,36 €
11	delta			1,16 €
Blad1

Cell Formulas
Range	Formula
B4:D4	B4	=+B2/7
B5:D5	B5	=+B4/B3
B7:D7	B7	=+$F$8/4
B8:D8	B8	=+$F$8/B5
B9:D9	B9	=+B7-B8
D11	D11	=+D8-D10

 

[jaxon7au]

OK, thanks for the follow up, does make it totally clear.
The 1st interest charge was spot on.

Thanks again