Title Page Background
HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 3458 AND i.igate IN ('usahud','mihud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'mihud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1; HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 3458 AND i.igate IN ('usahud','mihud','ushud') AND m.expertType = 'lender' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'mihud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1; HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 3458 AND i.igate IN ('usahud','mihud','ushud') AND m.expertType = 'other' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'mihud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1;

4743 Orchard Avenue
Dearborn, MI 48126

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $649,900
Beds: 4
Baths: 4
Status: Active
SqFt: 2200
Agency: Z&F INVESTMENTS, INC DBA REALTY EXECUTIVES CLASSIC

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: --
Property Type: Residential
Year Built: 2023
Notes: LOCATION!! LOCATION!! LOCATION!! IN THE HEART OF DEARBORN, NEW CONSTRUCTION, COMPLETED IN 2024, BRICK COLONIAL (MAX SIZE BRICKS 10"), 4 BEDROOMS, 4 FULL BATHROOMS (CUSTOM VANITIES W/ QUARTZ TOP), 2 CAR DETACHED GARAGE. OPEN CONCEPT HUGE KITCHEN WITH CUSTOM CABINETS, QUARTZ COUNTERTOPS WITH WATERFALL ISLAND, AND MARBLE BACKSPLASH. INCLUDES ZLINE RANGE HOOD. MASTER BEDROOM INCLUDES CUSTOM WALKIN CLOSET AND FULL BATHROOM. CUSTOM CARPENTRY: CROWN MOLDING/ WAINSCOTING THROUGHTOUT THE HOUSE. SOLID CORE DOORS THROUGHTOUT ENTIRE HOUSE. MAIN FLOOR IN THE HOUSE IS COVERED WITH LIFEPROOF 22 MM VINYLS. CARPET THROUGHTOUT THE SECOND FLOOR. FULLY FINISHED GARAGE INSULATED (OVERHOOD DOOR/WALLS), COVERED WITH DRYWALL AND RECESS LIGHTS WITH ELECTRICAL SUB PANEL, GARAGE INCLUDES EV CHARGER PLUG (FOR ELECTRIC CARS), GARAGE IS FULLY BRICKED. COMPLETLY FINISHED BASEMENT INCLUDES: FULL BATHROOM WITH STAND UP SHOWER, HUGE ADDITIONAL ROOM CAN BE USED AS 5TH BEDROOM, FITNESS ROOM, OR OFFICE. CUSTOM LAUNDRY ROOM WITH CABINETS AND QUARTZ COUNTERTOP. HOUSE INCLUDES 2 HIGH EFFICENCY CARRIER BRAND FURNACES AND 2 CARRIER BRAND AC’s, INSTANT TANKLESS HOT WATER HEATER. ALL DUCT WORK IS INSULATED SOLID PIPING. ANDERSON 100 SERIES WINDOWS THROUGHOUT THE HOUSE. BASEMENT IS WATERPROOFED AND HAS SUMP PUMP, AND EGRESS WINDOW.<br /> <br /> All data and measurements are approximate. Buyer agent to verify all information.
MlsNumber: 20240015146


Listing Provided By: Z&F INVESTMENTS, INC DBA REALTY EXECUTIVES CLASSIC, original listing
Name: Z&F INVESTMENTS, INC DBA REALTY EXECUTIVES CLASSIC
Phone: (313) 581-5002
Office Name: Realty Executives Classic
Office Phone: (313) 581-5002
Agent Name: Atiq A Alyaishi
Disclaimer: Copyright © 2024 Realcomp Limited II. All rights reserved. All information provided by the listing agent/broker is deemed reliable but is not guaranteed and should be independently verified.


USHUD.com on the Go!

Foreclosure Mobile App
Ushud Foreclosure iPhone App
Ushud Foreclosure Android App