sum formula weird when i use together with if function

[Chloemo]

Hi,
I have to do a recurring entry schedule and i have using formula to make the template more automated and easy for user to use.

However, I have met some problem, for example: in my sheet "deferred charges", for cell Z7, suppose it is to show balance as at month end with formula [=IF($G7<DATE(2014,1,1),$L7-SUM(OFFSET($M7,0,0,1,MONTH($Z$5))),IF(MONTH($Z$5)<MONTH($G7),0,$K7-SUM(OFFSET($M7,0,0,1,MONTH($Z$5)))))].

For that particular transaction, the result in cell Z7 should be become zero as at month end 31/12/14. But using the formula, the result not become zero.

If I only use the [$K7-SUM(OFFSET($M7,0,0,1,MONTH($Z$5))], the result is zero, but when i add in the "if" function together, the result cannot become zero.

I have been trying to solve this for whole day still have no idea.Please kindly help on this issue.

Please find attached file for reference purposes. Thank you so much!