Microsoft Excel: How to Round Dates to Current or Next Beginning of the Month

If you would like to unify the dates in Excel to be either in the beginning of current or next month, below formula is the solution.

Below example shows that Actual Date on 1/5/2014 (column A2) has a Controlled Date of 1/1/2014, because Jan-1st is less than 20th of the month, as specified in the formula. You can always change the threshold based on your needs.
Second record of Actual Date is 1/25/2014 (which is greater than 20th of the month), therefore, rounded up to the next month, as specified.

=IF(DAY(A2)<20,DATE(YEAR(A2),MONTH(A2),1),DATE(YEAR(A2),MONTH(A2)+1,1))

Complete Homebuyer's Checklist

1. Find a Buyer's Realtor/Agent

• A buyer's agent will represent only you and have a fiduciary responsibility to look out for your best interests.
• Buyer's agents may ask you to sign a buyer's broker agreement (not common practice), but it is the seller who pays the commission.
• Interview agents until you find an agent you trust and with whom you feel comfortable.
• Common questions:
• How long he/she been a realtor/agent?
• Is he/she part-time or full-time realtor/agent?
• How many homes have he/she sold in the past 6 month or a year?
• Highly recommended to find a local realtor who knows the area you are interested in. 

2. Get Pre-Qualified / Pre-Approved

• Order a free credit report online and fix mistakes, if any.
• Ask your agent for a referral to a mortgage broker, but also compare rates offered by your own bank and / or credit union.
• Ask the lender to give you a loan preapproval letter, which means it will verify your income and pull a credit report.
• Loan preapproval letter is required to submit an offer to a home.
• Determine your maximum loan amount, but choose only a mortgage type that you understand and a payment level with which you feel comfortable, which may very well be less than the maximum for which you are approved.
• Financial advise - keep the monthly loan payment at 30% (1/3 of your total monthly income) of your income, in order to have a well-balanced financial plan.
• To better understand your up-front cost, closing costs, ask your load officer for a good faith estimate.
• Good faith estimate - breaks down your monthly payment and you'll see everything that is included (not including utilities and other monthly bills).

3. Look at Homes for Sale

• Use web-sites like ZipRealty or MLS Listings to search for homes. You can also ask your agent to find homes for you.
• Check crime-rate from multiple sources.
• Setup alerts in area you are interested in to get news updates.
• Zillow let's you check the home value, historic price, and market trend and more.
• Ask your agent to give you MLS print-outs of comparable sales in your targeted neighborhood (aka: Competitive Market Analysis CMA).
• Competitive Market Analysis - gives you an idea of recently sold similar nearby homes; i.e. how much they were listed for vs. how much they were actually sold at, which provides a better range to narrow your bargain for negotiation.
• Consider all homes on the market, including fixer-uppers, REOs, foreclosures, short sales and those overpriced homes with longer Days On Market (DOM).
• Take detailed interior and exterior pictures of the visited homes that appeal to you.

4. Write a Purchase Offer (KNOW all your costs beforehand)

• Consider writing seller's market offers in sellers markets and buyer's market offers in buyer's markets.
• Select a home offer price based on the amount you feel a seller will accept or counter.
• Prepare for multiple offers if the home is considered desirable in a hot location.
• If your offer is rejected, ask your agent to explain why and don't repeat that mistake with your next offer.
• Items you will need to make an offer:
• Pre-approval letter from your Loan Officer.
• Earnest Money Deposit minimum of $1,000; the more Earnest Money Deposit you put in, the more serious your offer looks.
• Proof of funds available for down-payment; i.e. bank statement.
• Closing Costs Breakdown:
• Excludes down-payment and Earnest Money Deposit
• HOA Fee (applies to ONLY Condo and Townhomes)
• Appraisal Fee
• Inspection
• General Inspection
• Pest Inspection
• Roof Inspection
• Document Preparation Fees
• Credit Report Fee
• Loan Origination Fee
• Loan Discount Fee (optional, if buyer wants to pay lower rate for a fee)
• Primary Mortgage Insurance PMI (applies if down payment is less than 20%)
• Title Changes Fee
• Taxes
• With impound - collected 50% (6 months taxes) + title fees
• Without impound - not allowed for less than 20% down-payment
• Home Insurance
• 1st year insurance is paid up-front, then next year's annual amount is collected monthly as you go
• Home Warranty
• Sample Reports that Help better Prepare You:
• Purchase Agreement (Contract)
• Settlement Statement (HUD-1)
• Inspection Report
• Pest Report

