Page 2 of 3 1 2 3
Thread Options
#2042529 - 10/05/15 05:41 PM Re: New Calculator for Closing Disclosure CompliantOkie
Carolina Blue Offline
Platinum Poster
Carolina Blue
Joined: Jul 2005
Posts: 952
Lost in a regulatory fog
The Holidays are entered through the end of 2018. So at some point you will need to update it. The Holidays are listed in a hidden column (F for the Earliest tab, and E for the Desired tab). So you'll have to unprotect it, unhide that column and update it sometime in the next 3 years.

Flyboy, one of my best friends from high school went to Dook and I ended up marrying a someone who got their PHD at Dook, so I know it's possible (albeit very rare) to be a decent Dookie. I've met you when I was working in Charleston so I think it's safe to put you in that camp wink

Return to Top
TRID - TILA/RESPA Integrated Disclosures Rule
#2042563 - 10/05/15 06:35 PM Re: New Calculator for Closing Disclosure bankerchris
JWills, CRCM Offline
Diamond Poster
JWills, CRCM
Joined: May 2013
Posts: 1,742
The Mitten State
So probably a dumb question--B27 just says #NAME?

What did I forget to do?

Thanks.
_________________________
Nonsense wakes up the brain cells.

--Dr. Seuss

Return to Top
#2042565 - 10/05/15 06:37 PM Re: New Calculator for Closing Disclosure bankerchris
Compl101TX Offline
Gold Star
Compl101TX
Joined: Aug 2010
Posts: 378
W. TX
Nice tool Carolina!

I have a suggestion for whoever wants to add a reminder about changing the holidays during 2018.

You need to unlock the worksheets and add the formula below to the Earliest Closing Date sheet on Cell C3. There is nothing in that cell so it should work out. I would color the font red.

=IF(B3>F32,"Holidays on Column F must be updated","")

To do the same on the Desired Closing Date sheet add this formula to Cell C3:

=IF(B3>E32,"Holidays on Column E must be updated","")
Last edited by Compl101TX; 10/05/15 06:38 PM. Reason: spelling
_________________________
My opinion only.
AVP-Compliance

Return to Top
#2042595 - 10/05/15 07:30 PM Re: New Calculator for Closing Disclosure JWills, CRCM
ComplianceRegs Offline
100 Club
Joined: Jan 2013
Posts: 169
I noticed the same thing JWills and am looking into the formulas.
_________________________
CRCM

Return to Top
#2042607 - 10/05/15 07:53 PM Re: New Calculator for Closing Disclosure bankerchris
ComplianceRegs Offline
100 Club
Joined: Jan 2013
Posts: 169
The issue is if you are using a version of Excel 2007 or earlier. The workday.intl function is not recognized unless you have 2010 or newer versions of Excel.
_________________________
CRCM

Return to Top
#2042611 - 10/05/15 07:56 PM Re: New Calculator for Closing Disclosure bankerchris
JWills, CRCM Offline
Diamond Poster
JWills, CRCM
Joined: May 2013
Posts: 1,742
The Mitten State
Thank you. I will contact IT to see if I can get a more up to date version.
_________________________
Nonsense wakes up the brain cells.

--Dr. Seuss

Return to Top
#2042677 - 10/05/15 09:58 PM Re: New Calculator for Closing Disclosure JWills, CRCM
Carolina Blue Offline
Platinum Poster
Carolina Blue
Joined: Jul 2005
Posts: 952
Lost in a regulatory fog
Sorry about that. I wasn't sure when the workday.intl formula became available in Excel but knew it was included in 2010. If you have an Excel version earlier than 2010 there are some fixes out there to add the calculation to Excel through Visual Basic. You should probably consult your IT person for that.

Return to Top
#2042681 - 10/05/15 10:11 PM Re: New Calculator for Closing Disclosure bankerchris
Compl101TX Offline
Gold Star
Compl101TX
Joined: Aug 2010
Posts: 378
W. TX
I don't have an older version to test this but on the Developer toolbar you could try going to Add-Ins (if located there on earlier versions) and add the Analysis Toolpak and the Analysis Toolpak-VBA.
_________________________
My opinion only.
AVP-Compliance

Return to Top
#2042840 - 10/06/15 05:08 PM Re: New Calculator for Closing Disclosure bankerchris
Kristal Offline
New Poster
Joined: Oct 2015
Posts: 1
I saw a excel spreadsheet that was posted today that Carolina Blue created a TRID Calculator. I assume this is to take the place of the MDIA Calculator. A issue that I always had with the MDIA Calculator that I also notice with this one is that we are not open on Saturday. Is there a way to get it to calculate based on that exclusion of Saturday? There was a field on the MDIA Calculator that said to put a “2” if you were closed on Saturday, but it never worked. So, I don’t know if it can be done or not. But it would really be nice if it did. Can you have someone check on that for me, please?

