VBA for Rate Resets for Floating Securities

[StellarFinance]

Hello Everyone,

I'm here to request your help with writing code that requires excel to reset (fix rates) for floating rate securities for some specified interval.

For example, suppose we use a benchmark of LIBOR at a monthly frequency interval for each 10th of the month. So, we need the 1 month LIBOR rate that falls on each 10th of the month.

However, this becomes more complicated when the 10th is a weekend or a UK holiday

(because LIBOR is a UK based rate)​

for some month - for ex. June 2018 and November 2018 both fall on a weekend. So, we have to tell the code to go to the next business day in the case of a weekend OR holiday, and IF the next business day after the weekend is a holiday the code must go to the business day after both the weekend and the holiday.

I was able to weakly create this using a basic array formula which was legitimately maybe 7 lines long. Finding this to be a very inefficient and also inaccurate method many times over, I am hoping to leverage VBA.

Please note I am a complete VBA newbie. BIG thank you to all and king regards!

 

[AlphaFrog]

The WORKDAY function returns the next working day excluding weekend and Holidays.

This would Return the next working date after June 9th 2018
Holidays is a Range that lists all the holidays.

=WORKDAY(DATEVALUE("June 9, 2018"),1,Holidays)

 

[StellarFinance]

Hello Alpha,

Thank you for your reply. I'm happy to use workday, however my bigger issue in this case is the formula which tells excel to find the 10th of each month regardless of weekends or holidays. In other words, I can create a formula which finds the 10th of the month each month, but what if the 10th is a weekend or holiday as in June and November? So, ensuring the system finds the 10th AND if the 10th is a weekend or holiday, that is goes to the next business day.

 

[AlphaFrog]

So, ensuring the system finds the 10th AND if the 10th is a weekend or holiday, that is goes to the next business day.

That's exactly what the WORKDAY function does. If you tell it to find the next business day after the 9th, it will return the date for the next workday excluding weekends and holidays.

 

[StellarFinance]

Hi Alpha,

What I mean is, I need a code which will find the "June 10th" part. I can probably incorporate workday() into the code, but first I need to write code to get me that date each month, i.e. to find the 10th of each month, then I can use workday to the find the next business day. So the code would find June 10th, July 10th, August 10th, September 10th, etc...

 

[AlphaFrog]

This calculates the date for the 10th of each month or the following workday if the 10th is a weekend or holiday.

The yellow ones are the next Monday because the 10th is a weekend.

The green ones are the next workday because the 10th is a holiday. I made up the Holidays list for illustration. You can put whatever holidays in the list that you want.

Note: In March the 10th was a weekend and the following Monday is a holiday as well. So it returns the next Tuesday.

<br />

Book1
	A	B	C	D
1	Month	The 10th or Next workday		Holidays
2	January	Wednesday, January 10, 2018		Monday, March 12, 2018
3	February	Monday, February 12, 2018		Friday, August 10, 2018
4	March	Tuesday, March 13, 2018		Wednesday, October 10, 2018
5	April	Tuesday, April 10, 2018
6	May	Thursday, May 10, 2018
7	June	Monday, June 11, 2018
8	July	Tuesday, July 10, 2018
9	August	Monday, August 13, 2018
10	September	Monday, September 10, 2018
11	October	Thursday, October 11, 2018
12	November	Monday, November 12, 2018
13	December	Monday, December 10, 2018
Sheet1

Cell Formulas
Range	Formula
B2	=WORKDAY(DATEVALUE(A2 & " 9, 2018"),1,$D$2:$D$13)

This part creates a date for the month in column A and the 9th of this year.
DATEVALUE(A2 & " 9, 2018")
If your question is how to create a date, there are several ways to do it.

The Workday part of the formula calculates the next workday after the 9th excluding weekends and holidays.
=WORKDAY(DATEVALUE(A2 & " 9, 2018"),1,$D$2:$D$13)

 

[StellarFinance]

Hi Alpha,

This is extremely helpful. Thank you very much - I cannot believe how simple it is! Would you please suggest an identical formula for a weekly rate fix? This would be a tad different - in the above example we are looking for a monthly reset but this time it would be based on a weekday (i.e. each Friday or each Wednesday). Thank you again!!

 

[AlphaFrog]

You're welcome.

I don't understand what you're asking now.

 

[StellarFinance]

Hi Alpha,

To clarify, we have a floating rate security which resets weekly, each Monday, using the 1 month LIBOR as the basis.

The date for Monday of each week will vary based on the month. Is there a formula which will easily locate the date of. each Monday of each month, with the same guidelines in terms of excluding weekends and holidays and going to the next workday?

Hope that clears up the question!

 

[AlphaFrog]

This lists the 4-5 Mondays (next workdays) for a selected month.

Choose the Month and Year in A2, A3 and the rest is calculated.
<br />

Book1
	A	B	C	D
1	Month	Monday Workdays	Wk	Holidays
2	April	Monday, April 2, 2018	1	Monday, January 1, 2018
3	2018	Monday, April 9, 2018	2	Monday, April 16, 2018
4		Tuesday, April 17, 2018	3	Friday, August 10, 2018
5	1st Monday	Monday, April 23, 2018	4	Wednesday, October 10, 2018
6	Monday, April 2, 2018	Monday, April 30, 2018	5
Sheet1

Cell Formulas
Range	Formula
B2	=WORKDAY($A$6-1+(C2-1)*7,1,$D$2:$D$15)
B3	=WORKDAY($A$6-1+(C3-1)*7,1,$D$2:$D$15)
B4	=WORKDAY($A$6-1+(C4-1)*7,1,$D$2:$D$15)
B5	=WORKDAY($A$6-1+(C5-1)*7,1,$D$2:$D$15)
B6	=IF(MONTH(WORKDAY($A$6-1+(C6-1)*7,1,$D$2:$D$15))=MONTH(A6),WORKDAY($A$6-1+(C6-1)*7,1,$D$2:$D$15),"")
A6	=DATEVALUE($A$2&" 1, "&$A$3)+CHOOSE(WEEKDAY(DATEVALUE($A$2&" 1, "&$A$3)),1,0,6,5,4,3,2)