prorate real real estate taxes

Good morning everyone:

I've been trying to create a formula that will calculate proration's of real estate taxes but to be honest its way beyond my excel ability.

Here's the outline:

Property taxes per year $4,800 ($2,400 per):

#1 payable $2,400 12/10/2017 which covers 1/1/2018-6/30/2018
#2 payable $2,400 4/10/2018 which covers 7/1/2018-12/30/2018

#3 payable $2,400 12/10/2018 which covers 1/1/2019-6/30/2019 this will come into play

If the closing was 3/31/18 which is before 4/10/18 proration's would be simple:

Seller paid #1 $2,400 on 12/10/17 which covers taxes from 1/1/18-6/30/18 so, buyer would owe seller 3 months (4/1-6/30)

Now the problem, if the closing is after 4/10 the seller now has paid #2 $2,400 for the taxes due 7/1-12/31/18 so that entire amount will need to be added to the taxes already due under #1.

If the closing is after 6/30 buyer would not owe the seller taxes due under #1 but would still owe taxes due under #2.

Now the other problem, if the closing is after 12/10 the seller has now paid #3 $2,400 for the taxes due 1/1-7/31/19 so that entire amount will need be added to the taxes already due under #2.

So, that's my outline of the formula I need help with.

Thanks.

Bruce

P.S. hopefully, my outline will help vs cause more confusion.

P.S. I won't be able to respond until later today....doctors appointments most of the day

Jeff:

I took a quick glance at it and it sure seems to work.

I'll run the numbers manually and see if they all agree.

This forum is the best ever and thank you so much for your help.

Bruce

Glad to help. I should get notifications if you post here with questions or problems.

Jeff:

It worked perfectly.

I copied the sheet and inserted it into my larger sheet and made a few modifications (using other cells for the tax amount and settlement date) but the formulas worked perfectly.

It dawned on me to use this for the seller all I had to do is put a "-" in front of the results and it would show a credit for the seller.

I love this forum!

Thanks for all your help.

Bruce

This one goes back a ways. I'm trying to use this sheet, but what I need it for it to ignore the Year and only count based upon Day/Month. I also need it to assume that payment will be made on payment due dates (Day/Month) so as to determine the pro rata tax owed to or from the payee based upon the expected settlement Day/Month.

Awesome calculator, thank you! How would I add to this and have a column to show whether the tax is paid or unpaid? For example, in NY, the school tax runs July 1 - June 30 but the bill can be paid up until Oct 10. If we closed on October 10 and if the seller has not paid the bill yet, the buyer would pay it in full and then get a credit from the seller from July 1 to October 9.