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.

Start Date Finish Date Days Amount 31/08/2017 30/09/2017 31/10/2017 30/11/2017 31/12/2017 31/01/2018 Rates 15/08/2017 22/01/2018 161 10000 994 1925 1863 1925 1925 1366 <colgroup><col><col><col><col><col><col span="6"></colgroup><tbody> </tbody>

<colgroup><col><col><col><col><col><col span="6"></colgroup><tbody>
</tbody>

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
1		Start	Finish	Days	Amount	31/08/2017	30/09/2017	31/10/2017	30/11/2017	31/12/2017	31/01/2018
2	Rates	15/08/2017	22/01/2018	160	10,000	1,000	1,875	1,938	1,875	1,938	1,375

<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>
</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...

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.

 

[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