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

W1490 Northwoods
Foster City, MI 49834
LORI RYMKOS with PREMIER REAL ESTATE, INC., original listing - (906) 786-5030

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $250,000
Beds: 2
Baths: Full: 1
Status: Active Under Contract
SqFt: 1000 Square Feet
Agency: PREMIER REAL ESTATE, INC.
Agency Phone: (906) 786-5030

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 4 acre(s)
Garage: Garage
Property Type: Residential-Single Family Residence
Year Built: 2009
Notes: Escape to this storybook A-frame home tucked away on 4.33 peaceful acres with an incredible 352 feet of frontage on the pristine Sturgeon River. Enjoy the beauty of Up North living with a cozy, character-filled home and a property packed with features! Step inside to find a bright, open layout with vaulted ceilings, warm wood finishes, and a spray-foam insulated crawl space for energy efficiency. Take in the views from your screened-in porch or unwind on the wrap-around deck, perfect for soaking up the river breeze. Outside, the property is a dream: • 40x36 Cleary pole building for all your storage and workshop needs • 18x36 heated man cave inside the garage – ideal for hobbies, game nights, or a private retreat. • Towering trees, wildlife, and room to roam make this a nature lover’s paradise. Whether you’re looking for a year-round home or a weekend getaway, this one-of-a-kind property has it all. Bring your fishing pole, your kayak, and your sense of adventure – the river life awaits! There is laundry hook ups in the pantry. There was a HOA but is no longer existing. The gate is never locked but they shut it during hunting season. All the owners pitch in for road maintenance and snow removal. Offers will be presented Thursday may 22nd at 1 P.M.
MlsNumber: --
ListingId: 50174843


Listing Provided By: PREMIER REAL ESTATE, INC., original listing
Phone: (906) 280-3165
Office Phone: (906) 786-5030
Agent Name: LORI RYMKOS
Disclaimer: Copyright © 2025 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