Creating a formula to prorate amounts based on Dates

[neilangelo]

I am trying to create a formula in excel that carries out the following functions.

I operate a gym and members typically pay me either for 1 month, 3 months or 1 year. The Gym's financial year end is March 31 2005 . Now when a Gym member pays me for 1 year say at January 2005 then only 3 months of revenue can be reported up to March 31, 2005, ie the membership is prorated ie 3/12 of the membership amount. The remaining 9/12 will be a balance for the next year.

How do I create a formula that when I input the start date and the end date of the membership, it will atomatically calculate the amount to be included up to March 31, 2005 and then the balance that goes into the next year.

Thanks

 

[RalphA]

Here is one solution, assuming you are using the mm/dd/yy format:

Say you have:
In A9, "Start Date" and in A10 the Start date
In B9, "End Date", and in B10, the End date

In C8, enter "Up To", and in C9, 03/31/05
In D8, enter "Past", and in D10, 03/31/05

Now enter the solution formulas, as follows:
In C10, enter: =IF(B10-A10>C9-A10,C9,B10)
In D10, enter: =IF(B10-A10>C9-A10,B10,"")
and copy down C10:D10.

Let us know if this solves your problem.

 

[neilangelo]

I tried using your formula however it returns a date. What I am trying to achieve is outlined as follows:

1) If a member pays me $100 in January 2005 for a 12 month membership , then I would earns $25.00from January to March 2005 ie 3/12 of $100. The remaining 9/12 or $75 would be for the next financial year.

2) Scenario 2 is the same $100 however the member pays me for three months. In this case I would have earned the $100 in the financial year up to March 31, 2005. Nothing would have been brought over to the next financial year

So basically I want to enter a Start Date, The end Date and the membership amount and I want the formula to then calculate the amount earned up to March 31, 2005 and then the amount that will be earned after March 31, 2005 (The next financial year).

For the purposes of my calculation I am dealing with full months, so Jan 10 to March 31, 2005 would be 3 months, so the amounts of days does not matter.

Thanks

 

[RalphA]

Try this:
A1 = "Paid"
A2 is formatted as mm-yy, and holds the payment date, say, Jan-05
A3 has the amount paid, say $100.00

B1:C1 is merged, and = "Membership"
B2="Months"
B3 is formatted as Number, with no decimal places, and is, say, 12

C2 = "Dues"
C3 is formatted as currency, $, and has the amount required, $100.00

D1 = "Year Ends"
D2 is formatted as mm-yy, and holds the year-ending date, say, Mar=05
D3 is formatted as currency, $, and has the formula:
=IF(A3>=C3,C3/12*(MONTH(D2-A2)+1),"Payment too small")
E1 = "Paid To"
E2 is formatted as mm-yy, and has the formula:
=IF(A3=D3,"", A2+INT(365*A3/C3))
E3 is formatted as currency, $, and has the formula:
=IF(A3<=D3,"",INT((A3-D3)/(C3/B3))*C3/B3)

F2 = "Change"
F3 is formatted as currency, $, and has the formula:
=A3-(D3+E3)

Hopefully, this will do it for you.