Return to Top
#2042892 - 10/06/15 07:07 PM Re: New Calculator for Closing Disclosure Carolina Blue
Compl101TX Offline
Gold Star
Compl101TX
Joined: Aug 2010
Posts: 378
W. TX
Originally Posted By Carolina Blue
The calculator does not count Saturdays for the 3 business days to (re)issue a disclosure. It does count Saturdays for the waiting periods (mail, CD review, and ROR).

If you want to change it, then you will need to unprotect the sheet and find the applicable formula(s). Most of the formulas are directly in the cell, but a few are indirectly in a hidden column to the right. Highlight Columns C through G and right click in that area (or go to the View tab) and click Unhide. You can see the formulas in the D and E columns.

If the formula includes "WORDKDAY" then it is not counting Saturday. If it includes "WORKDAY.INTL" then it is counting Saturday. Adjust the formulas (add .INTL or remove it) as needed to count how you want. Hope that helps.
_________________________
My opinion only.
AVP-Compliance

Return to Top
#2043687 - 10/09/15 08:16 PM Re: New Calculator for Closing Disclosure bankerchris
Beagles Rule Offline
100 Club
Joined: Feb 2013
Posts: 102
Thank you Blue, from a Wake alum.

Return to Top
#2043712 - 10/09/15 08:55 PM Re: New Calculator for Closing Disclosure Beagles Rule
Carolina Blue Offline
Platinum Poster
Carolina Blue
Joined: Jul 2005
Posts: 952
Lost in a regulatory fog
You're welcome. Just let the Heels win next Saturday! grin

Also, I've been working on a tolerance calculator if anybody wants to test it out for me, then send me a PM.

Return to Top
#2043775 - 10/12/15 02:41 PM Re: New Calculator for Closing Disclosure Carolina Blue
Compliance1 Offline
Platinum Poster
Joined: Mar 2006
Posts: 650
Unfortunately I am having a problem getting my formula to work properly. I have followed the instructions that Carolina Blue sent but I am still having problems. I am on version 2010 for Excel. Any suggestions for the WORKDAY.INTL? I am needing the calculator to count Saturdays.

Return to Top
#2043781 - 10/12/15 03:22 PM Re: New Calculator for Closing Disclosure CompliantOkie
Compliance1 Offline
Platinum Poster
Joined: Mar 2006
Posts: 650
I have followed the simple steps in order for the spreadsheet to calculate Saturdays into our workday but the response back is #NUM!. I am not sure what I am doing wrong. I have even followed the corrections that Excel suggested and I got the same response. I am on 2010 version of Excel. Do you have any suggestions?

Return to Top
#2043784 - 10/12/15 03:51 PM Re: New Calculator for Closing Disclosure CompliantOkie
Compliance1 Offline
Platinum Poster
Joined: Mar 2006
Posts: 650
I have followed the simple steps in order for the spreadsheet to calculate Saturdays into our workday but the response back is #NUM!. I am not sure what I am doing wrong. I have even followed the corrections that Excel suggested and I got the same response. I am on 2010 version of Excel. Do you have any suggestions?

Return to Top
#2043801 - 10/12/15 09:12 PM Re: New Calculator for Closing Disclosure Compliance1
Here4Life Offline
100 Club
Joined: Feb 2010
Posts: 116
I had to change the calculator to count Saturdays too. On the Desired Closing Tab, the cell for LE must be issued by: my formula looks like this =IF(B3="","",WORKDAY.INTL(B3,3,11,E2:E35)).

You have to highlight "WORKDAY" in the formula, go to the formula tab, click on Date & Time, click on WORKDAY.INTL & change the function arguments.

Hope that helps!

Return to Top
#2043824 - 10/13/15 01:13 PM Re: New Calculator for Closing Disclosure CompliantOkie
Compliance1 Offline
Platinum Poster
Joined: Mar 2006
Posts: 650
Here4Life, thank you for your response. I entered in the .INTL into my formula but the one thing I did notice that was different was your formula had an 11 after the 3. I am not sure what the 11 is referencing but my formula now gives me a date instead of #NUM!.

My only question, at least for the moment, is why is the calculator giving me a date of 10/13/2015 when I put a date of 10/08/2015 into the cell that is reflecting when the LE was sent. The date should show 10/12/2015 which would incorporate the Saturday.
Last edited by Runner4fun; 10/13/15 01:35 PM.
Return to Top
#2043849 - 10/13/15 02:30 PM Re: New Calculator for Closing Disclosure Compliance1
Here4Life Offline
100 Club
Joined: Feb 2010
Posts: 116
10/12/15 was Columbus Day...Federal Holiday

