Prepaid Schedule

[thx4nuttin]

I am trying to create a prepaid schedule where the beginning and ending dates are mid-month resulting in pro-rated amounts at the beginning and end of the payment schedule. Can someone please help this frazzled person figure the formulas out for my spreadsheet?? I would be most appreciative.

[TABLE="width: 1221"]
<colgroup><col><col><col><col><col><col span="12"></colgroup><tbody>[TR]
[TD][TABLE="width: 1278"]
<colgroup><col><col><col><col><col><col span="12"></colgroup><tbody>[TR]
[TD]Vendor[/TD]
[TD]Invoice Date [/TD]
[TD]PPD Amount[/TD]
[TD] Beg Date[/TD]
[TD] End Date[/TD]
[TD]Jan-19[/TD]
[TD]Feb-19[/TD]
[TD]Mar-19[/TD]
[TD]Apr-19[/TD]
[TD]May-19[/TD]
[TD]Jun-19[/TD]
[TD]Jul-19[/TD]
[TD]Aug-19[/TD]
[TD]Sep-19[/TD]
[TD]Oct-19[/TD]
[TD]Nov-19[/TD]
[TD]Dec-19[/TD]
[/TR]
[TR]
[TD]Microsoft[/TD]
[TD="align: right"]6/1/2019 [/TD]
[TD] 15,000.00[/TD]
[TD="align: right"]7/15/2019[/TD]
[TD="align: right"]7/14/2020[/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]
[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[WaterGypsy]

Assuming you start in row one and column A then I think this might do the job if you put it in F2 then copy across

=($C2/($E2-$D2))*(IF(OR($D2>EOMONTH(F$1,0),$E2<f$1),0,1+(min(eomonth(f$1,0),$e2)-max(f$1,$d2+1))))<f$1),0,1+(min(eomonth(f$1,0),$e2)-max(f$1,$d2+1))))<f$1),0,1+(min(eomonth(f$1,0),$e2)-max(f$1,$d2+1))))

  
F$1),0,1+(MIN(EOMONTH(F$1,0),$E2)-MAX(F$1,$D2+1))))

<f$1),0,1+(min(eomonth(f$1,0),$e2)-max(f$1,$d2+1))))

I needs a < between the 2 parts
Sorry I can't get it to show correctly
</f$1),0,1+(min(eomonth(f$1,0),$e2)-max(f$1,$d2+1))))
</f$1),0,1+(min(eomonth(f$1,0),$e2)-max(f$1,$d2+1))))<f$1),0,1+(min(eomonth(f$1,0),$e2)-max(f$1,$d2+1))))<f$1),0,1+(min(eomonth(f$1,0),$e2)-max(f$1,$d2+1))))

 

[WaterGypsy]

= ($c2/($e2-$d2)) * (if (or ($d2>eomonth(f$1,0), $e2

then a less than followed by

F$1),0,1+(MIN(EOMONTH(F$1,0),$E2)-MAX(F$1,$D2+1))))

 

[thx4nuttin]

No, that returned some strange numbers.
[TABLE="width: 1264"]
<colgroup><col><col><col><col><col><col span="8"><col><col span="3"></colgroup><tbody>[TR]
[TD]Vendor[/TD]
[TD]Invoice Date[/TD]
[TD] PPD Amount[/TD]
[TD]Beg Date[/TD]
[TD]End Date[/TD]
[TD]Jan-19[/TD]
[TD]Feb-19[/TD]
[TD]Mar-19[/TD]
[TD]Apr-19[/TD]
[TD]May-19[/TD]
[TD]Jun-19[/TD]
[TD]Jul-19[/TD]
[TD]Aug-19[/TD]
[TD]Sep-19[/TD]
[TD]Oct-19[/TD]
[TD]Nov-19[/TD]
[TD]Dec-19[/TD]
[/TR]
[TR]
[TD]Microsoft[/TD]
[TD="align: right"]6/1/2019[/TD]
[TD] 15,000.00 [/TD]
[TD="align: right"]7/15/2019[/TD]
[TD="align: right"]7/14/2020[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 534.25[/TD]
[TD] 534.25[/TD]
[TD] 1,232.88[/TD]
[TD] 534.25[/TD]
[TD] 493.15[/TD]
[TD] 534.25[/TD]
[/TR]
</tbody>[/TABLE]