Monthly Archives: November 2017

Fannie Mae Updates Selling Guide

Selling Guide Announcement SEL-2017-09  October 31, 2017 Selling Guide Updates

The Selling Guide has been updated to include changes to the following:

 Servicing Execution Tool™ (SET™) and Servicing Marketplace  Inter Vivos Revocable Trusts

 Consolidation of eSign and Electronic Transactions  Mortgages Paid by Others

 Form 1004D as Optional for Uniform Collateral Data Portal (UCDP)

 Miscellaneous Selling Guide Updates Each of the updates is described below.

The affected topics for each policy change are listed on the Attachment. The Selling Guide provides full details of the policy changes. The updated topics are dated October 31, 2017. In addition, revisions to the Texas Security Instrument are described in this Announcement.

Servicing Execution Tool (SET) and Servicing Marketplace In December 2014, we introduced the Servicing Execution Tool (SET) Bifurcation option for concurrent transfers of servicing via the Pricing & Execution – Whole Loan® (PE-Whole Loan) committing platform, under which the selling representations and warranties for the delivered loans are bifurcated from the servicing duties, obligations, and responsibilities. With this update, we are expanding access to the bifurcation of selling and servicing representations and warranties available through PE-Whole Loan. In addition, we are introducing the Servicing Marketplace, an application designed to enhance and bring new concurrent transfer of servicing options to customers. This expansion is designed to simplify the customer experience related to concurrent transfers of servicing transactions and provide certainty of sale, execution, and process efficiency. In order for a seller to join SET or Servicing Marketplace, the eligibility criteria outlined in the Selling Guide must be met. As a reminder, specific lender approval is required to participate in the SET and Servicing Marketplace solutions. Effective Date Loans delivered against PE-Whole Loan servicing released commitments taken on or after December 4, 2017 will be bifurcated if seller participates in SET or Servicing Marketplace. Inter Vivos Revocable Trusts Normally, Fannie Mae deems property in which no borrower has a direct ownership interest as ineligible collateral. An exception has been granted for inter vivos revocable trusts, a common estate planning tool that may involve instances where no individual borrower has an ownership interest in the mortgaged property. We have made changes to the Selling Guide to clarify the distinction between the individual borrower and the inter vivos revocable trust as owner of an interest in the mortgaged property. We also clarify our expectations for the execution of notes and mortgages. © 2017 Fannie Mae. Trademarks of Fannie Mae. SEL- 2017-09 2 of 4 Effective Date These updates are effective immediately. Consolidation of eSign and Electronic Transactions In line with our continued efforts to simplify and consolidate policies shared by the Selling and Servicing Guide, we have again updated and streamlined duplicative content in a few topics in Part A, Doing Business with Fannie Mae, pertaining to electronic records, signatures, and other electronic transactions. With this update, we have  consolidated into one topic the various policies that pertain to the management of electronic transactions and confirmed that sellers/servicers are authorized to originate, service, and modify loans using electronic records (special approval is still required for electronic promissory notes);  clarified that audio and video recording are not permitted formats except to the extent requested by us in connection with permitted remote notarizations;

 streamlined language to clarify that electronic records must accurately reflect information and formatting as it was presented to the intended beneficiaries and signers;

 required that systems generating electronic records generate them as valid records and be maintained as secure;

 confirmed that recorded mortgages and deeds of trust need not be maintained in paper format;

 confirmed that all electronic signatures must comply with ESIGN, UETA, and applicable law; and

 removed requirements for document custodians from the Guide as they were duplicative of requirements in Fannie Mae’s Requirements for Document Custodians (RDC). There are no policy changes associated with this consolidation of content. The duplicative content was removed from the Servicing Guide in the update published on October 11, 2017, and the consolidated policy now resides within Selling Guide A2-5.1-03, Electronic Records, Signatures, and Transactions. Effective Date These updates are effective immediately. Mortgages Paid by Others We recently updated our policies in the Selling Guide related to multiple financed properties and mortgage debts paid by others. With this update, we are clarifying that when a borrower is obligated on a mortgage, but another party has been making the mortgage payments  the lender may exclude the full monthly housing expense from the DTI ratio, provided the borrower is not using rental income from the applicable property to qualify; that is, the PITIA may be excluded and not just the principal and interest payment;  the mortgaged property must still be included in the borrower’s multiple financed property count and the unpaid principal balance for the mortgage must still be included in the calculation of reserves for multiple financed properties. Effective Date These clarifications are effective immediately. Form 1004D as Optional for Uniform Collateral Data Portal (UCDP) To align with the UCDP Release Notification from September 12, 2017, we updated our policy related to forms that can be submitted to UCDP. Lenders now have the option to submit the Appraisal Update and/or Completion Report, (Form 1004D) directly to the portal for conventional mortgage loans delivered to Fannie Mae. © 2017 Fannie Mae. Trademarks of Fannie Mae. SEL- 2017-09 3 of 4 Effective Date Effective immediately. Miscellaneous Selling Guide Updates  B4-1.3-01, Review of the Appraisal Report. We updated the overview to clarify the purpose of our appraisal report policies.  B5-5.2-03, DU Refi Plus and Refi Plus Property Valuation and Project Standards Condo, Co-op, and PUD Project Review Requirements. To align with our standard policy, we updated the condo, co-op, and PUD project insurance requirements for DU Refi Plus loans to remove the requirement for liability insurance coverage.  A2-4-01, Master Agreement Overview. The Selling Guide contains a list of the types of mortgage loans that currently require negotiated terms in a Master Agreement, which includes second mortgage loans. From time to time, we receive questions as to whether we are currently negotiating second mortgages. As a result, we have added a clarification that we are not approving lenders for or accepting deliveries of second mortgages. Revisions to the Texas Security Instrument Fannie Mae and Freddie Mac have revised the Texas Deed of Trust (Form 3044) to reflect recent changes to state law that affect the date of foreclosure sales. The revised Form 3044 (with a revision date of 10/17) is available on the SingleFamily Security Instruments page on our website. Effective Date Lenders are encouraged to use the updated document immediately, but must do so for mortgage loans with note dates on or after April 1, 2018. Proposed Texas Constitutional Amendments for Home Equity Lending Voters in Texas will be voting on proposed amendments to the Texas constitution on November 7, 2017. These amendments, if passed, will affect home equity lending in Texas, and will become effective on January 1, 2018. Fannie Mae is assessing the potential impact on our Texas home equity legal documents and will provide guidance and updates if the amendments are passed. ***** Lenders who have questions about this Announcement should contact their Customer Delivery Team.


