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.

Vendor Invoice Date PPD Amount Beg Date End Date Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Microsoft 6/1/2019 15,000.00 7/15/2019 7/14/2020 <colgroup><col><col><col><col><col><col span="12"></colgroup><tbody> </tbody>

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

 

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

Vendor

Invoice Date

PPD Amount

Beg Date

End Date

Jan-19

Feb-19

Mar-19

Apr-19

May-19

Jun-19

Jul-19

Aug-19

Sep-19

Oct-19

Nov-19

Dec-19

Microsoft

6/1/2019

15,000.00

7/15/2019

7/14/2020

-

-

-

-

-

-

534.25

534.25

1,232.88

534.25

493.15

534.25

<colgroup><col><col><col><col><col><col span="8"><col><col span="3"></colgroup><tbody>
</tbody>