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

327 Arlington Street
Birmingham, MI 48009
Katherine E Broock with Max Broock, REALTORS®-Birmingham, original listing - (248) 644-6700

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $4,950,000
Beds: 5
Baths: Full: 5, ½: 2
Status: Active
SqFt: 5531 Square Feet
Agency: Max Broock, REALTORS®-Birmingham
Agency Phone: (248) 644-6700

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 0 acre(s)
Garage: Garage
Property Type: Residential-Single Family Residence
Year Built: 2023
Notes: Introducing an exquisite new construction home by renowned builder Great Lakes Custom Builder, where luxury meets warmth in every detail. This stunning masterpiece showcases the highest quality finishes, featuring extensive stonework, elegant glass accents, and custom lighting throughout. As you approach the property, the impressive exterior-crafted from stone and stucco- creates a striking first impression. Inside, the main floor welcomes you with a custom office space complete with built-in cabinetry and a beautiful gas fireplace. The great room features a floor-to-ceiling stone fireplace, connecting to the state-of-the-art kitchen, which is both warm and inviting. This sophisticated space features an oversized island with a gorgeous stone countertop, custom cabinetry, and high-end built-in appliances. A butler's pantry enhances functionality with a beverage refrigerator, dishwasher, a third sink, and abundant counter and cabinet space. The second floor showcases a sumptuous primary suite with its own gas fireplace, a luxurious bathroom, and a spacious walk-in closet enhanced by custom lighting and ample storage. The primary bathroom boasts a large shower with dual shower heads, a separate soaking tub, and dedicated vanity area. The generously sized laundry room, equipped with extensive storage and folding space, adds convenience to daily living. Four additional bedrooms, each with walk-in closets and ensuite bathrooms, complete this level. The lower level is a perfect entertainment space, featuring a spacious great room with another gas fireplace, a well-appointed kitchen/bar area, a large wine storage room, an additional bedroom, and a full bathroom, along with a versatile bonus room. This exceptional home is equipped with an elevator servicing all three floors. This home has been constructed with the highest quality materials in a well-thought-out space, ensuring a lasting impression. We invite you to come and see it for yourself.
MlsNumber: --
ListingId: 20240082013


Listing Provided By: Max Broock, REALTORS®-Birmingham, original listing
Phone: (248) 318-4504
Office Phone: (248) 644-6700
Agent Name: Katherine E Broock
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