CFPB Publishes Information Regarding Beta Launch HMDA Platform

The FFIEC and HUD have published the following resources for financial institutions required to file Home Mortgage Disclosure Act (HMDA) data:


Updated November 2017:

The Bureau launched the beta version of the HMDA Platform.

A new short video introducing the HMDA Platform is now available on CFPB’s YouTube channel.

HMDA Platform

The beta version of the HMDA Platform allows financial institutions to upload sample HMDA files and perform validation on the data; review edits; confirm the test data submission; and conclude the test HMDA filing process.

Technology Preview

The Technology Preview provides resources for financial institutions preparing their systems to file HMDA data with the CFPB.

Filing Instructions Guide

Separate Filing Instructions Guides (FIG) are now available for data collected in 2017 and 2018.

For data collected in 2017

For data collected in or after 2018

For data collected in or before 2016, please visit the FFIEC website  for data submission resources.

Loan/Application Register (LAR) Formatting Tool

The LAR Formatting Tool is intended to help financial institutions, typically those with small volumes of covered loans and applications, to create an electronic file that can be submitted to the HMDA Platform.

File Format Verification Tool (FFVT)

The File Format Verification Tool is provided for filers who wish to confirm that a LAR file is formatted in the required pipe delimited text file format, and meets certain formatting requirements specified in the Filing Instructions Guide. Section 3 of the HMDA Tools Instructions guide provides further information on using the FFVT.

HMDA Loan Scenarios

The HMDA Loan Scenarios is provided as an illustrative aid to help HMDA filers prepare their loan/application register.

Frequently Asked Questions (FAQs)

Do you have additional questions? Please check out the FAQs.


For technical questions about reporting HMDA data collected in or after 2017 use this form or email

source :

CFPB Issues Updated Small Entity Compliance Guide

1. Introduction For more than 30 years, federal law required lenders to provide two different disclosure forms to consumers applying for a mortgage. The law also generally required two different forms at or shortly before closing on the loan. Two different federal agencies developed these forms separately, under two federal statutes: the Truth in Lending Act (TILA) and the Real Estate Settlement Procedures Act of 1974 (RESPA). The information on these forms was overlapping, and the language inconsistent. Consumers often found the forms confusing, and lenders and settlement agents found the forms burdensome to provide and explain. The Dodd-Frank Wall Street Reform and Consumer Protection Act (Dodd-Frank Act) directed the Consumer Financial Protection Bureau (Bureau) to integrate the mortgage loan disclosures under TILA and RESPA Sections 4 and 5. Section 1032(f) of the Dodd-Frank Act mandated that the Bureau propose for public comment rules and model disclosures that integrate the TILA and RESPA disclosures by July 21, 2012. The Bureau satisfied this statutory mandate and issued proposed rules and forms on July 9, 2012. To accomplish this, the Bureau engaged in extensive consumer and industry research, analysis of public comment, and public outreach for more than a year. After issuing the proposal, the Bureau conducted a large-scale quantitative study of its proposed integrated disclosures with approximately 850 consumers, which concluded that the Bureau’s integrated disclosures had on average statistically significant better performance than the pre-existing disclosures under TILA and RESPA. On December 31, 2013, the Bureau published a final rule with new, integrated disclosures – “Integrated Mortgage Disclosures Under the Real Estate Settlement Procedures Act (Regulation X) and the Truth In Lending Act (Regulation Z)” (TILA-RESPA Final Rule). On January 20, 2015 and July 21, 2015, the Bureau issued amendments to the TILA-RESPA Final Rule. Additionally, the Bureau published technical corrections on December 24, 2015, and a

