Prorate price formula

[krguyton]

Hello, I am trying to create a spreadsheet that will calculate a prorated price based on an effective date. I had found this forum by searching the issue where someone proposed this formula:

=A2*(DAY(EOMONTH(B2,0))-DAY(B2)+1)/DAY(EOMONTH(B2,0))

The problem is we use a billing cycle starting from the 16th of the current month to the 15th of the next month.

Here is the layout I am working with

Thank you.

 

[krguyton]

I thought from Post#5 that your $267 was actually $16 x 5/30?

However, if 11 March to 15 March is 4 days, and not 5 inclusive, then presumably based on a monthly fee of $30 and the effective dates below, you want fees of:

...
16 Feb 2019 --> $27
15 Feb 2019 --> $28
14 Feb 2019 --> $30
13 Feb 2019 --> $30

And where months are longer than 30 days, e.g. for 15 April billing:

17 Mar --> $29
16 Mar --> $30
15 Mar --> $30

Is this OK, or did you want to use Excel's Days360 function, which will produce different discontinuities to fit the 360 day calendar?

Sorry I got caught up in another project. I was mistaken earlier. You are correct the prorate cost is the unit cost divided by 30 days times the remaining days in the billing cycle including the effective day. So for the example it is 5 days ((16/30)*5)=$2.67 and I need to treat every month as if it had 30 days in it. Billing cycles are on the 15th of every month.