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

76 Lorraine Avenue
Staten Island, NY 10312

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $1,085,000
Beds: 3
Baths: 4
Status: Active
SqFt: 1700
Agency: Gillani Homes Inc.

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: --
Property Type: Residential
Year Built: 2006
Notes: ONE OF A KIND. Exquisite Modern One Family Home in Prime Location. This exceptional single-family home is located in a highly sought-after neighborhood, offering unmatched convenience. With easy access to buses and trains, and close proximity to shopping, restaurants, schools, and places of worship, this home is perfect for families looking for both accessibility and a vibrant community. Luxurious and Spacious Living Room, 3 Bedrooms, 4 Bathrooms, 1 Car-Garage. Upon entering, you'll be captivated by the beautifully modern living room, featuring an expansive bay window that fills the space with natural light. The open layout flows seamlessly into a state-of-the-art kitchen equipped with top-of-the-line appliances and elegant quartz countertops. sliders lead to a stunning deck with glass railings, perfect for outdoor entertaining. This floor also includes a convenient half bath. Elegant Bedrooms and Bathrooms. A striking circular staircase from the living room leads to the second floor, where you'll find a luxurious master suite and two additional spacious bedrooms, each with ceiling fans for added comfort. The second floor also includes two full baths, providing ample space and convenience for the entire family. Versatile and Accessible Finished Basement offers additional space, featuring a three-quarter bath and a laundry area. It provides direct access to the garage and includes front and side doors, making it easy to access both the backyard and the front of the house. Central AC. Plus a split units system. Sec. System with cameras. Jenerac Generator so you never loose electricity. extra-large, resort-style backyard that is completely fenced. This outdoor oasis features a heated in-ground pool, beautiful pavers throughout, and a gazebo with an entertainment system, creating the perfect space for relaxation and entertainment. Advanced Climate Control and Security. This home offers a perfect blend of modern luxury, comfort and security.
MlsNumber: 483662


Listing Provided By: Gillani Homes Inc., original listing
Name: Gillani Homes Inc.
Phone: (718) 442-4400
Office Name: Gillani Homes Inc.
Office Phone: (718) 442-4400
Agent Name: Dil Gillani
Disclaimer: Copyright © 2024 Brooklyn New York Multiple Listing Service. 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