correction to supplementary information on February 10, 2016. On July 7, 2017, the Bureau issued further amendments intended to formalize guidance, and provide greater clarity and certainty (2017 TILA-RESPA Rule or 2017 amendments).1 The 2017 amendments were published in the Federal Register on August 11, 2017. The TILA-RESPA Final Rule, the amendments, and corrections are collectively referred to as the TILA-RESPA Rule in this Guide. The TILA-RESPA Rule provides a detailed explanation of how the forms should be filled out and used. The Good Faith Estimate (GFE) and the initial Truth-in-Lending disclosure (initial TIL) were combined into a single form, the Loan Estimate. Similar to those forms, the Loan Estimate form is designed to provide disclosures that will be helpful to consumers in understanding the key features, costs, and risks of the mortgage loan for which they are applying, and must be provided to consumers no later than the third business day after they submit a loan application. Second, the HUD-1 and final Truth-in-Lending disclosure (final TIL and, together with the initial TIL, the Truth-in-Lending forms) were combined into another form, the Closing Disclosure, which is designed to provide disclosures that will be helpful to consumers in understanding all of the costs of the transaction. This form must be provided to consumers at least three business days before consummation of the loan. The forms use clear language and design to make it easier for consumers to locate key information, such as interest rate, monthly payments, and costs to close the loan. The Loan Estimate and Closing Disclosure forms also provide more information to help consumers decide whether they can afford the loan and to facilitate comparison of the cost of different loan offers, including the cost of the loans over time. The TILA-RESPA Rule applies to most closed-end consumer mortgages. It does not apply to home equity lines of credit (HELOCs), reverse mortgages, or mortgages secured by a mobile home or by a dwelling (other than a cooperative unit) that is not attached to real property.2 Itdoes not generally apply to loans made by persons who are not considered “creditors” under TILA.3 Generally, the TILA-RESPA Rule’s provisions were effective on October 3, 2015. The December 2015 corrections were effective on December 24, 2015, and the February 2016 corrections were effective on February 10, 2016. The 2017 amendments are effective and will be incorporated into the Code of Federal Regulations on October 10, 2017. However, compliance with the 2017 amendments is not mandatory on the effective date. Generally, compliance with the amendments is only mandatory for transactions for which a creditor or mortgage broker receives an application on or after October 1, 2018. However, the requirements for the Escrow Cancellation Notice (Escrow Closing Notice) and Mortgage Servicing Transfer Notice Partial Payment Policy Disclosure (Partial Payment Policy Disclosure) provided post-consummation apply starting October 1, 2018 without regard to when the creditor or mortgage broker receives the application. The 2017 amendments include an optional compliance period, which begins on October 10, 2017 and is for transactions for which a creditor or mortgage broker receives an application prior to October 1, 2018. During this period, early compliance with the 2017 amendments is allowed, but not required. Additionally, if a creditor or mortgage broker receives an application prior to October 1, 2018, optional compliance continues to apply to that transaction after October 1, 2018 (except as noted regarding the Escrow Closing Notice and Partial Payment Policy Disclosures). During the optional compliance period (beginning on October 10, 2017 and for transactions with applications received prior to October 1, 2018), the provisions of the 2017 amendments can be implemented all at once or phased in over this period. For example, if a creditor chooses to phase in the 2017 amendments, those changes can be phased-in over the course of a transaction or by application date. Notwithstanding this flexibility, a person cannot phase in the 2017 amendments in a way that would violate provisions of Regulation Z that are not being changed.4 The information provided in this Guide incorporates the changes and clarifications from the 2017 amendments, and explains the TILA-RESPA Rule as of the mandatory compliance date on October 1, 2018. To understand the rule as it existed prior to the 2017 amendments, please review version 4.0 of the Guide, available on the Bureau’s website at

1.1 What is the purpose of this guide? The purpose of this Guide is to provide an easy-to-use summary of the TILA-RESPA Rule. This Guide also highlights issues that small creditors, and those that work with them, might find helpful to consider when implementing the TILA-RESPA Rule. This Guide also meets the requirements of Section 212 of the Small Business Regulatory Enforcement Fairness Act of 1996, which requires the Bureau to issue a small-entity compliance guide to help small entities comply with these new regulations. You may want to review your processes, software, contracts with service providers, or other aspects of your business operations in order to identify any changes needed to comply with this rule. Changes related to this rule may take careful planning, time, or resources to implement. This Guide will help you identify and plan for any necessary changes. To support rule implementation, the Bureau continues to coordinate with other agencies, publish and update plain-language guides, and publish updates to the Official Interpretations, if needed.


This Guide summarizes the TILA-RESPA Rule, but it is not a substitute for the rule. Only the rule and its Official Interpretations (also known as commentary) can provide complete and definitive information regarding its requirements. The discussions below provide citations to the sections of the TILA-RESPA Rule on the subject being discussed. Keep in mind that the Official Interpretations, which provide detailed explanations of many of the TILA-RESPA Rule’s requirements, are found after the text of the rule and its appendices. The interpretations are arranged by rule section and paragraph for ease of use. The 2013 Final Rule and the Official Interpretations, and related corrections and amendments, are available at The focus of this Guide is the TILA-RESPA Rule. This Guide does not discuss other federal or state laws that may apply to the origination of closed-end credit. The content of this Guide does not include any rules, bulletins, guidance, or other interpretations issued or released after the date on the Guide’s cover page. At the end of this Guide, there is more information about the TILA-RESPA Rule and related implementation support from the Bureau. 1.2 Who should read this guide? If your organization originates closed-end residential mortgage loans, you may find this Guide helpful to determine your compliance obligations for the mortgage loans you originate. This Guide may also be helpful to settlement service providers, secondary market participants, software providers, and other companies that serve as business partners to creditors.

1.3 Where can I find additional resources that will help me understand the TILARESPA Rule? Resources to help you understand and comply with the Dodd-Frank Act mortgage reforms and our regulations, including downloadable compliance guides, are available through the CFPB’s website at On this website, we also offer the ability to sign up for an email distribution list 20 CONSUMER FINANCIAL PROTECTION BUREAU SMALL ENTITY COMPLIANCE GUIDE: TILA-RESPA INTEGRATED DISCLOSURE RULE v 5.0 through which we announce additional resources and tools as they become available. The eRegulations tool, which is available at, includes an unofficial version of Regulation Z (12 CFR part 1026), in which the TILA-RESPA Rule is codified. The tool provides updated versions of the regulatory text and commentary in a single location. If after reviewing these materials, as well as the regulation and official commentary, you have a specific regulatory interpretation question about the TILA-RESPA Rule, you can submit it to us on our website at Please understand that the responses we provide are not official interpretations of the Bureau and are not a substitute for formal legal counsel or other compliance advice. Email comments about the Guide to Your feedback is crucial to making this Guide as helpful as possible. The Bureau welcomes your suggestions for improvements and your thoughts on its usefulness and readability. The Bureau is particularly interested in feedback relating to:  How useful you found this Guide for understanding the TILA-RESPA Rule;  How useful you found this Guide for implementing the TILA-RESPA Rule at your business; or  Suggestions you have for improving the Guide, such as additional implementation tips. 21 CONSUMER FINANCIAL PROTECTION BUREAU SMALL ENTITY COMPLIANCE GUIDE: TILA-RESPA INTEGRATED DISCLOSURE RULE v 5.0 2. Overview of the TILARESPA Rule 2.1 What is the TILA-RESPA Rule about? The TILA-RESPA Rule consolidates four disclosure forms that were required under TILA and RESPA for closed-end credit transactions secured by real property or cooperative unit into two forms: a Loan Estimate that must be delivered or placed in the mail no later than the third business day after receiving the consumer’s application, and a Closing Disclosure that must be provided to the consumer at least three business days prior to consummation.

