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!

 

[StellarFinance]

Hello,

Thank you very much for your help. I am having one issue with this formula.

Let's say the correct start date is 7/3/2018. The formula finds 7/3/2018 but then has to backdate 2 business days bringing us to 6/29/2018. The formula erroneously jumps to 5/29/2018 (the last business day in May on a -2 day lag) as the date. Is there a revision for both the monthly and the weekly formula I can leverage?

Just a note: when the formula calculates a date it will always be set to a -2 business day lag.

As always your help is enormously appreciated.

SF

 

[AlphaFrog]

Hello,

Thank you very much for your help. I am having one issue with this formula.

Which Formula?

How are you back dating it 2 days? Show your work.