Prepayment Schedule Formula

[adamski911]

Hi,

I am looking for a formula that will calculate my monthly payments based on a daily rate for each month.

I have manually entered the costs below to show what I am looking to return.

[TABLE="class: grid, width: 767"]
<colgroup><col><col><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD][TABLE="width: 767"]
<colgroup><col><col><col><col><col><col span="6"></colgroup><tbody>[TR="class: grid"]
[TD] [/TD]
[TD]Start Date[/TD]
[TD]Finish Date[/TD]
[TD]Days[/TD]
[TD]Amount[/TD]
[TD="align: right"]31/08/2017[/TD]
[TD="align: right"]30/09/2017[/TD]
[TD="align: right"]31/10/2017[/TD]
[TD="align: right"]30/11/2017[/TD]
[TD="align: right"]31/12/2017[/TD]
[TD="align: right"]31/01/2018[/TD]
[/TR]
[TR="class: grid"]
[TD]Rates[/TD]
[TD="align: right"]15/08/2017[/TD]
[TD="align: right"]22/01/2018[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]994[/TD]
[TD="align: right"]1925[/TD]
[TD="align: right"]1863[/TD]
[TD="align: right"]1925[/TD]
[TD="align: right"]1925[/TD]
[TD="align: right"]1366[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Can someone please help me?

Thanks

 

[Marcelo Branco]

Thanks Marcelo,

This is what I was looking for

You are welcome. Glad we could help.

M.

 

[hdave]

Maybe...

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

Start Date​

Finish Date​

Days​

Amount​

31/08/2017​

30/09/2017​

31/10/2017​

30/11/2017​

31/12/2017​

31/01/2018​

28/02/2018​

2​

Rates​

15/08/2017​

22/01/2018​

161​

10000​

1055,90​

1863,35​

1925,47​

1863,35​

1925,47​

1366,46​

0,00​

3​

<tbody>
</tbody>


Formula in F2 copied across
=MAX(0,1+MIN(F1,$C2)-MAX(DATE(YEAR(F1),MONTH(F1),1),$B2))/$D2*$E2

Hope this helps

M.

Hi Marcelo, thanks very much for posting this.

To take it one step further I'm wanting to add a closing monthly balance formula for a list of prepayments all with different start and finish dates. This is the data:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Description	Start Date	End Date	Days in Term	Amount (GST Exc)	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20
2	Insurance	31/10/2019	31/10/2020	367	63,073.17	171.86	5,155.84	5,327.71	5,327.71	4,983.98	5,327.71	5,155.84	5,327.71	5,155.84
3	Software - Annual Maintenance	1/12/2019	30/11/2020	366	5,325.00	-	-	451.02	451.02	421.93	451.02	436.48	451.02	436.48
4	SUBSCRIPTION	5/12/2019	5/12/2020	367	1,215.00	-	-	89.39	102.63	96.01	102.63	99.32	102.63	99.32
5	Insurance policy	10/12/2019	31/10/2020	327	1,058.33	-	-	71.20	100.33	93.86	100.33	97.09	100.33	97.09
6	Insurance policy	10/12/2019	31/10/2020	327	1,911.41	-	-	128.60	181.20	169.51	181.20	175.36	181.20	175.36
7	Service Contract	21/12/2019	20/12/2020	366	2,627.00	-	-	78.95	222.51	208.15	222.51	215.33	222.51	215.33
8	Subscription	13/02/2020	13/02/2021	367	2,400.00	-	-	-	-	111.17	202.72	196.19	202.72	196.19
9	Subscription	17/02/2020	17/02/2021	367	3,100.00	-	-	-	-	109.81	261.85	253.41	261.85	253.41
10	Insurance	6/03/2020	31/10/2020	240	3,193.04	-	-	-	-	-	345.91	399.13	412.43	399.13
11	Service Contract	1/05/2020	30/04/2021	365	2,510.00	-	-	-	-	-	-	-	213.18	206.30
12	Annual Subscription	4/05/2020	4/05/2021	366	4,801.60	-	-	-	-	-	-	-	367.34	393.57
13
14			Monthly Prepayment Expired			171.86	5,155.84	6,146.87	6,385.40	6,194.42	7,195.89	7,028.14	7,842.93	7,628.02
15
16			Prepayment Balance at Month-End
Sheet1 (4)

I'm looking for a formula in row 16 to calculate the closing balance at each month end date. Is anyone able to assist?

Thank you.