Apportion costs over financial years

[DCrake]

Hoping not to re-invent the wheel, what I am attempting to do is to take three arguments

LowerDate, UpperDate & Value

Then depending on whether the date range crosses the financial year boundary apportion the value pro-rata based on the number of days. So lets say I have a payment for £10000 to make and I raise the payment today but the payment is in respect of rent for the next 12 months. My financial year (for brevity is 1st Jan). Therefore I calculate the number of days between 1st Sep 2010 and 30th Aug 2011 = n. Then I calculate the date diff between 1st Sep and 31st Dec = x. finally calculate the date diff between 1st Jan and 30th Aug = y.

v = Value

Year one cost = v/(x+y)*x
Year two cost = v/(x+y)*y

Thats the logic, now heres where it get complicated. What happens if the payment covers more than dips it toe into more than 2 financial years. Also need to take into account leap years so the apportionment is correct. (my client insists on this).

Has anyone been down this route before? and if so what was your solution.

 

[namliam]

Isnt it easier/more intuative to assign all costs into months, for any give booking for any given period? then have the totals per month/period simply query them...

Only thing you probably need to do is keep track of the original payment/booking vs the split ones..... But totaling by financial year all of a sudden becomes a lot easier and much less to worry about.

 

[DCrake]

The way they do it at present is to raise a debtor payment request for x amount of money. Then they complete the start and end period that they want to opportion this money to. As an example a contractor may submit his invoice for 2 weeks work for week 52 of 2010/11 and week 1 of 2011/12. Therefore only 1 portion of it belongs in the relevant fin year. I need a clever way for it automate this as explained in my opening post. I understand what you are saying but when in Rome....