phasing savings across fiscal years - (attempt 2)

[ajm]

By this I mean, equally spreading a $$ saving amount over the months to which it will be applied. As an example, say we have a saving of $12k on the 1st November 2013 and it applies to 6 months. that would mean that in the Fiscal Year FY13 box (fiscal year here is 1 July to 30 June each year), number "6" shows up. Now, say this same saving actually applied to 12 months. FY13 would show "7" and FY14 would show "5". all well and good so far. How about if the same saving was to be applied over 23 months? FY13 shows "7", FY14 shows "12", and FY15 holds "4".

This last step is killing me.

Data at hand: Saving Start Date and the number of months until contract end, and the total saving.

Can anyone help with formulas that would help determine how many months in each fiscal year (last Fiscal Year, This Fiscal Year, Next Fiscal Year, and Next +1 Fiscal Year) a savings amount would be applied to given the data at hand?

I have some rather complicated formulas I have been working on which I will post as soon as I have added some explanations to them (my first attempt timed out ((http://www.mrexcel.com/forum/excel-questions/753749-phasing-savings-across-fiscal-years.html)), hence the "attempt 2" in the title). However, they may just be overcomplicating things (who me?)....

 

[ajm]

I have so far determined in which Fiscal year the saving starts. This identifies the box in which to place the first number.

[RANGE=cls:xl2bb-100][XR][XH=cs:12]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]G[/XH][XH]H[/XH][XH]I[/XH][XH]J[/XH][XH]K[/XH][XH]L[/XH][XH]V[/XH][XH]W[/XH][XH]X[/XH][XH]Y[/XH][XH]Z[/XH][/XR][XR][XH]1[/XH][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]ThisFY[/XD][XD=h:l]FY14[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][/XR][XR][XH]2[/XH][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][/XR][XR][XH]3[/XH][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][/XR][XR][XH]4[/XH][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:l]*[/XD][XD=h:c|c:ffffff|cls:fx][FORMULA=="30/06/"&RIGHT(ThisFY,2)-1]30/06/13[/FORMULA][/XD][XD=h:c|c:ffffff|cls:fx][FORMULA=="30/06/"&RIGHT(ThisFY,2)]30/06/14[/FORMULA][/XD][XD=h:c|c:ffffff|cls:fx][FORMULA=="30/06/"&RIGHT(ThisFY,2)+1]30/06/15[/FORMULA][/XD][XD=h:c|c:ffffff|cls:fx][FORMULA=="30/06/"&RIGHT(ThisFY,2)+2]30/06/16[/FORMULA][/XD][/XR][XR][XH]5[/XH][XD=h:l|fw:b]Remainder of term[/XD][XD=h:l|fw:b]Total term Savings[/XD][XD=h:l|fw:b]Date Of Modification[/XD][XD=h:l|fw:b]END FIN YEAR[/XD][XD=h:l|fw:b]Contract End Date[/XD][XD=h:l|fw:b]New Rate start date[/XD][XD=h:l|fw:b|bc:000080|c:ffffff]Benefit Start Date FY[/XD][XD=h:c|cls:fx][FORMULA=="Savings Months FY"&RIGHT(ThisFY,2)-1]Savings Months FY13[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=="Savings Months "&ThisFY]Savings Months FY14[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=="Savings Months FY"&RIGHT(ThisFY,2)+1]Savings Months FY15[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA=="Savings Months FY"&RIGHT(ThisFY,2)+2]Savings Months FY16[/FORMULA][/XD][/XR][XR][XH]6[/XH][XD=h:r]8[/XD][XD=h:r|cls:fx][FORMULA==F6*G6]$ *29,724.00[/FORMULA][/XD][XD=h:r]25/07/2013[/XD][XD=h:r]30-Jun-14[/XD][XD=h:r]14-Dec-13[/XD][XD=h:r]15-Apr-13[/XD][XD=h:c|fw:b|bc:ccffff|cls:fx][FORMULA=="FY"&RIGHT(IF(MONTH($L6)>6,YEAR($L6)+1,YEAR($L6)),2)]FY13[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==IF(OR($L6="-",$L6=""),"",IF($V6="FY"&(RIGHT(ThisFY,2)-1),(DATEDIF(IF(DAY($L6)<15,DATE(YEAR($L6),MONTH($L6),0)-1,DATE(YEAR($L6),MONTH($L6),0)+1),$W$4,"m")),0))]2[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==IF(OR($L6="-",$L6=""),"",IF($V6="FY"&(RIGHT(ThisFY,2)-1),MIN(12-$W6,$G6-$W6),IF($V6="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L6)<15,DATE(YEAR($L6),MONTH($L6),0)-1,DATE(YEAR($L6),MONTH($L6),0)+1),$X$4,"m")),0)))]10[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(OR($L6="-",$L6=""),"",IF($V6="FY"&RIGHT(ThisFY,2),12-$X6,(IF($V6="FY"&RIGHT(ThisFY,2)+1,(DATEDIF(IF(DAY($L6)<15,DATE(YEAR($L6),MONTH($L6),0)-1,DATE(YEAR($L6),MONTH($L6),0)+1),$Y$4,"m")),0))))]0[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(OR($L6="-",$L6=""),"",IF($V6="FY"&RIGHT(ThisFY,2)+1,12-$Y6,(IF($V6="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L6)<15,DATE(YEAR($L6),MONTH($L6),0)-1,DATE(YEAR($L6),MONTH($L6),0)+1),$Z$4,"m")),0))))]0[/FORMULA][/XD][/XR][XR][XH]7[/XH][XD=h:r]13[/XD][XD=h:r|cls:fx][FORMULA==F7*G7]$ * *5,825.43[/FORMULA][/XD][XD=h:l]*[/XD][XD=h:r]30-Jun-14[/XD][XD=h:r]9-Aug-14[/XD][XD=h:r]10-Jul-13[/XD][XD=h:c|fw:b|bc:ccffff|cls:fx][FORMULA=="FY"&RIGHT(IF(MONTH($L7)>6,YEAR($L7)+1,YEAR($L7)),2)]FY14[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==IF(OR($L7="-",$L7=""),"",IF($V7="FY"&(RIGHT(ThisFY,2)-1),(DATEDIF(IF(DAY($L7)<15,DATE(YEAR($L7),MONTH($L7),0)-1,DATE(YEAR($L7),MONTH($L7),0)+1),$W$4,"m")),0))]0[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==IF(OR($L7="-",$L7=""),"",IF($V7="FY"&(RIGHT(ThisFY,2)-1),MIN(12-$W7,$G7-$W7),IF($V7="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L7)<15,DATE(YEAR($L7),MONTH($L7),0)-1,DATE(YEAR($L7),MONTH($L7),0)+1),$X$4,"m")),0)))]12[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(OR($L7="-",$L7=""),"",IF($V7="FY"&RIGHT(ThisFY,2),12-$X7,(IF($V7="FY"&RIGHT(ThisFY,2)+1,(DATEDIF(IF(DAY($L7)<15,DATE(YEAR($L7),MONTH($L7),0)-1,DATE(YEAR($L7),MONTH($L7),0)+1),$Y$4,"m")),0))))]0[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(OR($L7="-",$L7=""),"",IF($V7="FY"&RIGHT(ThisFY,2)+1,12-$Y7,(IF($V7="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L7)<15,DATE(YEAR($L7),MONTH($L7),0)-1,DATE(YEAR($L7),MONTH($L7),0)+1),$Z$4,"m")),0))))]36[/FORMULA][/XD][/XR][XR][XH]8[/XH][XD=h:r]17[/XD][XD=h:r|cls:fx][FORMULA==F8*G8]$ * *2,737.00[/FORMULA][/XD][XD=h:l]*[/XD][XD=h:r]30-Jun-14[/XD][XD=h:r]19-Dec-14[/XD][XD=h:r]20-Jul-13[/XD][XD=h:c|fw:b|bc:ccffff|cls:fx][FORMULA=="FY"&RIGHT(IF(MONTH($L8)>6,YEAR($L8)+1,YEAR($L8)),2)]FY14[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==IF(OR($L8="-",$L8=""),"",IF($V8="FY"&(RIGHT(ThisFY,2)-1),(DATEDIF(IF(DAY($L8)<15,DATE(YEAR($L8),MONTH($L8),0)-1,DATE(YEAR($L8),MONTH($L8),0)+1),$W$4,"m")),0))]0[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==IF(OR($L8="-",$L8=""),"",IF($V8="FY"&(RIGHT(ThisFY,2)-1),MIN(12-$W8,$G8-$W8),IF($V8="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L8)<15,DATE(YEAR($L8),MONTH($L8),0)-1,DATE(YEAR($L8),MONTH($L8),0)+1),$X$4,"m")),0)))]11[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(OR($L8="-",$L8=""),"",IF($V8="FY"&RIGHT(ThisFY,2),12-$X8,(IF($V8="FY"&RIGHT(ThisFY,2)+1,(DATEDIF(IF(DAY($L8)<15,DATE(YEAR($L8),MONTH($L8),0)-1,DATE(YEAR($L8),MONTH($L8),0)+1),$Y$4,"m")),0))))]1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(OR($L8="-",$L8=""),"",IF($V8="FY"&RIGHT(ThisFY,2)+1,12-$Y8,(IF($V8="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L8)<15,DATE(YEAR($L8),MONTH($L8),0)-1,DATE(YEAR($L8),MONTH($L8),0)+1),$Z$4,"m")),0))))]35[/FORMULA][/XD][/XR][XR][XH]9[/XH][XD=h:r]18[/XD][XD=h:r|cls:fx][FORMULA==F9*G9]$ * *2,718.00[/FORMULA][/XD][XD=h:l]*[/XD][XD=h:r]30-Jun-14[/XD][XD=h:r]30-Jan-15[/XD][XD=h:r]31-Jul-13[/XD][XD=h:c|fw:b|bc:ccffff|cls:fx][FORMULA=="FY"&RIGHT(IF(MONTH($L9)>6,YEAR($L9)+1,YEAR($L9)),2)]FY14[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==IF(OR($L9="-",$L9=""),"",IF($V9="FY"&(RIGHT(ThisFY,2)-1),(DATEDIF(IF(DAY($L9)<15,DATE(YEAR($L9),MONTH($L9),0)-1,DATE(YEAR($L9),MONTH($L9),0)+1),$W$4,"m")),0))]0[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==IF(OR($L9="-",$L9=""),"",IF($V9="FY"&(RIGHT(ThisFY,2)-1),MIN(12-$W9,$G9-$W9),IF($V9="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L9)<15,DATE(YEAR($L9),MONTH($L9),0)-1,DATE(YEAR($L9),MONTH($L9),0)+1),$X$4,"m")),0)))]11[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(OR($L9="-",$L9=""),"",IF($V9="FY"&RIGHT(ThisFY,2),12-$X9,(IF($V9="FY"&RIGHT(ThisFY,2)+1,(DATEDIF(IF(DAY($L9)<15,DATE(YEAR($L9),MONTH($L9),0)-1,DATE(YEAR($L9),MONTH($L9),0)+1),$Y$4,"m")),0))))]1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(OR($L9="-",$L9=""),"",IF($V9="FY"&RIGHT(ThisFY,2)+1,12-$Y9,(IF($V9="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L9)<15,DATE(YEAR($L9),MONTH($L9),0)-1,DATE(YEAR($L9),MONTH($L9),0)+1),$Z$4,"m")),0))))]35[/FORMULA][/XD][/XR][XR][XH]10[/XH][XD=h:r]18[/XD][XD=h:r|cls:fx][FORMULA==F10*G10]$ * *2,718.00[/FORMULA][/XD][XD=h:l]*[/XD][XD=h:r]30-Jun-14[/XD][XD=h:r]30-Jan-15[/XD][XD=h:r]31-Jul-13[/XD][XD=h:c|fw:b|bc:ccffff|cls:fx][FORMULA=="FY"&RIGHT(IF(MONTH($L10)>6,YEAR($L10)+1,YEAR($L10)),2)]FY14[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==IF(OR($L10="-",$L10=""),"",IF($V10="FY"&(RIGHT(ThisFY,2)-1),(DATEDIF(IF(DAY($L10)<15,DATE(YEAR($L10),MONTH($L10),0)-1,DATE(YEAR($L10),MONTH($L10),0)+1),$W$4,"m")),0))]0[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==IF(OR($L10="-",$L10=""),"",IF($V10="FY"&(RIGHT(ThisFY,2)-1),MIN(12-$W10,$G10-$W10),IF($V10="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L10)<15,DATE(YEAR($L10),MONTH($L10),0)-1,DATE(YEAR($L10),MONTH($L10),0)+1),$X$4,"m")),0)))]11[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(OR($L10="-",$L10=""),"",IF($V10="FY"&RIGHT(ThisFY,2),12-$X10,(IF($V10="FY"&RIGHT(ThisFY,2)+1,(DATEDIF(IF(DAY($L10)<15,DATE(YEAR($L10),MONTH($L10),0)-1,DATE(YEAR($L10),MONTH($L10),0)+1),$Y$4,"m")),0))))]1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(OR($L10="-",$L10=""),"",IF($V10="FY"&RIGHT(ThisFY,2)+1,12-$Y10,(IF($V10="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L10)<15,DATE(YEAR($L10),MONTH($L10),0)-1,DATE(YEAR($L10),MONTH($L10),0)+1),$Z$4,"m")),0))))]35[/FORMULA][/XD][/XR][XR][XH]11[/XH][XD=h:r]4[/XD][XD=h:r|cls:fx][FORMULA==F11*G11]-$ * * *252.00[/FORMULA][/XD][XD=h:l]*[/XD][XD=h:r]30-Jun-14[/XD][XD=h:r]9-Nov-13[/XD][XD=h:r]10-Jul-13[/XD][XD=h:c|fw:b|bc:ccffff|cls:fx][FORMULA=="FY"&RIGHT(IF(MONTH($L11)>6,YEAR($L11)+1,YEAR($L11)),2)]FY14[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==IF(OR($L11="-",$L11=""),"",IF($V11="FY"&(RIGHT(ThisFY,2)-1),(DATEDIF(IF(DAY($L11)<15,DATE(YEAR($L11),MONTH($L11),0)-1,DATE(YEAR($L11),MONTH($L11),0)+1),$W$4,"m")),0))]0[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==IF(OR($L11="-",$L11=""),"",IF($V11="FY"&(RIGHT(ThisFY,2)-1),MIN(12-$W11,$G11-$W11),IF($V11="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L11)<15,DATE(YEAR($L11),MONTH($L11),0)-1,DATE(YEAR($L11),MONTH($L11),0)+1),$X$4,"m")),0)))]12[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(OR($L11="-",$L11=""),"",IF($V11="FY"&RIGHT(ThisFY,2),12-$X11,(IF($V11="FY"&RIGHT(ThisFY,2)+1,(DATEDIF(IF(DAY($L11)<15,DATE(YEAR($L11),MONTH($L11),0)-1,DATE(YEAR($L11),MONTH($L11),0)+1),$Y$4,"m")),0))))]0[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(OR($L11="-",$L11=""),"",IF($V11="FY"&RIGHT(ThisFY,2)+1,12-$Y11,(IF($V11="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L11)<15,DATE(YEAR($L11),MONTH($L11),0)-1,DATE(YEAR($L11),MONTH($L11),0)+1),$Z$4,"m")),0))))]36[/FORMULA][/XD][/XR][XR][XH]12[/XH][XD=h:r]6[/XD][XD=h:r|cls:fx][FORMULA==F12*G12]$ * *3,156.00[/FORMULA][/XD][XD=h:l]*[/XD][XD=h:r]30-Jun-14[/XD][XD=h:r]14-Jan-14[/XD][XD=h:r]15-Jul-13[/XD][XD=h:c|fw:b|bc:ccffff|cls:fx][FORMULA=="FY"&RIGHT(IF(MONTH($L12)>6,YEAR($L12)+1,YEAR($L12)),2)]FY14[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==IF(OR($L12="-",$L12=""),"",IF($V12="FY"&(RIGHT(ThisFY,2)-1),(DATEDIF(IF(DAY($L12)<15,DATE(YEAR($L12),MONTH($L12),0)-1,DATE(YEAR($L12),MONTH($L12),0)+1),$W$4,"m")),0))]0[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==IF(OR($L12="-",$L12=""),"",IF($V12="FY"&(RIGHT(ThisFY,2)-1),MIN(12-$W12,$G12-$W12),IF($V12="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L12)<15,DATE(YEAR($L12),MONTH($L12),0)-1,DATE(YEAR($L12),MONTH($L12),0)+1),$X$4,"m")),0)))]11[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(OR($L12="-",$L12=""),"",IF($V12="FY"&RIGHT(ThisFY,2),12-$X12,(IF($V12="FY"&RIGHT(ThisFY,2)+1,(DATEDIF(IF(DAY($L12)<15,DATE(YEAR($L12),MONTH($L12),0)-1,DATE(YEAR($L12),MONTH($L12),0)+1),$Y$4,"m")),0))))]1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(OR($L12="-",$L12=""),"",IF($V12="FY"&RIGHT(ThisFY,2)+1,12-$Y12,(IF($V12="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L12)<15,DATE(YEAR($L12),MONTH($L12),0)-1,DATE(YEAR($L12),MONTH($L12),0)+1),$Z$4,"m")),0))))]35[/FORMULA][/XD][/XR][XR][XH]13[/XH][XD=h:r]12[/XD][XD=h:r|cls:fx][FORMULA==F13*G13]$ *10,080.00[/FORMULA][/XD][XD=h:l]*[/XD][XD=h:r]30-Jun-14[/XD][XD=h:r]17-Aug-14[/XD][XD=h:r]17-Aug-13[/XD][XD=h:c|fw:b|bc:ccffff|cls:fx][FORMULA=="FY"&RIGHT(IF(MONTH($L13)>6,YEAR($L13)+1,YEAR($L13)),2)]FY14[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==IF(OR($L13="-",$L13=""),"",IF($V13="FY"&(RIGHT(ThisFY,2)-1),(DATEDIF(IF(DAY($L13)<15,DATE(YEAR($L13),MONTH($L13),0)-1,DATE(YEAR($L13),MONTH($L13),0)+1),$W$4,"m")),0))]0[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==IF(OR($L13="-",$L13=""),"",IF($V13="FY"&(RIGHT(ThisFY,2)-1),MIN(12-$W13,$G13-$W13),IF($V13="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L13)<15,DATE(YEAR($L13),MONTH($L13),0)-1,DATE(YEAR($L13),MONTH($L13),0)+1),$X$4,"m")),0)))]10[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(OR($L13="-",$L13=""),"",IF($V13="FY"&RIGHT(ThisFY,2),12-$X13,(IF($V13="FY"&RIGHT(ThisFY,2)+1,(DATEDIF(IF(DAY($L13)<15,DATE(YEAR($L13),MONTH($L13),0)-1,DATE(YEAR($L13),MONTH($L13),0)+1),$Y$4,"m")),0))))]2[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(OR($L13="-",$L13=""),"",IF($V13="FY"&RIGHT(ThisFY,2)+1,12-$Y13,(IF($V13="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L13)<15,DATE(YEAR($L13),MONTH($L13),0)-1,DATE(YEAR($L13),MONTH($L13),0)+1),$Z$4,"m")),0))))]34[/FORMULA][/XD][/XR][XR][XH=cs:12][RANGE][XR][XD]Lease Modifications[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

Col L New Rate Start Dates (when the savings start)
Col V Benefit Start Date FY (the Fin Year in which the Savings can be started)
Col G Remainder of Term (num,ber of months from New Rate Start Date until scheduled end of contract)
Col W, X, Y, Z Number of savings months for each of these Fiscal years
Cell W4 30/6/2013
Cell X4 30/6/2014
Cell Y4 30/6/2015
Cell Z4 30/6/2016

Named Range ThisFY = FY14

Headers are all in Row 5 with data starting Row 6


Formulas:
Savings Months FY13

=IF(OR($L6="-",$L6=""),"",IF($V6="FY"&(RIGHT(ThisFY,2)-1),(DATEDIF(IF(DAY($L6)<15,DATE(YEAR($L6),MONTH($L6),0)-1,DATE(YEAR($L6),MONTH($L6),0)+1),$W$4,"m")),0))

if there is no start date in $L6, return BLANK

Otherwise, if the Benefit Start Date FY is the same as Named Range ThisFY (which holds "FY14") less 1 (so FY13), give me the number of months between the New Rate Start Date and $W$4 which is the 30/6/2013(fin year end).

So, for a contract with 8 months to run, with a New Rate Start Date of 15/04/2013, V6 = FY13, and the difference between the new rate start date and the end of the financial year is 2.

Savings Months FY14

=IF(OR($L6="-",$L6=""),"",IF($V6="FY"&(RIGHT(ThisFY,2)-1),MIN(12-$W6,$G6-$W6),IF($V6="FY"&RIGHT(ThisFY,2),(DATEDIF(IF(DAY($L6)<15,DATE(YEAR($L6),MONTH($L6),0)-1,DATE(YEAR($L6),MONTH($L6),0)+1),$X$4,"m")),0)))

IF(OR($L6="-",$L6=""),"" if there is no start date in $L6, return BLANK

IF($V6="FY"&(RIGHT(ThisFY,2)-1) if the Last Fin Year is the same as V6 - which in this case it is then

MIN(12-$W6,$G6-$W6) give me the smaller of either 12 less the number of months already run or the remaining term less months already run. ???? so, in this case its either 12 - 2 = 10 or 8 - 2 = 6 which is the correct answer

Result is then 2 months ion FY13 and 6 months in FY14.

Then everything goes pair shaped. I cannot think how to do the next step. It will calculate months going beyond FY14 for savings that are longer than 12 months.

Using the data from row 6 (outlined above in the first two examples), imagine now that the saving is going to stretch out for 25 months from the 15th April 2013. The formulas above will return 2 and 10 for FY13 and FY14 respectively. The formula in FY15 should result in 12 and FY16 should contain a 3.