5. Negotiate and Write Counter Offers

• Expect the seller to issue a counter offer.
• If the seller counters at full price, continue to negotiate.
• During offer negotiation, share personal information about your family to give the seller a reason to care about you.

6. Make an Earnest Money Deposit

• When your offer is accepted, deposit your earnest money check with the appropriate party.
• This is normally done at a Title company.
• Do not ever make your check payable to the seller, earnest money is always paid to the title company.
• Your offer should contain contingencies that will return your earnest money deposit to you if you cancel the contract.

7. Open Escrow / Order Title

• Your agent or transaction coordinator will open escrow and title, if the listing agent hasn't already done so.
• Ask for the escrow officer's name, phone and escrow file number.
• Give this information to your lender and your insurance agent.

8. Order Appraisal

• Your lender will require an advance payment for the appraisal.
• Sometimes, the lender will order the appraisal themselves.
• If you receive a low appraisal, discuss options with your agent.
• Ask for a copy of the appraisal.
• Further negotiate the price based on appraisal.

9. Comply With Lender Requirements

• Lenders may ask for additional information.
• Do not make home buying mistakes such as altering your financial situation while in escrow.
• If you made a full cash offer, changing into 30 year mortgage makes a huge difference.
• When the file is complete, the lender will submit it for final underwriter approval.

10. Approve Seller Disclosures

• Read and question items you do not understand on the TDS, Seller Property Questionnaire, natural hazard report, pest inspection / completion and other documents such as a preliminary title policy.
• Realize you have 10 days to cancel if lead paint is a health hazard.
• Read every document in its entirety; ask questions about all seller disclosures.

11. Order Homeowner's Insurance Policy

• Order your homeowner's insurance early.
• Sometimes previous claims by a home owner can make it difficult to get insurance.
• Get replacement coverage.

12. Conduct Home Inspection

• Hire a reputable home inspector.
• Get a quote from multiple inspection companies, to better understand the price range.
• Bring a home inspection checklist with you.
• Attend the home inspection.
• Take pictures of your things you find yourself.
• Questions to Ask from Home Inspector:
• What does your inspection cover?
• How long will the inspection take?
• Choose an inspector who carries "Errors and Omissions" coverage, in case an inspector misses a major issue.
• Do you offer to do repair or improvements based on your findings?
• What type of inspection report do you provide and how long will it take to receive the report?
• Will I be able to attend the inspection?
• Does the house meet "Up-to-Code" condition?
• How bad is a issue?
• How soon the problem should be repaired?
• How much will it cost?

13. Issue Request for Repair

• If the home inspection turns up health and safety issues, issue a request for repair by asking the seller to address those issues or give you a credit for them.
• Realize no home is perfect, and the inspector will find faults.
• Normally inspectors also provide an estimated cost of repair.
• Based on how much the repair cost is, you can use it to further negotiate the price down and/or have it all fixed before closing the escrow.
• Be reasonable.

14. Remove Contingencies

• By default, California C.A.R. contracts give you 17 days to remove contingencies.
• Make sure your loan is firm and the appraisal is acceptable before removing your loan contingency.
• If you do not remove contingencies, the seller can issue a request to perform and then cancel the contract, on top of demanding your deposit.

15. Do Final Walk-Through

• Do not pass up doing a final walk-through.
• Inspect the property to make sure it's in the same condition as when you agreed to buy it.
• If you find a serious issue, address it now before you close.

16. Sign Loan / Escrow Documents

• In southern California, you will sign escrow documents shortly after opening escrow.
• In northern California, you will sign escrow documents along with your loan documents near closing.
• Bring a valid picture ID.

17. Deposit Funds

• Bring a certified check payable to escrow.
• Expect escrow to pad the amount, so you will receive a refund after closing.

18. Close Escrow

• Your property deed, seller's reconveyance and deed of trust will record in the public records.
• Title will notify you and your agent when it records.
• After recordation, unless your contract specifies otherwise, the property is yours -- change the locks immediately.

19. After Move-In

• Be sure to change all your door-locks.

20. Live happily ever after! :)

8 Tips on Dashboards

Life Hack...

Persian Poetry

Рафтам ба сари кабри шаханшохи яман.
У даст бароварду ба ман дод кафан.
Гуфто, ки аз ин саховатам айб макун
Охир зи чахон хамин расидаст ба ман...