Formulas for pro rata with dates

Hi All,

I am racking my brain trying to work out a suitable formula to work out a flow on effect.

Situation is we supply funding to individuals for 12 month periods with varing start dates, our centralised funding is run on the financial year 1st July to 30 June.

What I need a formula to work out is if $10,000 funding is started on 15 January 2016 what pro rata amount will be spent in the 2015-2016 financial year and what amount will flow into the 2016-2017 financial year.

Thank you in advance for any help you can give me.

Cheers

Vanessa

Step 1: The End Date (30/06/2016) less the Start Date (15/01/2016) +1 = 168 days
Step 2: The amount of $10,000 / 365 * 168 = $4,602.74 ... so that much in allocated to this financial year and ...
Step 3: The remainder of $5,397.26 is allocated to the next finacial year.

Thank you for this, Unfortunatley this was well beyond my level of understanding, this could very possibly be because I have been drowning in funding all week and complex formuls are not a strong point of mine, I just could not get it to work.
I am going to have another look at it next week when my head is less foggy to see if I can nut it out and get it to work.

Thank you so much for your assistance

Originally Posted by gak67

It's not an elegant formula, and somebody may be able to improve it, but it should work:

Formula:

Please Login or Register  to view this content.

This will give the amount of the funding that applies to the financial year in which it commenced.

BAMM the problem is solved.

Thank you so much, this was the perfect way to step out the instructions for me, I'm not good with these functions in excel so going right back to basics in simple language was just what I needed. You have saved me from pulling my hair out...

Originally Posted by Ursul

Step 1: The End Date (30/06/2016) less the Start Date (15/01/2016) +1 = 168 days
Step 2: The amount of $10,000 / 365 * 168 = $4,602.74 ... so that much in allocated to this financial year and ...
Step 3: The remainder of $5,397.26 is allocated to the next finacial year.

Based on Ursul's clear explanation of the calculation:

A2=Start Date (of loan)
B2= Financial Year end
C2=Amount of Loan
D2=2015-2016 amount =ROUND(C2*((B2-A2)+1)/365,2)
E2 =2016-2017 amount =C2 - D2

See attached

Oh wow, I did it the long way, this cuts out a step, once again a very clear basic explaination that very easy to follow and great to see in action.

Thank you so much, I feel a bit silly now seeing how basic it was

Originally Posted by JohnTopley

Based on Ursul's clear explanation of the calculation:

A2=Start Date (of loan)
B2= Financial Year end
C2=Amount of Loan
D2=2015-2016 amount =ROUND(C2*((B2-A2)+1)/365,2)
E2 =2016-2017 amount =C2 - D2

See attached

Glad to have helped: we all have a "bad at the office" sometime !!!

If you have a solution could you please mark the thread as solved ("Thread Tools" at top of first post).