Date Formulas for Financial Liquidity Chart

[Brk004]

Hi,

I work for a college endowment and am creating a spreadsheet/calendar that has all of liquidity terms for our different funds. The idea is to use the =TODAY function to have the spreadsheet be responsive and continuously current. I'd love your help in developing a formula that will continuously update the sheet and have the output reflect business days and not weekend days.

For example, for Fund 1 there is a quarterly liquidity in which we can only redeem on say 12/31/2017. However, we need to provide 45 days advance notice and so the latest we could notify Fund 1 for a quarterly redemption is 11/15/17 (i.e. 12/31/2018 - 45). Well, November 15, 2017 has passed and so I'd like the formula to recognize that 11/15 has passed and move the next Trade Date to 3/31/2018 and have the Minimum Notification Date be 45 days after which equates to 2/14/2018 (3/31/2018 - 45 days).

Plus, if there's a formula or condition that would ensure that the Minimum Notification Date falls on a workday vs. a weekend that would be very helpful. Once the "code is cracked" this should be applicable to all of the funds.

I hope this screenshot below helps communicate what I'm looking to achieve. Many thanks for your help.

Snip.PNG

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Brk004]

Thanks for your helpful suggestions, Glenn.

I've revised the spreadsheet per your input. Please let me know if you have any problems.


In short, I'm using the =TODAY forumula to create a responsive spreadsheet. The problem I have is that if want to redeem our investment with a fund on quarterly basis, for example, on 12/31/2017, and they require 45 days advance notice, the minimum notification/latest date would be 11/15/2017. However, since 11/15/17 date has passed, the next available date is 3/31/2018. How do I move the next quarterly trade date to 3/31/2018 once 11/15/2017 has passed? Also, if the trade date and the minimum notification date could be WORKDAY or business day, that would be great.

[JeteMc]

I know that this isn't what you asked for, however I thought that it might be useful.
On sheet 2 of the attached file the Trade and Notification dates are projected into the future.
The formulas for the Notification dates are similar to: =IF($D6="Daily","",WORKDAY(J6-$E6,0))
The formulas for the Trade dates are similar to:

Please Login or Register  to view this content.

The minimum/latest valid notification and trade dates are highlighted in green using the following formula as a conditional formatting rule (applied to the range G6:AD17):

Please Login or Register  to view this content.

Let us know if you have any questions.