Excel: The First and Last Day of Month with and without Weekends or Holidays

The post “Excel: The First and Last Day of Month with and without Weekends or Holidays” was originally published on SHLee AI Financial Model blog.

This post shows how to determine the first or last day of a month taking weekend or holidays into account using Excel functions with a user-defined table of holidays. This can be applied when generating a schedule of IRS swap cash flows.

There are some cases where the first or last day of a month needs to be determined taking weekend or holidays into account. A typical example is to make a schedule of interest rate or currency swap cash flows.

Using Excel functions and user-defined table of holidays, we can determine these dates simply.

Excel Formula

For example, the following Excel formula are used for the 3rd row in the spreadsheet.

A) C3=DATE(LEFT(B3,4), MID(B3,6,2),1)
B) D3=C3+IF(WEEKDAY(C3)=1,1,IF(WEEKDAY(C3)=7,2,0))
C) E3=WORKDAY(C3-1,1,$B$14:$H$22)
D) F3=EOMONTH(C3, 0)
E) G3=F3-IF(WEEKDAY(F3)=1,2,IF(WEEKDAY(F3)=7,1,0))
F) H3=WORKDAY(F3+1,-1,$B$14:$H$22)

A) means the first day of a month. In B), the date moves forward to the first weekday taking Saturday and Sunday into account and in C) that date moves forward more with consideration of holidays. Conversely, D), E), and F) are the cases of the end of a month.

In particular, $B$14:$H$22 is a range of user-defined holidays which tends to differ country by country.

Period.

Join The Conversation

For specific platform feedback and suggestions, please submit it directly to our team using these instructions.

If you have an account-specific question or concern, please reach out to Client Services.

We encourage you to look through our FAQs before posting. Your question may already be covered!

Visit IBKR.com Open an IBKR Account

Leave a Reply Cancel reply

Disclosure: Interactive Brokers Third Party

Information posted on IBKR Campus that is provided by third-parties does NOT constitute a recommendation that you should contract for the services of that third party. Third-party participants who contribute to IBKR Campus are independent of Interactive Brokers and Interactive Brokers does not make any representations or warranties concerning the services offered, their past or future performance, or the accuracy of the information provided by the third party. Past performance is no guarantee of future results.

This material is from SHLee AI Financial Model and is being posted with its permission. The views expressed in this material are solely those of the author and/or SHLee AI Financial Model and Interactive Brokers is not endorsing or recommending any investment or trading discussed in the material. This material is not and should not be construed as an offer to buy or sell any security. It should not be construed as research or investment advice or a recommendation to buy, sell or hold any security or commodity. This material does not and is not intended to take into account the particular financial conditions, investment objectives or requirements of individual customers. Before acting on this material, you should consider whether it is suitable for your particular circumstances and, as necessary, seek professional advice.

How much could you save on your margin loan by switching to Interactive Brokers?

Fill out the information below to see your estimated savings.

Current Interest Rate

Balance

USD

Margin Amount Borrowed

USD

Time Margin is Borrowed

IBKR will assess a surcharge of 1% on large loan balances unless otherwise prearranged with IBKR. The 1% surcharge would apply to all balances in the highest tier.

The interest calculator is based on information that we believe to be accurate and correct, but neither Interactive Brokers LLC nor its affiliates warrant its accuracy or adequacy and it should not be relied upon as such. Neither IBKR nor its affiliates are responsible for any errors or omissions or for results obtained from the use of this calculator.

Restrictions apply. Annual Percentage Rate (APR) on USD margin loan balances for IBKR Pro as of October 3, 2024. Interactive Brokers calculates the interest charged on margin loans using the applicable rates for each interest rate tier listed on its website. Learn more about margin loan rates.

The projections or other information generated by the Interest Calculator tool are hypothetical in nature, do not reflect actual results and are not guarantees of future results. Please note that results may vary with use of the tool over time.

Trading on margin is only for experienced investors with high risk tolerance. You may lose more than your initial investment. For additional information about rates on margin loans, please see Margin Loan Rates.

Excel: The First and Last Day of Month with and without Weekends or Holidays

Join The Conversation

Leave a Reply Cancel reply

Disclosure: Interactive Brokers Third Party

Information on Other Interactive Brokers Affiliates

Interactive Brokers Canada Inc.

Interactive Brokers Australia Pty. Ltd.

Interactive Brokers Hong Kong Limited

Interactive Brokers India Pvt. Ltd.

Interactive Brokers Securities Japan Inc.

Interactive Brokers Singapore Pte. Ltd.

Join The Conversation

Leave a Reply Cancel reply

Disclosure: Interactive Brokers Third Party

Bi-Weekly Newsletter

Daily Newsletter

Weekly Newsletter

Weekly Newsletter

Monthly Newsletter