Formula to calculate pro-rate + 1 month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mrsteveo - 09 Oct 2007 23:46 GMT

Hi all,

I'm not an Excel expert but I am wondering how I might accomplish the
following.

I work at an ISP and lets assume a customer wants our 256 x 256
package which is $31.00 month.

Obviously, if they sign up on say the 10th of the month, our billing
system bills them the 10th - End of Month + 1 FULL MONTH.

I'm wondering how I can put say $31.00 + $22.95 (phone service charge
per month) into a formula and have it figure out the pro-rate
depending on which date I use + the 1 full month? Make sense?

I need a formula to calculate pro-rate (so let's use the 10th) 10th
through end of the month + 1 full month and have different figures/
sums depending where in the cycle it falls.

I hope this makes sense. Let me know if I need to clarify and if you
have any ideas on how to accomplish this I am all ears.

Kind regards and many thanks in advance,

Steve Ryan

Reply to this Message

Peo Sjoblom - 10 Oct 2007 00:02 GMT

For October if the customer started his/her plane on the 10th and the price
is in A1

=(DATE(2007,10+1,0)-DATE(2007,10,10)+1)*(A1/DAY(DATE(2007,10+1,0)))

with the month number in B1 and the day in C1

=(DATE(2007,B1+1,0)-DATE(2007,B1,C1)+1)*(A1/DAY(DATE(2007,B1+1,0)))

it's important to format the result as currency or number or else you will
get a pseudo date

Signature

Regards,

Peo Sjoblom

> Hi all,
>
[quoted text clipped - 21 lines]
>
> Steve Ryan

Reply to this Message

Tyro - 10 Oct 2007 00:35 GMT

This formula calculates the day of today's date 10/9/2007, divided by the
number of days in October, 31 to give a proration multiplier of 9/31 =
.290323.

=DAY(TODAY())/DAY(EOMONTH(TODAY(),0))
So, your full formula is: =DAY(TODAY())/DAY(EOMONTH(TODAY(),0))*($31.00 +
$22.95) + $31.00 + $22.95 which equals $69.61 if service starts on
10/9/2007.

You can leave out the $ signs and the decimal points. Also you can put the
amounts $31 in cells A1 and $22.95 in B1 and use
=DAY(TODAY())/DAY(EOMONTH(TODAY(),0))*(A1 + B1) + A1 + B1

You have to install the Analysis Toolpak for Excel versions prior to Excel
2007. The EOMONTH function is standard in Excel 2007. You can, of course,
put any date in a cell, say A1 and substitute A1 in the formula for TODAY().
Then after prorating, you simply add one month's fees to that result.

> Hi all,
>
[quoted text clipped - 21 lines]
>
> Steve Ryan

Reply to this Message

Tyro - 10 Oct 2007 00:49 GMT

Sorry, formula is wrong. It should be
=(DAY(EOMONTH(TODAY(),0))-DAY(TODAY())+1)/DAY(EOMONTH(TODAY(),0)) to
calculate the multiplier
.
It should be =DAY(TODAY())/DAY(EOMONTH(TODAY(),0))*(31 + 22.95) + 31 + 22.95

> This formula calculates the day of today's date 10/9/2007, divided by the
> number of days in October, 31 to give a proration multiplier of 9/31 =
[quoted text clipped - 40 lines]
>>
>> Steve Ryan

Reply to this Message