Excel gurus - Prorate formula?

[jdoggg12]

I'm wanting something like this:

A1 - Plan start/pre-purchase date
A2 - Plan purchase price
A3 - Plan cancel date
A4 - Formula to calculate prorated amount due

What formula will do this so that it is completely dynamic and prorates to the day? I'm good with excel - but for some reason i'm terrible when it comes to implementing dates in to formulas.

Thanks!

 

[pulse8]

=$A2/($A3-$A1)

?

Edit: Make sure the cell is defined as a number and not a date.

 

[KLin]

Is interest being applied?

 

[jdoggg12]

Originally posted by: pulse8
=$A2/($A3-$A1)

?

Edit: Make sure the cell is defined as a number and not a date.

I wish it were that easy... but no. Try your formula.
Look at it like this:

You buy a gym membership on Jan 1, 07, prepaid for the year. You Cancel it on July 1, 07. Your formula doesn't calculate the prorated refund due.

Originally posted by: KLin
Is interest being applied?

Nope, straight fee for the purchase, no additional fees upon cancellation.

 

[Kelemvor]

If it's a 1 year thing then you can use the datedif command to get the number of days between the two dates. Then divide that into 365 to get the percentage of the year useed and then just multiply it out.
Here you go:

=((365-DATEDIF(A1,C1,"D"))/365*B1)

Assucming it's setup like

1/1/08 - 100 - 3/1/08

 

[pulse8]

Originally posted by: jdoggg12

Originally posted by: pulse8
=$A2/($A3-$A1)

?

Edit: Make sure the cell is defined as a number and not a date.

I wish it were that easy... but no. Try your formula.
Look at it like this:

You buy a gym membership on Jan 1, 07, prepaid for the year. You Cancel it on July 1, 07. Your formula doesn't calculate the prorated refund due.

Originally posted by: KLin
Is interest being applied?

Nope, straight fee for the purchase, no additional fees upon cancellation.

You didn't offer up the extra information. I made the formula with the information you provided.

 

[LegendKiller]

A5=A2-((A3-A1)/(A4-A1))*A2

A4 = Normal plan termination date of 12/31/0X

 

[jdoggg12]

Hmm... i've tried all the suggested formulas.

Here are a couple outputs that were automatically calculated by the system i'm using - because it's not retro active to a previous date, i can do it without crunching numbers manually. I need the formula for when i back date things.

Purchase:------ $19.95 Jan 2, 2008 (paid through Feb 1, 2008)
Cancellation:---Jan 3, 2008 (1 day proration)

Amount due: $19.31

Purchase:------ $19.95 Dec 28, 2007 (paid through Jan 28, 2008)
Cancellation:---Jan 3, 2008

Amount due: $16.09

Purchase:------ $119.40 Feb 28, 2007 (paid through Feb 28, 2008)
Cancellation:---Jan 3, 2008

Amount due: $18.92

I can't get any of the formulas to match (close to) those figures. Can anyone verify or modify the formulas to do this?

Thanks again guys!

 

[OdiN]

Assuming you are taking your rate plan price and dividing it by 365 days/year to get a daily rate, then this would work:

=(A3-A1)*(A2/365)

If you are using a monthly rate:

=(A3-A1)*((A2 * 12) / 365)

Problem is that for monthly it won't be exact as the # of days per month differs. So charging say $26.99 per month for a 31 day month is a different daily price than $26.99 in a 30 day month.

Slightly off, but for your purposes, you could say that proration is calculated based on a daily rate and not a monthly rate.

If you don't count the cancellation date in the rate you can subtract 1 i.e. ((A3-A1)-1)


But I'm not sure this is what you're looking for.

For a monthly rate, you would need to specify the beginning of the last month in which the plan was cancelled, not the start of the plan. For example, if the rate was 26.99 per month, and they paid for 4 months, but only 5 days worth of the 5th month, then you would start your calculation with 05/01/2008 and end it with 05/05/2008 to get the additional amount owed which was not already paid.


So unless you have a different way of calculating your prorated rates then that should work.

 

[KLin]

Originally posted by: jdoggg12
Hmm... i've tried all the suggested formulas.

Here are a couple outputs that were automatically calculated by the system i'm using - because it's not retro active to a previous date, i can do it without crunching numbers manually. I need the formula for when i back date things.

Purchase:------ $19.95 Jan 2, 2008 (paid through Feb 1, 2008)
Cancellation:---Jan 3, 2008 (1 day proration)

Amount due: $19.31

Purchase:------ $19.95 Dec 28, 2007 (paid through Jan 28, 2008)
Cancellation:---Jan 3, 2008

Amount due: $16.09

Purchase:------ $119.40 Feb 28, 2007 (paid through Feb 28, 2008)
Cancellation:---Jan 3, 2008

Amount due: $18.92

I can't get any of the formulas to match (close to) those figures. Can anyone verify or modify the formulas to do this?

Thanks again guys!

You need another cell value to calculate the percentage due based on the total number of days paid for vs. number of days up until the cancellation date.

A1 - StartDate
A2 - Price
A3 - EndDate
A4 - CancelDate
A5 - ((A3-A1)-(A4-A1))/(A3-A1)*A2

 

[OdiN]

Originally posted by: KLin

Originally posted by: jdoggg12
Hmm... i've tried all the suggested formulas.

Here are a couple outputs that were automatically calculated by the system i'm using - because it's not retro active to a previous date, i can do it without crunching numbers manually. I need the formula for when i back date things.

Purchase:------ $19.95 Jan 2, 2008 (paid through Feb 1, 2008)
Cancellation:---Jan 3, 2008 (1 day proration)

Amount due: $19.31

Purchase:------ $19.95 Dec 28, 2007 (paid through Jan 28, 2008)
Cancellation:---Jan 3, 2008

Amount due: $16.09

Purchase:------ $119.40 Feb 28, 2007 (paid through Feb 28, 2008)
Cancellation:---Jan 3, 2008

Amount due: $18.92

I can't get any of the formulas to match (close to) those figures. Can anyone verify or modify the formulas to do this?

Thanks again guys!

You need another cell value called PaidThroughDate to calculate the percentage due based on the total number of days paid for vs. number of days up until the cancellation date.

Or you just subtract the calculated value from the total paid - cancellation before the end of the contract would end up in a refund if you provide refunds.

 

[Jiggz]

To get a pro-rated cost between paid dates, you should divide the total amount paid by the total number of days covered. This will give you the daily cost rate. If a member cancels before the paid end dates, subtract the start date from the cancellation date and then multiplied by the daily cost rate and it should give you the pro-rated cost of the days or period used.
For example: Paid $31.00 (Start date: Jan. 01 2008 - End date: Jan 31 2008).
End Date - Start Date: (31 - 1) + 1 [to make the date inclusive) = 31 days.
Daily Pro-rate: $31 / 31 = $1.00
Member cancels Jan 15 2008.
Amount due: [Period used = (Cancellation Date - Start Date) + 1 (to make it inclusive)] X Daily Pro-rate
= [(15 - 1) + 1] X $1.00 = $15.00

I know the numbers and amount I chose are proportional but it's no different if they are not.