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

 

[njimack]

Why is October (31 days) lower than Sep and Nov (30 days)

 

[adamski911]

Good point.This is just demonstration as I think my formula must of been wrong.
Should be 10000/161 * 31 = 1925 for Oct and not the 30 day months

 

[njimack]

Here's what I came up with...

Sheet1

	A	B	C	D	E	F	G	H	I	J	K
	Start	Finish	Days	Amount
Rates

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:68px;"><col style="width:76.5px;"><col style="width:76.5px;"><col style="width:68px;"><col style="width:68px;"><col style="width:77.5px;"><col style="width:76.5px;"><col style="width:76.5px;"><col style="width:76.5px;"><col style="width:76.5px;"><col style="width:76.5px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/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]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]15/08/2017[/TD]
[TD="align: right"]22/01/2018[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"] 10,000 [/TD]
[TD="align: right"] 1,000 [/TD]
[TD="align: right"] 1,875 [/TD]
[TD="align: right"] 1,938 [/TD]
[TD="align: right"] 1,875 [/TD]
[TD="align: right"] 1,938 [/TD]
[TD="align: right"] 1,375 [/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F1 =EOMONTH(B2,0) G1 =EOMONTH(F1,1) H1 =EOMONTH(G1,1) I1 =EOMONTH(H1,1) J1 =EOMONTH(I1,1) K1 =EOMONTH(J1,1) D2 =C2-B2 F2 =$E2/$D2*(MIN(F1,$C2)-MAX(EOMONTH(F1,-1),$B2)) G2 =$E2/$D2*(MIN(G1,$C2)-MAX(EOMONTH(G1,-1),$B2)) H2 =$E2/$D2*(MIN(H1,$C2)-MAX(EOMONTH(H1,-1),$B2)) I2 =$E2/$D2*(MIN(I1,$C2)-MAX(EOMONTH(I1,-1),$B2)) J2 =$E2/$D2*(MIN(J1,$C2)-MAX(EOMONTH(J1,-1),$B2)) K2 =$E2/$D2*(MIN(K1,$C2)-MAX(EOMONTH(K1,-1),$B2)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Marcelo Branco]

Maybe...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD]

Start Date​

[/TD]
[TD]

Finish Date​

[/TD]
[TD]

Days​

[/TD]
[TD]

Amount​

[/TD]
[TD]

31/08/2017​

[/TD]
[TD]

30/09/2017​

[/TD]
[TD]

31/10/2017​

[/TD]
[TD]

30/11/2017​

[/TD]
[TD]

31/12/2017​

[/TD]
[TD]

31/01/2018​

[/TD]
[TD]

28/02/2018
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Rates​

[/TD]
[TD]

15/08/2017​

[/TD]
[TD]

22/01/2018​

[/TD]
[TD]

161​

[/TD]
[TD]

10000​

[/TD]
[TD]

1055,90​

[/TD]
[TD]

1863,35​

[/TD]
[TD]

1925,47​

[/TD]
[TD]

1863,35​

[/TD]
[TD]

1925,47​

[/TD]
[TD]

1366,46​

[/TD]
[TD]

0,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[Marcelo Branco]

Forgot to say
Format F2:L2 as Number with 2 decimal places

M.

 

[adamski911]

Hi,

This looks great however I should be calculating 161 days as there will be 161 if you include the day it started. ie August would be 17 as I am including the 15th as first day of cost if that makes sense?

Also,is there any way of returning 0 in the following months or previous months when copying the formula across rather than returning negative amounts ie IF statement or something?

Thanks

 

[Marcelo Branco]

Hi,

This looks great however I should be calculating 161 days as there will be 161 if you include the day it started. ie August would be 17 as I am including the 15th as first day of cost if that makes sense?

Also,is there any way of returning 0 in the following months or previous months when copying the formula across rather than returning negative amounts ie IF statement or something?

Thanks

The formula in post 5 does exactly what you need (17 days in August, 0 in the following months)

M.

 

[njimack]

Give this a whirl. Note the slightly different formula in column F, to include the start date as an additional day.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Start	Finish	Days	Amount	31/08/2017	30/09/2017	31/10/2017	30/11/2017	31/12/2017	31/01/2018	28/02/2018	31/03/2018	30/04/2018	31/05/2018
2	Rates	15/08/2017	22/01/2018	161	10,000	1,056	1,863	1,925	1,863	1,925	1,366	-	-	-	-
Sheet1

 

[adamski911]

Thanks Marcelo,

This is what I was looking for