2.2 What transactions does the rule cover? (§ 1026.19(e) and (f)) The TILA-RESPA Rule applies to most closed-end consumer credit transactions secured by real property or a cooperative unit (regardless of whether state law classifies it as real property). Credit extended to certain trusts for tax or estate planning purposes is not exempt from the TILA-RESPA Rule. (Comment 3(a)-10). However, some specific categories of loans are excluded from the rule. Specifically, the TILA-RESPA Rule does not apply to HELOCs, reverse mortgages, or mortgages secured by a mobile home or by a dwelling (other than a cooperative unit) that is not attached to real property. (§ 1026.19(e) and (f)). For further discussion of coverage, see section 4 below.

2.3 What are the record retention requirements for the TILA-RESPA Rule? (§ 1026.25) The creditor must retain copies of the Closing Disclosure (and all documents related to the Closing Disclosure) for five years after consummation. The creditor, or servicer if applicable, must retain the post-consummation Escrow Closing Notice and Partial Payment Policy Disclosure for two years. For additional information, see section 16 below. For all other evidence of compliance with the Integrated Disclosure provisions of Regulation Z (including the Loan Estimate) creditors must maintain records for three years after consummation of the transaction. Creditors are obligated to obtain and retain a copy of the completed Closing Disclosures provided separately by a non-creditor settlement agent to a seller under 1026.38(t)(5), but are not obligated to collect underlying seller-specific documents and records from that third-party settlement agent to support these disclosures. To the extent the creditor does receive documentation related to the seller’s disclosure, such as when the creditor is the settlement agent, or when seller-related documents are provided to the creditor by a third-party settlement agent along with the completed disclosure, the creditor should adhere to the record retention requirements that apply to the Closing Disclosure. 2.4 What are the record retention requirements if the creditor transfers or sells the loan? (§ 1026.25) If a creditor sells, transfers, or otherwise disposes of its interest in a mortgage and does not service the mortgage, the creditor shall provide a copy of the Closing Disclosure to the new owner or servicer of the mortgage as a part of the transfer of the loan file. Both the creditor and the new owner or servicer shall retain the Closing Disclosure for the remainder of the five-year period.

2.5 Is there a requirement on how the records are retained? Regulations X and Z permit, but do not require, electronic recordkeeping. Records can be maintained by any method that reproduces disclosures and other records accurately, including computer programs. (Comment 25(a)-2)

3. Effective Date 3.1 When do I have to start following the TILA-RESPA Rule and using the Integrated Disclosures? The TILA-RESPA Rule generally took effect on October 3, 2015 for applications received on or after October 3, 2015. The Integrated Disclosures (i.e., the Loan Estimate and the Closing Disclosure) must be provided by a creditor or mortgage broker that receives an application from a consumer for a closed-end credit transaction secured by real property on or after October 3, 2015.5 Creditors were required to use the GFE, HUD-1, and Truth-in-Lending forms for applications received prior to October 3, 2015. 3.2 Are there any requirements that take effect regardless of when an application was received? Yes. As discussed in section 13, below, the TILA-RESPA Rule includes some restrictions on certain activity prior to a consumer’s receipt of the Loan Estimate. These restrictions took effect on the calendar date October 3, 2015, regardless of when an application was received. These activities include:

Imposing fees on a consumer before the consumer has received the Loan Estimate and indicated an intent to proceed with the transaction (§ 1026.19(e)(2)(i));  Providing written estimates of terms or costs specific to consumers before they receive the Loan Estimate without a written statement informing the consumer that the terms and costs may change (§ 1026.19(e)(2)(ii)); and  Requiring the submission of documents verifying information related to the consumer’s application before providing the Loan Estimate. (§ 1026.19(e)(2)(iii)) Beginning on October 1, 2018, a creditor must provide the Escrow Closing Notice and Partial Payment Policy Disclosure when required, regardless of when the creditor or mortgage broker received the application.6 (Comment 1(d)(5)-1) For example, for an application received on October 10, 2010, if the escrow account was cancelled on April 14, 2020, the creditor would be required to give the Escrow Closing Notice, because the cancellation occurred after October 1, 2018 and after that time, Escrow Closing Notice and Partial Payment Policy Disclosure are given regardless of when the application was received. (Comment 1(d)(5)-1.v.E) For more information about the Escrow Closing Notice and Partial Payment Policy Disclosure, see section 16 of this Guide. 6 Prior to October 1, 2018, it is acceptable for a creditor to give the Escrow Closing Notice and Partial Payment Policy Notice only to transactions where the application was received on or after October 3, 2015. For example, for an application received on October 10, 2010, if the escrow account was cancelled on December 19, 2016, the creditor would not be required to provide the Escrow Closing Notice because the application was received before October 3, 2015 and the cancellation occurred prior to October 1, 2018. A consumer may indicate an intent to proceed in any manner the consumer chooses, unless the creditor requires a particular manner of communication. (§ 1026.19(e)(2)(i)(A)). For further discussion on intent to proceed, see section 13.5 below. 26 CONSUMER FINANCIAL PROTECTION BUREAU SMALL ENTITY COMPLIANCE GUIDE: TILA-RESPA INTEGRATED DISCLOSURE RULE v 5.0 3.3 Can a creditor use the Integrated Disclosures for applications received before October 3, 2015? No. For transactions where the application is received prior to October 3, 2015, creditors will still need to follow the disclosure requirements under Regulations X and Z as they existed before the Integrated Disclosures were created by the TILA-RESPA Rule. A creditor will need to use the Truth-in-Lending disclosures, GFE, HUD-1, etc., as applicable, for those transactions. 27 CONSUMER FINANCIAL PROTECTION BUREAU SMALL ENTITY COMPLIANCE GUIDE: TILA-RESPA INTEGRATED DISCLOSURE RULE v 5.0 4. Coverage 4.1 What transactions are covered by the TILA-RESPA Rule? (§§ 1024.5; 1026.3; and 1026.19) The TILA-RESPA Rule applies to most closed-end consumer credit transactions secured by real property or a cooperative unit (regardless of whether state law classifies it as real property), but does not apply to:  HELOCs;  Reverse mortgages; or  Chattel-dwelling loans, such as loans secured by a mobile home or by a dwelling (other than a cooperative unit) that is not attached to real property. Consistent with existing rules under TILA, the TILA-RESPA Rule also generally does not apply to loans made by a person or entity that is not considered a creditor under Regulation Z. (§ 1026.2(a)(17)) There is also a partial exemption for certain transactions associated with housing assistance loan programs for low- and moderate-income consumers. (§ 1026.3(h)) However, certain types of loans that are subject to TILA but are not subject to RESPA are subject to the TILA-RESPA Rule’s integrated disclosure requirements, including:  Construction-only loans; and  Loans secured by vacant land or by 25 or more acres.

