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 = 1244 AND i.igate IN ('usahud','pahud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'pahud', 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 = 1244 AND i.igate IN ('usahud','pahud','ushud') AND m.expertType = 'lender' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'pahud', 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 = 1244 AND i.igate IN ('usahud','pahud','ushud') AND m.expertType = 'other' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'pahud', 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;

6863 Crystal Drive
Beulah, MI 49617
Jon Zickert with REO-TCBeulah-Frankfort-233027, original listing - (231) 882-4449

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $2,300,000
Beds: 3
Baths: Full: 2, ½: 1
Status: Active
SqFt: 2932 Square Feet
Agency: REO-TCBeulah-Frankfort-233027
Agency Phone: (231) 882-4449

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 0 acre(s)
Property Type: Residential-Single Family Residence
Year Built: 1960
Notes: Imagine waking up to breathtaking views of Crystal Lake’s signature blue-green waters, where every sunrise feels like a masterpiece. This stunning mid-century modern home is designed to bring the outdoors in, with floor-to-ceiling windows that frame the lake from nearly every room. With 209 feet of private frontage, a sugar-sand beach, and a spacious waterfront yard, there’s plenty of room for relaxation and adventure—whether it's swimming, sunbathing, or simply soaking in the natural beauty. Inside, the home blends timeless design with warmth, featuring Onaway stone, redwood paneling, and pine-beamed ceilings. The kitchen, with its expansive lake views, is a space meant for gathering, while the sunroom—complete with a built-in grill—offers a seamless transition to outdoor living. A generous 24 x 40 three-car garage provides ample storage, and the lakeside boathouse keeps all your waterfront gear within reach. Adding to the charm, a stone-lined stream with a footbridge winds through the property, creating a peaceful retreat. Located just minutes from Sleeping Bear Dunes, Lake Michigan, Crystal Mountain, and the charming downtowns of Beulah and Frankfort, this is more than a home—it’s an invitation to experience the best of Northern Michigan living. Buy now and be on the water this summer!
MlsNumber: --
ListingId: 1931244


Listing Provided By: REO-TCBeulah-Frankfort-233027, original listing
Phone: (231) 882-6996
Office Phone: (231) 882-4449
Agent Name: Jon Zickert
Disclaimer: Copyright © 2025 Northern Great Lakes Realtors MLS. 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