Sumproduct to account for a date

[pauldaddyadams]

Hi,

I don’t expect many takers on this one as it is complicated what I am trying to achieve buy preying there is an expert out there that can help.

Background.
In the UK you usually pay your full years tax on property over 10 months and then in months 11 (Feb) and 12 (March) there is no charge. But in order to account for this what we would do is split the annual bill by 10 months but then show an element of the 1/12th on the balance sheet as a prepayment.

The Model
Column W and X is the start and End date of the Council Tax Period
Column Y is the amount of the council Tax for that period
Columns Z:AK I have working which splits the payment by month

Columns AN:AY Is where I have the problem. Currently it works only when the end period is set as 31/05/2014 as what it does is recognise that the bill is being split by 10 periods and it calculates the element which is being prepaid as council tax you can pay over 10 months rather than 12 so therefore each month you pay an element of Febuary and March’s bill where no bill is received.

What I need the formula to do is two fold:

Problem 1
I need the formulas highlighted in Blue to take into account the End date. So if the start and end date is less than a year then there shouldn’t be any prepayment as this has already been calculated correctly.

Problem 2
Once the above formula is working then I need to factor in that the property could suddenly terminate and therefore it would need to take into account the termination date E.g. If council tax is set to run from April to March and then it suddenly cancels in June then I would expect a prepayment in April and May and then in June (to the remaining year) it to be zero.

I know this is complicated but hope someone can help?

I have added an example spreadsheet

Paul

[Ace_XL]

In AN7 try

=MAX(0,SUMPRODUCT(($Z$6:$AK$6<=AN$6)*($Z7:$AK7))-($Y7/(IF(DATEDIF($W7,$X7,"m")+1=10,12,DATEDIF($W7,$X7,"m")+1)))*(DATEDIF($W7,AN$6,"m")+1))

Copy down and across

[hoyasaxa215]

Seems to be easiest to have two rows. One with the ratable monthly expense and another with the actual cash received. Then you could calculate your excess cash reserves as the difference.

[pauldaddyadams]

Hi Ace

Thanks for coming to the rescue again.

Its so close - the only one it doesnt work for is in cell AP13 this should be zero because it would be assumed the final payment has been made and so the value of the prepayments would be cleared out. Any suggestions?

[Ace_XL]

Aah yes, that's due to the cash outflow schedule formulas

Change formula in Z7 to
=IF($O7>0,IF(ISBLANK($T7),IF(OR(Z$6<$W7,Z$6>$X7),0,$Y7/(DATEDIF($W7,$X7,"m")+1)),IF(OR(Z$6<$W7,Z$6>$X7,Z$6>=$T7),0,$Y7/(DATEDIF($W7,$X7,"m")+1))),0)

Copy down and across

[pauldaddyadams]

Hi,

Sorry - when I change to use that formula the cashoutflow section is incorrect as what should happen is in cell AB13 this should be £30 (as a payment will still have to be made in the month) but in cell AP13 - this should be zero because the final payment has been made.

Any ideas?

[Ace_XL]

Ok Understand. Scarp Post #5.

In AN7 use

=MAX(0,IF(AND(ISNUMBER($T7),AN$6>=$T7),0,SUMPRODUCT(($Z$6:$AK$6<=AN$6)*($Z7:$AK7))-($Y7/(IF(DATEDIF($W7,$X7,"m")+1=10,12,DATEDIF($W7,$X7,"m")+1)))*(DATEDIF($W7,AN$6,"m")+1)))

Copy down and across

[pauldaddyadams]

Ace that did it!

However, bear with me as I have only just relised this (sorry)

The formula works when the start date is set as 1/4/2014 however when the start date is more present than that the formula returns an error (Cell AL13).

Can there be a workaround? I dont want to use IFERROR as this may mask other errors.

I have attached the spreadsheet - use these formulas as I have added columns.

[Ace_XL]

Try this in AL7
=MAX(0,IF(AND(ISNUMBER($Q7),AL$6>=$Q7),0,SUMPRODUCT(($X$6:$AI$6<=AL$6)*($X7:$AI7))-($W7/(IF(DATEDIF($U7,$V7,"m")+1=10,12,DATEDIF($U7,$V7,"m")+1)))*(DATEDIF(MIN($U7,AL$6),AL$6,"m")+1)))

Note: Prepayments will kick in only when duration of (Col V less Col U) is 10 months i.e. Apr14-Jan15, May14-Feb15, Jun14-Mar15. Is that how you want it?

[pauldaddyadams]

Hi, Thanks for the formula - that worked.

Thats how I would need it yes as the only option ususally is to pay 12 months or 10 months. If on the rare chance you own the property for less, say 6 months, then the outflow formula would just divde the amount by 6 and assume no prepayment.

There is one other related formula I need help on but I need to prepare the example file - once done I will post on here. What I need it to do in a seperate section is work out the total cashoutflow less the termination value and place the amount in the correct month (it will be the same as the termination month)

[pauldaddyadams]

Hi, Thanks for the formula - that worked.

Thats how I would need it yes as the only option ususally is to pay 12 months or 10 months. If on the rare chance you own the property for less, say 6 months, then the outflow formula would just divde the amount by 6 and assume no prepayment.

There is one other related formula I need help on but I need to prepare the example file - once done I will post on here. What I need it to do in a seperate section is work out the total cashoutflow less the termination value and place the amount in the correct month (it will be the same as the termination month)

[pauldaddyadams]

This is the new model attached

What I would like help with is a formula in the blue highlighted fields which will work out:

1. The correct month based on the termination date- (I tried using an IF = Eomonth formula but this didn’t work)
2. The sum Paid to that month
3. The total tax due (cell R7) less the sum paid to date less

So in this example we have paid £120 for 3 months but it terminated in June so we have overpaid meaning a credit of 60 is due back.

Can anyone help please?

[Ace_XL]

In AY7

=IF(TEXT(AY$6,"mmyy")=TEXT($Q7,"mmyy"),$R7-SUM($X7:X7),"")

Copy across

[pauldaddyadams]

Thanks Ace this did it!! Thanks for all your help - couldnt have built the model without you