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.

 

[StephenCrump]

Welcome to the Forum!

Perhaps something like this:

C4: =MEDIAN((EOMONTH(B4,-1)+16-A4)/DAY(EOMONTH(B4,-1)),0,1)*MonthlyPrice

Book1
	A	B	C
1	Monthly price	$100
2
3	Start date	Billing month	Amount
4	12 Dec 2018	Dec 2018	13.33
5	18 Dec 2018	Dec 2018	0.00
6	18 Dec 2018	Jan 2019	93.55
Sheet1

 

[krguyton]

Thanks!

 

[krguyton]

I am having trouble with this formula and the 'Unit PR Cost'. I will give an example. It seems that the formula is assuming there are 28 days in the month instead of 30. I want to force each month to have 30 days. So February will be looked at with 30 days instead of 28, March will be looked at for 30 days instead of 31 and such. As you can see from the picture. My Unit PR Cost shows $2.86 when it should be $2.67.

 

[krguyton]

H

ere is the photo

 

[StephenCrump]

You'll need to be give some more thought to how you want your 360 day calendar to work ...

What if the effective date is, say, 15 Feb 2019, 16 Feb 2019, or 28 Feb 2019?

 

[krguyton]

You'll need to be give some more thought to how you want your 360 day calendar to work ...

What if the effective date is, say, 15 Feb 2019, 16 Feb 2019, or 28 Feb 2019?

Is there a way to ignore the specifics of the month? I want to treat every month the same (30 days) the only reason I would need to specify the month is because of where it falls during the year. If the effective date is on Feb 15 then the next bill day would be 30 days after that, Mar 15. If the effective day was Feb 28, then the next bill day would be March 28.

 

[StephenCrump]

Is there a way to ignore the specifics of the month? I want to treat every month the same (30 days) the only reason I would need to specify the month is because of where it falls during the year. If the effective date is on Feb 15 then the next bill day would be 30 days after that, Mar 15. If the effective day was Feb 28, then the next bill day would be March 28.

Sorry, I'm lost now.

You have only provided one example so far.

Perhaps a few more examples will help us understand what you're trying to achieve, thanks.

 

[krguyton]

Sorry, I'm lost now.

You have only provided one example so far.

Perhaps a few more examples will help us understand what you're trying to achieve, thanks.

We bill for services on a monthly basis. No matter how many days are in the month, we prorate the prices for a 30 day period. If a client starts a service on March 11th, we bill them on March 15th for a prorate based on 4 days since our billing cycles are on the 15th of every month. Even though march has 31 days we want to prorate as if the month were only 30 days. Lets say the service price is 30 dollars a month. We want to prorate them at a rate of a dollar a day, therefore they are charged only 4 dollars at the end of the cycle. Using the example I provided the prorate price should be $2.67 for the month, not $2.86. The Unit price is $20. ($20 price/30 days)*4 days left in billing cycle=$2.67. If the effective date was March 30 we will be them on April 15th but for only 15 days instead of 16 even though march has 31 days.

 

[StephenCrump]

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?