Source :

The 10 Most Useful Things in Excel

Every Excel user has their own favorite tip. So we decided to bring some method, data, and science to bear in this hotly debated topic.

We consulted the research of 30 of the world’s leading Excel experts, our own internal Excel experts, grouped up and consolidated the emerging features, and applied a scoring methodology and index to produce a definitive ordered list of the 100 most useful Excel features, hacks, tips and tricks.

These are the top 10 Excel tips as determined by our ranking.

Skills are ranked by usefulness and given a score out of 100. Each skill also has a difficulty rating (out of 5) based on the complexity and sophistication of the feature. Finally, we provide the average time in minutes it takes the average learner to go from no knowledge to proficient.

The videos and gifs inserted in this list were created by Business Insider.

1. Conditional Formatting

Utility: 100
Difficulty: 3
Learn in 180 mins

Making sense of our data-rich, noisy world is hard but vital. Used well, Conditional Formatting brings out the patterns of the universe, as captured by your spreadsheet. That’s why Excel experts and Excel users alike vote this the #1 most important feature. This can be sophisticated. But even the simplest colour changes can be hugely beneficial. Suppose you have volumes sold by sales staff each month. Just three clicks can reveal the top 10% performing salespeople and tee up an important business conversation.

2. PivotTables

Utility: 95
Difficulty: 3
Learn in 240 mins

At 4 hours to get to proficiency, you may be put off learning PivotTables but don’t be. Use them to sort, count, total or average data stored in one large spreadsheet and display them in a new table, cut however you want. That’s the key thing here. If you want to look only at sales figures for certain countries, product lines or marketing channels, it’s trivial. Warning: make sure your data is clean first!

3. Paste Special

Utility: 88
Difficulty: 3
Learn in 10 mins

Grabbing (ie Copying) some data from one cell and pasting it into another cell is one of the most common activities in Excel. But there’s a lot you might copy (formatting, value, formula, comments, etc) and sometimes you won’t want to copy all of it. The most common example of this is where you want to lose the formatting – the place this data is going is your own spreadsheet with your own styling.

4. Add Multiple Rows

Utility: 87
Difficulty: 0
Learn in 10 mins

Probably one of the most frequently carried out activities in spreadsheeting. Ctrl Shift + is the shortcut, but actually it takes longer, so Right Click is what we recommend. And if you want to add more than one, select as many rows or columns as you’d like to add and then Right Click and add.

5. Absolute References

Utility: 85
Difficulty: 2
Learn in 15 mins

Indispensable! The dollar in front of the letter fixes the column, the dollar sign in front of number fixes the row F4 toggles through the four possible combinations.

6. Print Optimisation

Utility: 84
Difficulty: 3
Learn in 120 mins

Everyone has problems printing from Excel. But just imagine if what you printed were always just what you intended to print. It IS actually possible. There are a few components to this though: print preview, fit to one page, adjusting margins, print selection, printing headers, portrait vs landscape and spreadsheet design. Invest the time to get comfortable with it. You’ll be carrying out this task many, many times in your working life.

7. Extend formula across/down

Utility: 84
Difficulty: 1
Learn in 5 mins

The beauty of Excel is its easy scalability. Get the formula right once and Excel will churn out the right calculation a million times. The + cross hair is handy. Double clicking it will take it all the way down if you have continuous data. Sometimes a copy and paste (either regular paste or paste formulas) will be faster for you.

8. Flash Fill

Utility: 84
Difficulty: 2
Learn in 30 mins

Excel developed a mind of its own in 2013. Say you have two columns of names and you need to construct email addresses from them all. Just do it for the first row and Excel will work out what you mean and do it for the rest. Pre-2013 this was possible but relied on a combination of functions (FIND, LEFT, &, etc). This is much faster and WILL impress people.


Utility: 82
Difficulty: 4
Learn in 45 mins

This is one of the most powerful combinations of Excel functions. You can use it to look up a value in a big table of data and return a corresponding value in that table. Let’s say your company has 10,000 employees and there’s a spreadsheet with all of them in it with lots of information about them like salary, start date, line manager etc. But you have a team of 20 and you’re only really interested in them. INDEX-MATCH will look up the value of your team members (these need to be unique like email or employee number) in that table and return the desired information for your team. This is worth getting your head around this as it is more flexible and therefore more powerful than VLOOKUPs.

10. Filters

Utility: 81
Difficulty: 2
Learn in 60 mins

