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','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 = 1244 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 = 1244 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;

3719 Orchard Lane
Beulah, MI 49617
Kari King with Century 21 Northland-Frankfort, original listing - (231) 352-7123

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $675,000
Beds: 4
Baths: Full: 2
Status: Active
SqFt: 2386 Square Feet
Agency: Century 21 Northland-Frankfort
Agency Phone: (231) 352-7123

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 1 acre(s)
Property Type: Residential-Single Family Residence
Year Built: 1989
Notes: Welcome to this stunning and well maintained 4-bedroom, 2-bath contemporary home perfectly situated on just over an acre with 200 ft of shared access to the pristine waters of Crystal Lake and peaceful Long Lake. Inside you will enjoy the wood accented cathedral ceilings, this home offers a spacious and inviting living area highlighted by a cozy wood stove and outdoor views from the main floor. Open concept living space with the living room flowing nicely to the kitchen and dining room. Expand your entertaining space outdoors onto the back deck, complete with a hot tub and space to spread out while you take in the breathtaking countryside views. A bedroom and full guest bathroom are conveniently located on the main floor. Upstairs you will find the large family room featuring charming wood accent ceilings, and could be used as an office/den space. An open air loft leads you to the master bedroom with walk in closet, guest room and full bath. On the lower level a full bedroom has been added along with ample storage space and washer and dryer. The property includes an attached one-car garage and an additional detached two-car garage, ideal for storing all your seasonal toys and equipment. This home has been beautifully landscaped and meticulously maintained, providing a private and peaceful setting. A premier location within the Crystal Highlands Association which includes shared access to two Crystal Lake swim beaches where you will enjoy the sandy beach including two docks, a deck, volleyball court, swim raft, a shed that houses various water toys and sports equipment and a community pavilion. For a quieter lake experience head over to the 300' access to Long Lake, a perfect lake for sinking into the sounds of nature, fishing and enjoying many forms of wildlife that surround the area. All of this just a short distance to downtown Frankfort and Beulah providing quaint shops, restaurants and access to Lake Michigan making this a perfect place to call home.
MlsNumber: --
ListingId: 1926557


Listing Provided By: Century 21 Northland-Frankfort, original listing
Phone: (231) 651-0923
Office Phone: (231) 352-7123
Agent Name: Kari King
Disclaimer: Copyright © 2024 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