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 = 3465 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 = 3465 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 = 3465 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;

21575 22 Mile
Macomb Twp, MI 48044

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $1,750,000
Beds: 6
Baths: 7
Status: Pending
SqFt: 6640
Agency: CJV LLC

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 3 acre(s)
Property Type: Residential
Year Built: 2014
Notes: OPEN SUNDAY 3/3/24 12:00 - 3:00 Luxurious and sprawling 6600 sq ft ranch built with unmatched quality and craftmanship. Every amenity imaginable has been included. Magnificent 8 ft mahogany entry doors leading to massive foyer and living room with 22 ft arched ceiling and floor to ceiling windows overlooking 3+ acres and inground gunite pool. Light, bright and airy open concept kitchen overlooking large gas stone fireplace with 2" limestone hearth, custom built-ins and 12 ft, trayed 3 tiered ceiling. Kitchen with custom cabinetry, granite counters, under counter lighting. Custom paneled refrigerator and freezer. Walk-in pantry. New commercial gas cooktop. Double ovens. Pot filler. 5 ft. wide hallways leading to large spacious ensuite bedrooms. Master bedroom with 12 ft. trayed ceiling with attached sitting room overlooking picturesque property. Master bath with large steam shower, deluxe jacuzzi tub and heated floors. Quality California closets throughout. Whole house freshly painted. New hardwood flooring. New blinds throughout. All new light fixtures. Two large laundry rooms. Dog washing room with entrance to outside. Extensive baseboard and crown molding throughout both levels of home. 42" inch wide stairway leading to completely finished lower level. Freshly painted and new carpeting adding an additional 6500 sq ft of living space with an additional 2 bedrooms, full bath and kitchen area. 9'7" ceiling height. Theatre room wired. Large storage area A/V equipment room. 8" solid doors. 3 furnaces and 3 A/C units adding 7 zones of heating and cooling with thermostats. Generac brand water cooled whole house generator. Large covered porch in back of house wraps around from family room to master suite. 20x42 inground gunite pool with new heater and cover. Stamped concrete pool deck. Freshly landscaped. New asphalt driveway . Perimeter soffit lights around entire house on timer. Motion flood lights. Security cameras covering entire perimeter of house. Large circular driveway which leads to east and west garages.
MlsNumber: 50133782


Listing Provided By: CJV LLC, original listing
Name: CJV LLC
Phone: (586) 792-8000
Office Name: RE/MAX First
Office Phone: (586) 799-8000
Agent Name: Lucy Fowlds
Disclaimer: Copyright © 2024 Multiple Listing Service MiRealSource. 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