Running Tab using Quarterly Payments

[Phillycheese5]

I have clients that pay on a specific date each quarter (pay months of
Mar, Jun, Sep and Dec), and I'm looking to keep a running tab of how
much is still owed by the end of the year, based on today's date.
My file is set up like this:

Today's Date (10/20/05)

A (Cycle) B (Amount) C (Due Date) D (Due by EOY)
Quarterly $400 25th $100

I have hundreds of clients that pay on different days during the pay
months. I'm having a hard time writing a formula that will accurately
(to the day) show me how much is still owed by the end of the year.

Any help is greatly appreciated.
Thanks,
Phillycheese

 

[Roger Govier]

Hi

Unless I have misunderstood you, you want to know for any value of Today()
what is the accrued sum that will be due from each client through till the
end of the year.

In which case it will be
=Date(Year(Today()),12,31)-Today()*(B1*4/((Date(Year(Today()),12,31)-Date(Year(Today()),1,1))



Regards

Roger Govier