Explore data in a table quickly. Filtering effectively hides data that is not of interest. Usually there’s a value e.g. ‘Blue cars’ that you’re looking for and Filters will bring up those and hide the rest. But in more modern versions of Excel, you can now also filter on number values (e.g. is greater than, top 10%, etc), and cell color. Filtering becomes more powerful when you need to filter more than one column in combination e.g. both colors and vehicles to find your blue car.


8 Free Excel Add-Ins to Make Visually Pleasing Spreadsheets

Microsoft Excel has an array of useful features for creating powerful spreadsheets with charts and tables. But, when you want to take your spreadsheet to the next level, beyond fancy text or themes, you may need the help of an add-in.

With these handy tools, you can create more visually stimulating representations of your data. For printing, presenting, or providing a better view of your spreadsheets and what they contain, check out these awesome Excel add-ins.

1. Excel Colorizer

For an easy way to add color to your spreadsheet data, the Excel Colorizer add-in works great. To make your data simpler to read, just pick the colors and pattern and let the add-in do the rest of the work.

You can choose a type from uniform, horizontal, vertical, or matrix and pick your four colors. Then, adjust the interpolation to smooth or linear and the color space to RGB or HSV. You can also pick the pattern from interlaced or waves. So, select your data, make it look amazing, and hit Colorize! to finish.

2. Radial Bar Chart

While Microsoft Excel offers many chart options, Radial Bar Chart gives you one more. This add-in follows its name in providing exactly what it says: a radial bar chart. Access the add-in, select the cells containing your data, and then adjust the chart as you like.

You can pick the labels, numbers, and set a title. Hit Save and your chart will be created automatically. Move or resize your chart and run your mouse over the bars to display the data for each. The add-in is quick, simple, and gives you another chart style option for your data.

3. HierView

For displaying an organizational chart, HierView is the add-in for you. Your data should include a row identifier with the information included across the columns. Then, configure your data with filtering, automatic refreshing, column names, and data per node.

Once your chart is complete, you can zoom in or out, move in all four directions, or fit the chart to the plotted area. You can also move the entire chart or resize it if needed. Adjust the settings or get help at any time with the convenient buttons at the top of the chart.

4. Bubbles

Would your data look better in a bubble? If so, then Bubbles is the add-in you should try. This interactive bubble chart tool lets you display information in a unique way. Just open the add-in and select your table of data. When the chart is created, you can move the bubbles around while presenting or leave them as they are.

8 Types of Excel Charts & When You Should Use Them

Bubbles has a few settings you can adjust for basic or detailed mode, color range, and the title and column displays. You can also use the keyboard shortcuts shown at the bottom of the chart for things like hiding or showing more information or bouncing the bubbles about.Graphics are easier to grasp than text and numbers. Charts are a great way to visualize numbers. We show you how to create charts in Microsoft Excel and when to best use what kind.

5. Bing Maps

If adding a map to your spreadsheet is useful, the Bing Maps add-in makes it simple. Your data can include addresses, cities, states, zip codes, or countries as well as longitude and latitude. Just open the add-in, select your data cells, and click the Location button on the map.

The Bing Maps tool is ideal for displaying numeric data related to locations. So, if you work in sales, you can show new markets to cover or if you work for a company with multiple locations, you can present those facilities to clients clearly.

6. Geographic Heat Map

When a heat map is what you really need, the Geographic Heat Map add-in is terrific. From the same developer as the Radial Bar Chart add-in, you have similar settings. Select your data, choose a U.S. or world map, pick your column headings, and add a title.

You can reselect the data set if you add more columns or rows and if you change a value already in the set, you will see the heat map update automatically. For a fast glance at data related to countries, states, or regions, you can pop this heat map into your spreadsheet easily.

7. Pickit Free Images

For adding photos and other images to your spreadsheets, Pickit Free Images is the perfect tool. Once you access the add-in, you have a few options for finding the image you need. You can do a keyword search, browse through collections, or pick a category from a huge selection.

Click a spot to put it in your spreadsheet, select the image you want, and hit the Insert button. You can move, resize, or crop images as needed. And, if you create a free Pickit account, you can mark favorites to reuse and follow fellow users or categories to stay up-to-date on new uploads.

8. Web Video Player

Maybe your company has videos on YouTube or tutorials on Vimeo that would be beneficial to your workbook. With the Web Video Player add-in, you can pop a video from either of these sources right into your spreadsheet.

Open the add-in and then either enter the URL of the video or click to visit YouTube or Vimeo to obtain the link. Hit the Set Video button and you are done. Then, when you open the spreadsheet, the video is there and ready for you to click the Play button.

For a one-time $5 fee, you can set your videos to play automatically or start and end at specific spots in the clip. This option is included within the add-in.

Find, Install, and Use Add-Ins

You can access the add-ins store easily from within Excel. Open a workbook, select the Insert tab, and click Store. When the Office Add-Ins Store opens, browse by category or search for a specific add-in. You can also visit the Microsoft AppsSource site on the web and browse or search there.

15 Excel Add-Ins to Save Time on Your Business TasksDid you know Microsoft Office, including Excel and Word, supports add-ins? Excel add-ins can help you get through your tasks quicker because you never have to leave your Excel workbook.READ MORE

When you find the add-in you want, you can select it for further details or simply install it. However, it is wise to view the description first to check the terms and conditions, privacy statements, and system requirements.


Mortgage Advertising Compliance Refresher

Regulation N – Mortgage Acts and Practices – Advertising was issued by the Consumer Financial Protection Bureau (CFPB) to implement requirements of the Credit Card Accountability and Responsibility and Disclosure Act of 2009 (CARD Act) and Dodd-Frank Wall Street Reform and Consumer Financial Protection Act of 2010 (Dodd-Frank Act). The Federal Trade Commission oversees compliance with Regulation N for entities over which it exercises jurisdiction.

