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;

968 Arlington Street
Birmingham, MI 48009
Pamela Stoler with The Agency Hall & Hunter, original listing - (248) 229-6100

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $3,999,000
Beds: 5
Baths: Full: 5, ½: 3
Status: Active
SqFt: 7029 Square Feet
Agency: The Agency Hall & Hunter
Agency Phone: (248) 229-6100

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 0 acre(s)
Garage: Garage
Property Type: Residential-Single Family Residence
Year Built: 2006
Notes: Magnificent residence boasting over 10,000+ square feet with 6 Bedrooms and 6.3 Baths, this extraordinary home combines old-world charm with every modern amenity imaginable. Recently renovated in 2024, it features oversized crown and baseboard moldings, heated floors, an elevator, and custom doors throughout. Chestnut wood floors, copper gutters, and a cedar shake roof add to its unique character. The rear of the home has oversized windows, flooding the space with natural light. The open floor plan includes a fabulous all-season Solarium, a spectacular kitchen with an island, and a 2-story Library. The Master Suite features a fireplace and an expansive luxurious bath, plus there are two Laundry Rooms. The finished lower level offers an open Theatre, Gym, wine cellar, and a pool room. Exterior highlights include manicured gardens front and rear, a circular driveway, Porte-cochere, and a backyard retreat. The property also includes a 3-car attached garage and a 2-car detached carriage house with a studio apartment above. Recent upgrades and replacements encompass the boiler, basement flooring, workout room/gym flooring, water filtration system, FloLogic system, whole-house Lutron Shade Lights with smart electronics, new kitchen appliances, outside kitchen/patio, upgraded Rainbird controls, fenced/gate, ice melt system for the garage and sunroom, upstairs carpet, Ring camera, MyQ gate remote controls, steam shower, wine cellar, main floor heated floor, elevator, 5-car garage (3 attached and 2 detached as part of the carriage house) , and a carriage house complete with a bathroom, kitchenette, and bedroom.
MlsNumber: --
ListingId: 20240044374


Listing Provided By: The Agency Hall & Hunter, original listing
Phone: (248) 229-6100
Office Phone: (248) 229-6100
Agent Name: Pamela Stoler
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