Pre-payments.....help

[KatieO]

Hi all

I am 'trying' to create a prepayment schedule. I have scoured this site and I found a formula that I though would work....but it doesn't quite.
I need the excel sheet to show how much is left in the prepayment account every month.

For example. The insurance costs £1200 per year, after the first payment the prepayments schedule will show £1200 minus the first payment. It also needs to be done in days.
The formula I am currently works to start off with but grdually goes wrong.

Please can someone advise me on what I am doing wrong.

The formula i'm using is IF(K$2>=$D4,$G4-$H4*MAX(0,MIN(K$2,$E4)+1-$D4),"-")+H4

Many thanks, Katie.

 

[KatieO]

As you can see above, there should be 15 days of 0.21 in MAy - but there isn't...

 

[Dave Patton]

T202303a.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1									Allocate by days in month
2	Start	End	Amount	Total Days		Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23
3	31-Oct-22	15-May-23	41.88	197	0.21	0.00	0.21	6.38	6.59	6.59	5.95	6.59	6.38	3.19	0.00	0.00
4	01-Jan-23	30-Jun-23	24,000.00	181	132.60	0.00	0.00	0.00	0.00	4,110.50	3,712.71	4,110.50	3,977.90	4,110.50	3,977.90	0.00
2cc

Cell Formulas
Range	Formula
H3:H4	H3	=F3-E3+1
I3:I4	I3	=G3/H3
J3:J4	J3	=MAX(0, MIN(EOMONTH(J$2, 0), $F3) + 1 - MAX(J$2, $E3+1))*$G3/$H3
K3:T4	K3	=MAX(0, MIN(EOMONTH(K$2, 0), $F3) + 1 - MAX(K$2, $E3))*$I3

 

[KatieO]

T202303a.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1									Allocate by days in month
2	Start	End	Amount	Total Days		Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23
3	31-Oct-22	15-May-23	41.88	197	0.21	0.00	0.21	6.38	6.59	6.59	5.95	6.59	6.38	3.19	0.00	0.00
4	01-Jan-23	30-Jun-23	24,000.00	181	132.60	0.00	0.00	0.00	0.00	4,110.50	3,712.71	4,110.50	3,977.90	4,110.50	3,977.90	0.00
2cc

Cell Formulas
Range	Formula
H3:H4	H3	=F3-E3+1
I3:I4	I3	=G3/H3
J3:J4	J3	=MAX(0, MIN(EOMONTH(J$2, 0), $F3) + 1 - MAX(J$2, $E3+1))*$G3/$H3
K3:T4	K3	=MAX(0, MIN(EOMONTH(K$2, 0), $F3) + 1 - MAX(K$2, $E3))*$I3

Hi Dave, thank you so much for your reply. I'm trying to see how much is left in the prepayment account in the month as opposed to the monthly cost. For example: The cost in October is 0.21 - it would show (£41.88 - 0.21) £41.67. Can that be done? Once again, I really appreciate your help, Katie.

 

[Dave Patton]

T202303a.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P
1							Balance by Month
2	Start	End	Total Days	Amount	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23
3	31-Oct-22	15-May-23	197	41.88	41.67	35.29	28.70	22.11	16.16	9.57	3.19	0.00
4
2cc

Cell Formulas
Range	Formula
G3	G3	=F3-E3+1
I3:P3	I3	=H3-MAX(0, MIN(EOMONTH(I$2, 0), $F3) + 1 - MAX(I$2, $E3))*$H3/$G3

 

[KatieO]

Hi Dave, thanks for your updated formula. Unfortunately, it gives the same result as I had. There should be 15 days left in May - but I can't get it to show it. Or maybe I'm loooking at it the wrong way? Katie

 

[KatieO]

T202303a.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P
1							Balance by Month
2	Start	End	Total Days	Amount	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23
3	31-Oct-22	15-May-23	197	41.88	41.67	35.29	28.70	22.11	16.16	9.57	3.19	0.00
4
2cc

Cell Formulas
Range	Formula
G3	G3	=F3-E3+1
I3:P3	I3	=H3-MAX(0, MIN(EOMONTH(I$2, 0), $F3) + 1 - MAX(I$2, $E3))*$H3/$G3

Hi Dave, thanks for your updated formula. Unfortunately, it gives the same result as I had. There should be 15 days left in May - but I can't get it to show it. Or maybe I'm loooking at it the wrong way? Katie

 

[Dave Patton]

N.B. The amount amortized in May is the balance at the end of April $3.19 or H3/G3*15 which leaves a balance of 0.