Regulation N defines mortgage credit product as any form of credit that is secured by real property or a dwelling and that is offered or extended to a consumer primarily for personal, family, or household purposes. For a mortgage credit product, the regulation prohibits certain material representations in any commercial communication about any term of a mortgage credit product, including:

The interest charged for the product – amount of interest in the monthly payment, loan amount, or total amount due;

The annual percentage rate, simple annual rate, periodic rate, or any other rate;

Information about the existence, nature, or amount of fees or costs to the consumer for the mortgage credit product;

Information about the existence, nature, or amount of fees or costs to the consumer for any additional product that may be sold in conjunction with the mortgage credit product;

The terms, amounts, payments, or other requirements relating to taxes or insurance associated with the mortgage credit product;

Any prepayment penalty associated with the mortgage credit product;

Any comparison between a rate or payment that will be available for a period less than the full length of the mortgage credit product and an actual or hypothetical rate or payment;

The type of mortgage credit product;

The amount of the obligation and the nature of cash or credit components of the obligation;

The existence, number, amount, or timing of any minimum or required payments;

The potential for default under the mortgage credit product and circumstances of default;

The effectiveness of the mortgage credit product in helping the consumer resolve difficulties in paying debts;

The association of the provider of the mortgage credit product with any other person or program;

The source of any commercial communication about the mortgage credit product;

The right of the consumer to reside in the dwelling that is the subject of the mortgage credit product;

The consumer’s ability or likelihood to obtain any mortgage credit product or term;

The consumer’s ability or likelihood to obtain a refinancing or modification of any mortgage credit product or term; and

The availability, nature, or substance of counseling services or any other expert advice offered to the consumer regarding any mortgage credit product or term..


New Compliance Rules on Reverse Mortgages

In October, new regulations went into effect regarding reverse mortgages. The reason for the changes was that the federal Department of Housing and Urban Development was facing large losses associated with their guarantees to reverse mortgage borrowers and lenders.

One of the things borrowers like about reverse mortgages is that the lender can never recover more than the fair market value of the home, regardless of the amount of money owed on the mortgage.

Borrowers may stay in their home as long as they maintain the property and pay the required real estate taxes and homeowner’s insurance. If they fail to do any of these things, or if they voluntarily leave the home (or vacate the property for 12 months, for health reasons or for any other reason), the lender takes possession and may sell the home.

One of the things lenders like about reverse mortgages is that if the outstanding loan exceeds the fair market value of the property, the lender does not incur a loss on the transaction. HUD bears the loss.

HUD has been facing larger losses recently, so it has made the following changes:

  • Previously, HUD charged an upfront insurance premium of 0.5 percent for borrowers who took less than 60 percent of the maximum loan amount, and 2.5 percent of for those who took more than 60 percent. It now takes 2 percent for all loans.
  • Previously, HUD imposed an annual mortgage insurance premium (MIP) of 1.25 percent of the loan outstanding. Now, new borrowers pay an annual insurance fee of 0.5 percent.
  • The maximum loan amount has also been reduced. Previously, a borrower could borrow 60 or 70 percent of the property value; now that maximum is based on the applicant’s age, the mortgage rate and the property value. It is estimated that the amount that can be borrowed now is approximately 5 percent less than what was available before the new regulations.

One strategy for using a reverse mortgage is to take advantage of the growth of the available line of credit. (Some call this taking a “standby” reverse mortgage.) The credit line grows based on the loan rate plus the annual insurance premium. Since the annual insurance premium has been reduced, the line of credit will now grow at a slower pace.

In summary, borrowers will now find that they can borrow less under the new regulations, and the line of credit will grow more slowly.

On the other hand, the reduction in the annual MIP to 0.5 percent means that the homeowner’s loan balance will increase much more slowly, and accordingly the equity in the home will be retained.

The changed regulations are not a “game-changer” regarding the benefits of obtaining a reverse mortgage. However, they are factors to take into consideration. If you are obtaining a reverse mortgage in order to take advantage of the flexibility of the line of credit, you should understand how the line of credit is computed and how large it will be based on how long you expect to reside in the home.

For those borrowers planning to get cash up front, reverse mortgages may still be advantageous. Front-end costs and interest rates vary between lenders, so you have to comparison shop. In addition, do not base your decision solely on the mandated review/approval of a certified HUD adviser. You should have the proposal or contract reviewed by your own financial adviser or attorney. Any decision should be consistent with your long-term financial plan.


CFPB – Don’t Do What Zillow Did

Dreaming of an oceanfront condo in Southern California or a cabin escape on the slopes in Aspen? Or perhaps you’re moving to a new town and are looking at apartments? No matter what type of place you’re searching for, chances are you’ve used Zillow.

Launched in 2006, the Seattle-based real estate giant is a free platform that provides consumers with “data, inspiration and knowledge.” So, how did this free, consumer-dedicated platform find itself in the crosshairs of the Consumer Financial Protection Bureau?

Simple: government oversight run amok.

Originally proposed by Elizabeth Warren in 2007 and established in 2011, the CFPB was intended to prevent financing companies from treating consumers unfairly. But the bureau has a history of overstepping that mandate, with its aggressive tactics hurting both consumers and businesses alike.

Over the past several years, the CFPB has used anti-kickback laws in the Real Estate Settlement Procedures Act (RESPA) to slap companies with multi-million dollar fines for engaging in historically-common business practices. The CFPB regularly sues mortgage lenders and real estate agencies that have simply partnered in routine marketing service agreements (MSAs), whereby a realtor advertises or recommends a lender or broker.

Two years ago, the CFPB trained its sights on Zillow and began investigating the online platform for allowing advertising by real estate agents and mortgage lenders with MSAs.

To provide free services to consumers, Zillow sells ad space to realtors, rental companies, builders, lenders, and others in the home industry. Zillow simply provides a neutral platform for businesses to reach customers interested in real estate to advertise.

Monthly advertising accounts for around 70 percent of Zillow’s revenue, nearly $190 million in the second quarter of 2017 alone.

