Title Page Background
DEBUG FN args: Array ( [0] => 1825 [1] => [2] => [3] => agent ) BEFORE: Site: 1825 LocID: Region: State: agent Experttype: Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 SITE IS NUMERIC, SWAPPING IFELSE numeric IFELSE else AFTER: Site: 1825 Igate: njhud LocID: 1825 Region: State: Experttype: agent Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 QUERY: 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 = 1825 AND i.igate IN ('nyhud','ushud') AND m.expertType = 'agent' ORDER BY i.igate_index DESC, IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC, IF(i.igate = 'nyhud', 0, 1), IF(i.igate = 'ushud', 0, 1) LIMIT 0, 1; DEBUG FN args: Array ( [0] => 1825 [1] => [2] => [3] => lender ) BEFORE: Site: 1825 LocID: Region: State: lender Experttype: Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 SITE IS NUMERIC, SWAPPING IFELSE numeric IFELSE else AFTER: Site: 1825 Igate: njhud LocID: 1825 Region: State: Experttype: lender Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 QUERY: 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 = 1825 AND i.igate IN ('nyhud','ushud') AND m.expertType = 'lender' ORDER BY i.igate_index DESC, IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC, IF(i.igate = 'nyhud', 0, 1), IF(i.igate = 'ushud', 0, 1) LIMIT 0, 1; DEBUG FN args: Array ( [0] => 1825 [1] => [2] => [3] => other ) BEFORE: Site: 1825 LocID: Region: State: other Experttype: Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 SITE IS NUMERIC, SWAPPING IFELSE numeric IFELSE else AFTER: Site: 1825 Igate: njhud LocID: 1825 Region: State: Experttype: other Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 QUERY: 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 = 1825 AND i.igate IN ('nyhud','ushud') AND m.expertType = 'other' ORDER BY i.igate_index DESC, IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC, IF(i.igate = 'nyhud', 0, 1), IF(i.igate = 'ushud', 0, 1) LIMIT 0, 1;

5621 Palisade Avenue
Bronx, NY 10471
Arlene Trebach with TREBACH REALTY INC, original listing - (718) 543-7174

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $5,200,000
Beds: 5
Baths: Full: 2, ½: 1
Status: Active
SqFt: --
Agency: TREBACH REALTY INC
Agency Phone: (718) 543-7174

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 1 acre(s)
Garage: Garage
Property Type: Residential-Single Family Residence
Year Built: 1984
Notes: Spectacular Hudson River Views: Spacious Contemporary Home on 1.2 Acres with Decks, Lawns & Stream on Private Cul-de-Sac Set on a huge and rare 1.2 acres of land with picture-perfect Hudson River views, this spacious 3- to 5-bedroom, 2.5-bath, contemporary-style home was built at the end of a private cul-de-sac in 1984. Its extensive grounds enjoy upper and lower decks leading to broad, gracefully rolling lawns, complete with a sparkling stream and a charming footbridge. The house is characterized by large, airy, light-filled rooms, most offering direct views of the river and the great cliffs of the Palisades on the opposite shore. All of the main rooms have doors leading out to the decks. Standout details include: a landscaped and generous front yard; a wide and welcoming entry deck; an inviting center hall with cathedral ceiling and open dining area; a handsome living room with a soaring ceiling and a wood-burning fireplace set into a stone wall; a center-island kitchen with stainless-steel appliances; a front music room; a library with custom built-in bookcases; and a large office. The music room and office are easily convertible to 4th and 5th bedrooms. On the main floor, the bedroom wing has 3 bedrooms and 2 full bathrooms. These include a master bedroom with a river view, fireplace and 2 walk-in closets. The master bathroom features a double-sink vanity, a glass-enclosed shower and a whirlpool tub. The main floor also includes a windowed laundry room. There are two lower-level wings - one with a family room and the other with a sizeable fitness room (20 by 34 ft.) enjoying a full-on view of the river. The lower level also includes a 17-by 24-ft. storage room and a walk-in cedar closet. Central HVAC. Long private driveway and attached 2-car garage. The grounds include flower/vegetable beds, plus an adorable separate playhouse for children. Very conveniently located for Wave Hill public garden and culture center, SAR Academy and the Metro-North trains to Manhattan.
MlsNumber: --
ListingId: 11286728


Listing Provided By: TREBACH REALTY INC, original listing
Phone: (718) 543-7174
Office Phone: (718) 543-7174
Agent Name: Arlene Trebach
Disclaimer: Copyright © 2024 My State 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