If year match certain criteria allocate accordingly

Dear all,



I want to calculate in how many days the bond will mature and then linear allocate it to the years (can be seen in the dummy attachment) . For example if the bond is maturing on 9.11.2010 (purchase date 30.6.2010) I will allocate the whole amount to 2010! (100%)

If the bond is maturing on 22.03.2012. then I will calculate in how many days it will mature (starting from 30.06.2010) and then allocate accordingly. (total 631 days until maturity, from which 29% are in 2010 (184/631) , 57% are in 2011 (364/631), and rest is in 2012- whole amount – (allocated 2010+allocated 2011) ).

I will appreciate any help in solving this issue. My guess is that there should be 3 scenarios. If the maturity column is equal to 2010 , than take the whole 100% value and allocate it under 2010 column.

If the maturity value contains 2011, than take the (184/number of days until maturing) * the whole value and allocate under 2010 column, and the rest , for 2011, is whole value – 2010 allocation.

Further, if the maturity column contains 2012, than take the (184/number of days until maturing) * the whole value and allocate under 2010 column, take the (364/number of days until maturing) * the whole value and allocate under 2011 column, and the rest the whole value – (allocated 2010+allocated 2011) allocate under 2012 ,

and in the last scenario if the column contains 2013 and above. the process would be the following – take the (184/number of days until maturing) * the whole value and allocate under 2010 column, take the (364/number of days until maturing) * the whole value and allocate under 2011 column, take the (365/number of days until maturing) * the whole value and allocate under 2012 column, and the rest , the whole value – (allocated 2010+allocated 2011+ allocated 2012) allocate under 2013 and beyond.

thanks for any help