The CFPB is now demanding that the real estate giant settle to the tune of millions of dollars or face legal action for allowing realtors and lenders with MSAs to advertise on its site.

Zillow, baffled by the allegations, reached out to the CFPB to discuss the matter, but has not received a formal response. The agency also declined to comment on news stories related to its vague charges.

In a recent article about the case in GeekWire, a Zillow spokesperson said the CFPB has “failed to give concrete feedback, and we’re aware of no evidence of consumer harm or any actual consumer complaints…this is a clear overreach, and one of main examples of the CFPB legislating by fiat.”

Additionally, the agency’s logic in going after Zillow is questionable.

If the CFPB’s priority is to protect consumers—as one would assume given the bureau’s name—going after a website such as Zillow is puzzling. Zillow and similar websites provide valuable tools to consumers at no cost. They help would-be homebuyers or sellers access important information critical to negotiating the terms of a real estate sale or purchase. And it would be a stretch to argue that Zillow somehow restricts consumers’ access to other real estate agents or lenders simply by allowing businesses to purchase advertising space.

Given these facts, why would the CFPB push so hard to settle this case with such scant evidence?

It appears CFPB’s battle against Zillow is more about politics and less about consumer protections.

Late last year, mortgage company PHH Corp. successfully defended a $109 million fine imposed against them by the CFPB. The court lambasted the CFPB’s actions as a clear overreach.

After such a ruling, the CFPB should have pumped the brakes on the anti-kickback witch-hunt, but CFPB Director Richard Cordray sees it differently.

Cordray, who has dramatically expanded the intended scope of the CFPB during his tenure, is rumored to be plotting a run for governor in Ohio next year. A “win” against Zillow provides another campaign talking point to brand himself as a top consumer watchdog.

No matter the purpose of the CFPB’s new hunt for RESPA violations, the vague anti-kickback statute confuses and harms the real estate industry and consumers.

Baseless allegations against Zillow and others invalidate the bureau’s claims of consumer protection. The CFPB is unconstitutional and its unaccountable overreach has no place in our government.

Cameron DeSanti is a student at George Washington University and a former policy intern at Americans for Prosperity.

If you would like to write an op-ed for the Washington Examiner, please read our


How 2018 HMDA Changes Impact Your Lending Operations

You want the good news or the bad news on HMDA? Let’s go with the good news: After collecting expanded data on borrowers under the Home Mortgage Disclosure Act rule changes, lenders are going to have greater insight than ever before on their lending practices. The bad news? So is everyone else.

“Your lending practices are about to become a wide-open book,” said Mitchel Kider, chairman and managing partner, Weiner Brodsky Kider PC.

“When there is more information available to the public and to regulators, there will be a lot more scrutiny and potential liability.”

Kider was one of the experts who spoke Monday about HMDA rule implementation at the Mortgage Bankers Association’s 2017 Annual Conference and Expo. The panel’s unofficial theme, as outlined by John Haring, director of compliance enablement at Ellie Mae, was “we scare because we care,” due to the anxiety some lenders feel about what the HMDA changes mean for them.

Updated HMDA requirements involve a significant expansion of data collection, including 25 new fields that have to be reported. These changes are the latest attempts by federal regulators to ensure fair lending by identifying possible discriminatory patterns.

In the past, Kider said, regulators looked at HMDA data as part of their fair lending review, but the data wasn’t comprehensive enough by itself to determine discrimination. With the HMDA changes, the data will provide a complete picture for regulators, and lending pattern outliers will trigger an investigation.

“Now, all that data is available in a consistent electronic format and it will be very easy for others to compare you to peers, to drill down to specific information that was more difficult to obtain before,” Kider said.

That new data includes pricing information, origination charges, discount points, lender credits, interest rates, combined LTV ratio, credit score and DTI ratio.

“Fair lending will receive greater visibility when federal agencies and private litigants have additional data under HMDA to support their analysis,” Kider said.

In addition, poor data quality by itself can be the basis for action against lenders.

“Substance is important, but the accuracy of HMDA data is independently important as well,” Kider said. “Does this [poor data quality] mean you have to resubmit? No, this means civil money penalties as well. It is a reflection of having a poor compliance management system in all other areas.”

Maurice Jourdain-Earl, managing director at ComplianceTech, compared the HMDA implementation to an iceberg, with the expanded data points making up an unseen, bulky mass under the water line.

“Things are about to get very real,” Jourdain-Earl said. “We’ve been tiptoeing with HMDA data and a lot of lenders go through the process of collecting and submitting, but many don’t do what they need to in order to understand what it says. That need is about to become paramount. The new HMDA rules will be a game changer. We are going to experience a major paradigm shift, and HMDA can be either friend or foe.”

All of the HMDA panelists, which also included Richard Andreano Jr., practice group leader, mortgage banking group at Ballard Spahr, saw the HMDA changes as an opportunity for lenders to see, understand and hopefully improve, their lending practices.

“The responsible thing for lenders to do, is understand what their data is saying. How can you use your data to establish a process of benchmarking where you compare your business process and performance metrics to industry norms and best practices,” Jourdain-Earl said.

Part of the new data collected includes the unique identifier for the loan officer, which means regulators will have transparency into the lending practices of not just companies or branches or managers, but all the way down to the level of loan officer.

“With new HMDA data, regulators will be able to more scientifically identify a peer, and a peer with a similar business model. Are their outcomes the same or different than yours?” Jourdain-Earl asked.

In addition to the challenges of the HMDA reporting, Haring pointed out the difficulties of getting ready for implementing the HMDA changes while the CFPB is still adjusting requirements, including changes to the filing instruction guides.

The bureau is still developing the platform that lenders will use to submit HMDA data starting on March 1, 2018, but has not provided a specific date when it will be completed.

“Lenders want to test against that platform today,” Haring said.


Web Statistics