Split a value across FY's based on start and end dates

[hindk]

I'm trying to determine the impact of hardware/software maintenance agreements on each financial year.

Eg.
Start date 1 May 2011
End date 30 June 2013
Total Value $850,000

I need to know the impact for 2010/11, 2011/12 & 2012/13 to help with budget development for future years.

I've tried taking it down to a daily cost and then multiplying it out but this still requires some work as each item has a different start and end date.

If anyone has a suggestion for formulas it would be greatly appreciated.

Thanks.

 

[anandvarma]

TRY THIS

Excel Workbook
	A	B	C	D	E	F
1	Date	Cost of Maintenance	criteria
2			01 May 2010	01 May 2011	01 May 2012	01 May 2013
3			30 April 2011	30 April 2012	30 April 2013	30 June 2013
4
5			0	0	0	0
Sheet1

 

[hindk]

Thanks for your quick response anandvarma!

That is almost what I'm after, maybe if I explain it another way.

E.g. A simple example would be where the cost of the maintenance agreement is $850,000 for 2 years.
If it was for period 1 July 2011 to 30 June 2013 the cost would be $425,000 for 2011/12 and $425,000 for 2012/13.

I want Excel to split the full amount across all years in the date range.

 

[moonfish]

So I guess your fiscal year starts on july 1st?

 

[hindk]

Yes 1 July - 30 June

 

[moonfish]

The formula has become a bit of a beast but here you have it:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Start Date	End Date	duration	Value	*	7/1/2010	7/1/2011	7/1/2012	7/1/2013	7/1/2014
2	5/1/2011	6/30/2013	2.16	$ *850,000.00	*	$ * *65,501.95	$ *393,011.72	$ *360,260.74	$ * * * * * * * * * *- *	*
3	8/1/2011	6/1/2012	0.83	$ *500,000.00	*	$ * * * * * * * * * *- *	$ *500,000.00	$ * * * * * * * * * *- *	$ * * * * * * * * * *- *	*
Sheet1

Copy the formula in F2 across the entire time line (in this example, F2:I3).

 

[hindk]

Thanks, just what I needed. Really appreciate your help and quick responses.

 

[moonfish]

Just so you know, I'm currently looking for an error in the formula. The resulting split doesn't add up to $850k

 

[moonfish]

Found it, this is the revised formula:

=$D2*IF(AND($A2 >= F$1,$A2 < G$1),IF(AND($B2 >= F$1,$B2 < G$1),1,(YEAR(G$1)-YEAR($A2)+(MONTH(G$1)-MONTH($A2))/12+(DAY(G$1)-DAY($A2))/365)/$C2),IF(AND($B2 >= F$1,$B2 < G$1),(YEAR($B2)-YEAR(F$1)+(MONTH($B2)-MONTH(F$1))/12+(DAY($B2)-DAY(F$1))/365)/$C2,IF(AND($A2 <= F$1,$B2 > F$1),1/$C2,0)))

 

[hindk]

Thanks!