Return to Top
#2043860 - 10/13/15 02:46 PM Re: New Calculator for Closing Disclosure Here4Life
Compliance1 Offline
Platinum Poster
Joined: Mar 2006
Posts: 650
Oh my bad...I'm looking at calendars that don't show the Holidays. Thanks...

Return to Top
#2043891 - 10/13/15 04:03 PM Re: New Calculator for Closing Disclosure Compliance1
Carolina Blue Offline
Platinum Poster
Carolina Blue
Joined: Jul 2005
Posts: 952
Lost in a regulatory fog
I PM'd you Runner4 fun, but for others- when you change the workday to workday.intl it requires one additional parameter. This third parameter requires a code for which days to count as business days. 11 as referenced above counts all days except Sundays. Type workday.intil in the Help function and it will give you the full list of codes if you want to count/exclude other days.

Return to Top
#2095078 - 08/24/16 07:33 PM Re: New Calculator for Closing Disclosure bankerchris
ALW Offline
Member
Joined: Dec 2014
Posts: 96
A dated thread, but as I was looking at the holidays within the TRID calendar, I noticed that for Christmas 2016, it is listed as 12/25/2016, but isn't the true Federal Reserve holiday the 26th because the 25 is a Sunday, so wouldn't we want the Holiday schedule to read the 26th? Just looking for confirmation before I make a change. Similarly, for the 1/1/2017, would we want it to read 1/2/2017? Thanks!

Return to Top
#2095081 - 08/24/16 07:40 PM Re: New Calculator for Closing Disclosure bankerchris
John Burnett Offline
10K Club
John Burnett
Joined: Oct 2000
Posts: 39,598
Cape Cod
If you're counting business days using the precise Regulation Z definition (all days except Sunday and the ten designated federal holidays), whether the Fed (or the post office) is open is irrelevant. Monday holidays always fall on Mondays and Thanksgiving always is a Thursday, but the holidays that fall on fixed dates (Jan 1, July 4, December 25) don't move from those dates when counting those business days. Christmas 2016 is a Sunday, and the next day is a business day for rescission purposes, as is January 2, 2017.

You would not count days you aren't open for substantially all business purposes when counting "general definition" business days.
_________________________
John S. Burnett
BankersOnline.com
Fighting for Compliance since 1976
Bankers' Threads User #8

Return to Top
#2095121 - 08/24/16 09:10 PM Re: New Calculator for Closing Disclosure bankerchris
Docs Offline
100 Club
Joined: May 2016
Posts: 241
For those of you needing a citation for John's comment above:

TILA, Regulation Z, 12 CFR 1026, Supplement I, Official Interpretations
1026.2(a)(6)-2, Business Day
Rule for rescission, disclosures for certain mortgage transactions, and private education loans. A more precise rule for what is a business day (all calendar days except Sundays and the Federal legal holidays specified in 5 U.S.C. 6103(a)) applies when the right of rescission, the receipt of disclosures for certain dwelling-secured mortgage transactions under §§1026.19(a)(1)(ii), 1026.19(a)(2), 1026.31(c), or the receipt of disclosures for private education loans under §1026.46(d)(4) is involved. Four Federal legal holidays are identified in 5 U.S.C. 6103(a) by a specific date: New Year's Day, January 1; Independence Day, July 4; Veterans Day, November 11; and Christmas Day, December 25. When one of these holidays (July 4, for example) falls on a Saturday, Federal offices and other entities might observe the holiday on the preceding Friday (July 3). In cases where the more precise rule applies, the observed holiday (in the example, July 3) is a business day.

Return to Top
#2110441 - 12/12/16 05:24 PM Re: New Calculator for Closing Disclosure bankerchris
CompliantOkie Offline
Gold Star
CompliantOkie
Joined: Dec 2013
Posts: 422
OOOOOOklahoma
FYI I did a little test and the calculator was not recognizing 12/26/2016 and 1/2/2017 as Federal holidays. I updated my copy for Carolina Blue's calculator to account for the Monday holidays. Works like a charm!

Return to Top
#2110445 - 12/12/16 05:32 PM Re: New Calculator for Closing Disclosure CompliantOkie
rlcarey Offline
10K Club
rlcarey
Joined: Jul 2001
Posts: 78,965
Galveston, TX
Originally Posted By CompliantOkie
FYI I did a little test and the calculator was not recognizing 12/26/2016 and 1/2/2017 as Federal holidays. I updated my copy for Carolina Blue's calculator to account for the Monday holidays. Works like a charm!


That is because they are not holidays under the precise business day rules.
_________________________
The opinions expressed here should not be construed to be those of my employer: PPDocs.com

Return to Top
Page 2 of 3 1 2 3