VBA for Rate Resets for Floating Securities

StellarFinance

New Member
Joined
Apr 14, 2018
Messages
8
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! ;)
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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
ABCD
1MonthThe 10th or Next workdayHolidays
2JanuaryWednesday, January 10, 2018Monday, March 12, 2018
3FebruaryMonday, February 12, 2018Friday, August 10, 2018
4MarchTuesday, March 13, 2018Wednesday, October 10, 2018
5AprilTuesday, April 10, 2018
6MayThursday, May 10, 2018
7JuneMonday, June 11, 2018
8JulyTuesday, July 10, 2018
9AugustMonday, August 13, 2018
10SeptemberMonday, September 10, 2018
11OctoberThursday, October 11, 2018
12NovemberMonday, November 12, 2018
13DecemberMonday, December 10, 2018
Sheet1
Cell Formulas
RangeFormula
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)
 
Last edited:
Upvote 0
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!!
 
Upvote 0
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!
 
Upvote 0
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
ABCD
1MonthMonday WorkdaysWkHolidays
2AprilMonday, April 2, 20181Monday, January 1, 2018
32018Monday, April 9, 20182Monday, April 16, 2018
4Tuesday, April 17, 20183Friday, August 10, 2018
51st MondayMonday, April 23, 20184Wednesday, October 10, 2018
6Monday, April 2, 2018Monday, April 30, 20185
Sheet1
Cell Formulas
RangeFormula
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)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top