Prorate price formula

krguyton

New Member
Joined
Dec 21, 2018
Messages
13
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

Untitled.png


Thank you.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Welcome to the Forum!

Perhaps something like this:

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


Book1
ABC
1Monthly price$100
2
3Start dateBilling monthAmount
412 Dec 2018Dec 201813.33
518 Dec 2018Dec 20180.00
618 Dec 2018Jan 201993.55
Sheet1
 
Last edited:
Upvote 0
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.
P6thWSg
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top