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

4385 S Us Highway 23
Greenbush, MI 48738

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $1,500,000
Beds: --
Baths: 12
Status: Active
SqFt: 11000
Agency: KW Advantage

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 1 acre(s)
Property Type: Commercial
Year Built: 1960
Notes: Live and work on the shores of majestic Lake Huron in this 1.25 acre property offering a unique blend of luxurious living and income potential, making it the perfect live-in and investment property. Pristine lakeside land includes a meticulously updated main house and 8 charming cabins, all with breathtaking lake views. The main house boasts 3 beds and 2 full baths, a masterpiece of modern upgrades and functionality. Spacious open-concept living area flooded with natural light. Gourmet kitchen with top-of-the-line appliances, granite countertops, and a large island. Wall of windows offering panoramic lake views. Luxurious master suite with a spa-like ensuite bath. Also includes an office space with private exterior entry. The eight cabins are a perfect blend of rustic charm and modern comfort. Successful rental history providing a steady stream of income for the current owner. Whether you continue the rental business or use the cabins as guest accommodations for friends and family, this property offers exceptional income potential. Imagine waking up to the sound of the waves and the smell of fresh lake air, all while earning rental income! The property features well-maintained grounds, providing a serene environment for residents and guests alike. Enjoy long walks on the private beach, picnics on the beach, and water sports right from your doorstep. Lake Huron is renowned for its crystal-clear waters, and this property gives you a front-row seat to experience it all. Most cabins will come fully furnished - start collecting rental income right away! View drone video and 3D tour.
MlsNumber: 23146299


Listing Provided By: KW Advantage, original listing
Name: KW Advantage
Phone: (248) 380-8800
Office Name: KW Advantage
Office Phone: (248) 380-8800
Agent Name: Danielle Snell
Disclaimer: Copyright © 2024 Michigan Regional Information Center, LLC. 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