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

N7581 S-4
Stephenson, MI 49887
CHARITY MELSHA with MULTI STATE REALTY, original listing - (906) 828-2525

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $1,640,000
Beds: 8
Baths: Full: 7, ½: 1
Status: Active
SqFt: 6041 Square Feet
Agency: MULTI STATE REALTY
Agency Phone: (906) 828-2525

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 30 acre(s)
Garage: Garage
Property Type: Residential-Single Family Residence
Year Built: 2005
Notes: Luxury with Warmth, who said you can't have it all! Welcome to your personal sanctuary. This home sits on the Menominee River with 1/4 mile of picturesque riverfront, offering some of the best fishing in the Michigan UP plus a private swimming pond that is great for enjoying warmer days or ice skating when the when the snow flakes are dancing in the air. As you cross the Koss Bridge you can feel the serenity you so desperately seek start to settle in. Entering your gated driveway you'll be met with stunning views of your lush property and majestic home which is perfectly positioned between the Menomonee River and your own private swimming pond where peaceful deer are regularly seen sipping from the clean aerate waters. Do you want room to grow and entertain? This 6500+ square foot luxury log home offers 8 bedrooms and 7.5 baths which have been meticulously maintained by professionals that know the meaning of service, the kind of personal care and service which gives you the freedom to enjoy your home and property with a sense of peace and awe that you deserve. Whole house back up generator and Starlink internet keeps you connected while two boilers, one for in-floor heat and one for domestic water keeping you warm and comfortable. Enjoying the picturesque views of the Menomonee River from your jetted bubble bath, swinging carelessly in a hammock, or sitting next to a warm fire in the Great Room, all will inspire you to dream again and reflect on how incredibly blessed you are to have a home that feels like a piece of heaven on earth. Or maybe you just relax and take a nap...your choice. You'll have all the space you want and everything you need to escape, focus, or both. This home is luxurious throughout, with TVs and waterfront views in every bedroom, a pool table, foosball table, shuffleboard table, ping pong table, and gourmet kitchen there is nothing lacking here. And just incase you wonder... some of the biggest critics of housing (namely The REALTORS Association) have requested to use this property for their retreats for years. The current VRBO profits exceed 80k per year with P&L's available to serious buyers.
MlsNumber: --
ListingId: 50156705


Listing Provided By: MULTI STATE REALTY, original listing
Phone: (906) 396-6570
Office Phone: (906) 828-2525
Agent Name: CHARITY MELSHA
Disclaimer: Copyright © 2024 Upper Peninsula Association of Realtors. 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