{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Recapture Request Form","$DB.CONFIG","$DB.LOOKUP"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":45,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Recapture Request Form","index":0,"n_rows":51,"n_cols":15,"rows_kept":51,"cut":false,"hidden":false,"merged":[[6,6,6,14],[3,4,3,14],[36,4,36,14],[51,4,51,6],[51,8,51,12],[47,4,47,14],[8,12,8,14],[8,6,8,10],[16,4,16,14],[33,4,33,14],[10,6,10,14],[49,4,49,14],[31,4,31,14],[22,10,22,14],[24,4,24,14],[40,4,40,14],[19,4,20,14],[12,4,12,14]]}
[]
["Hidden Calculations & Constants",null,null,null,"Affordable Housing Programs (AHP) : Recapture Request Form (AHP-153)",null,null,null,null,null,null,null,null,null,{"$f":"\"Version \"&APP_VERSION","v":"Version 2.1.0"}]
["NO_SELECTION_FORM_ROWS",{"$f":"ROW(D13)&\":\"&ROW(D16)","v":"13:16"}]
["OWNER_TERM_LEN",60,null,null,"Member & Project / Household Details"]
["OWNER_SUBSIDY_PER_MONTH",{"$f":"IF(AND(OWNER_SUBSIDY_AMOUNT<>\"\",OWNER_TERM_LEN<>\"\"),OWNER_SUBSIDY_AMOUNT/OWNER_TERM_LEN,\"\")","v":""}]
["OWNER_MONTHS_REMAINING",{"$f":"IF(AND(OWNER_MATURITY_DATE<>\"\",OWNER_RECAPTURE_DATE<>\"\"),MAX(((YEAR(OWNER_MATURITY_DATE)-YEAR(OWNER_RECAPTURE_DATE))*12)+(MONTH(OWNER_MATURITY_DATE)-MONTH(OWNER_RECAPTURE_DATE)),0),\"\")","v":""},null,null,"Project/Household Type",null,"Member Name"]
["OWNER_PRORATA_AMOUNT",{"$f":"IF(AND(OWNER_MONTHS_REMAINING<>\"\",OWNER_SUBSIDY_PER_MONTH<>\"\"),MIN(OWNER_SUBSIDY_AMOUNT,OWNER_SUBSIDY_PER_MONTH*OWNER_MONTHS_REMAINING),\"\")","v":""}]
["OWNER_SALECOMPONENT_REQUIRED",{"$f":"IF(AND(OWNER_PRORATA_AMOUNT<>\"\",OWNER_PRORATA_AMOUNT<=OWNER_RECAP_NOTREQUIRED_THRESHOLD),FALSE,TRUE)","v":true},null,null,"Project # / FHC # / HDP #",null,"Property Address",null,null,null,null,null,"Project Name (AHP Only)"]
["OWNER_NET_PROCEEEDS",{"$f":"IF(\nAND(SALE_PRICE_NEW_MTG<>\"\",TRANSACTION_COST<>\"\",HOUSEHOLD_INVESTMENT<>\"\",OUTSTANDING_DEBT<>\"\"),\nSALE_PRICE_NEW_MTG-TRANSACTION_COST-HOUSEHOLD_INVESTMENT-OUTSTANDING_DEBT,\n\"\")","v":""}]
["OWNER_RECAPTURE_AMOUNT",{"$f":"IF(NOT(OWNER_SALECOMPONENT_REQUIRED),MAX(0,OWNER_PRORATA_AMOUNT),IF(AND(OWNER_PRORATA_AMOUNT<>\"\",OWNER_NET_PROCEEDS<>\"\"),MAX(0,MIN(OWNER_PRORATA_AMOUNT,OWNER_NET_PROCEEDS)),\"\"))","v":""},null,null,"Reason for Recapture",null,"If other, please explain"]
["OWNER_SECTION_STARTED_FLAG",{"$f":"OR(OWNER_HOUSEHOLD_NAME<>\"\",OWNER_SUBSIDY_AMOUNT<>\"\",OWNER_PROJECT_COMPLETION_DATE<>\"\",OWNER_RECAPTURE_DATE<>\"\",SALE_PRICE_NEW_MTG<>\"\",TRANSACTION_COST<>\"\",HOUSEHOLD_INVESTMENT<>\"\",OUTSTANDING_DEBT<>\"\",OWNER_HOUSEHOLD_INVESTMENT_DOCUMENTS<>\"\")","v":false}]
["OWNER_SECTION_COMPLETED_FLAG",{"$f":"AND(OWNER_HOUSEHOLD_NAME<>\"\",OWNER_SUBSIDY_AMOUNT<>\"\",OWNER_PROJECT_COMPLETION_DATE<>\"\",OWNER_RECAPTURE_DATE<>\"\",OR(NOT(OWNER_SALECOMPONENT_REQUIRED),SALE_PRICE_NEW_MTG<>\"\",TRANSACTION_COST<>\"\",HOUSEHOLD_INVESTMENT<>\"\",OUTSTANDING_DEBT<>\"\",OWNER_HOUSEHOLD_INVESTMENT_DOCUMENTS<>\"\"))","v":false}]
["OWNER_UI_MESSAGE",{"$f":"IF(AND(OWNER_SECTION_STARTED_FLAG,NOT(OWNER_SECTION_COMPLETED_FLAG)),\"Please Fill Out All Fields\",IF(OWNER_RECAPTURE_AMOUNT<=OWNER_RECAP_NOTREQUIRED_THRESHOLD,\"No Recapture Necessary\",\"\"))","v":""},null,null,"Instructions"]
["OWNER_RECAP_NOTREQUIRED_THRESHOLD",2500]
["OWNER_FORM_ROWS",{"$f":"ROW(D17)&\":\"&ROW(D33)","v":"17:33"},null,null,"Please select \"Project/Household Type\" above to display applicable Recapture Request Form. "]
["RENTAL_TERM_LEN",180]
["RENTAL_SUBSIDY_PER_MONTH",{"$f":"IF(AND(RENTAL_SUBSIDY_AMOUNT<>\"\",RENTAL_TERM_LEN<>\"\"),RENTAL_SUBSIDY_AMOUNT/RENTAL_TERM_LEN,\"\")","v":""},null,null,"AHP Owner-Occupied Project / AHP Set-Aside Recapture Request"]
["RENTAL_MONTHS_REMAINING",{"$f":"IF(AND(RENTAL_MATURITY_DATE<>\"\",RENTAL_RECAPTURE_DATE<>\"\"),MAX(((YEAR(RENTAL_MATURITY_DATE)-YEAR(RENTAL_RECAPTURE_DATE))*12)+(MONTH(RENTAL_MATURITY_DATE)-MONTH(RENTAL_RECAPTURE_DATE)),0),\"\")","v":""}]
["RENTAL_RECAPTURE_AMOUNT",{"$f":"IF(AND(RENTAL_MONTHS_REMAINING<>\"\",RENTAL_SUBSIDY_PER_MONTH<>\"\"),MIN(RENTAL_SUBSIDY_AMOUNT,RENTAL_SUBSIDY_PER_MONTH*RENTAL_MONTHS_REMAINING),\"\")","v":""},null,null,"Instructions"]
["RENTAL_SECTION_STARTED_FLAG",{"$f":"OR(RENTAL_SUBSIDY_AMOUNT<>\"\",RENTAL_PROJECT_COMPLETION_DATE<>\"\",RENTAL_RECAPTURE_DATE<>\"\")","v":false},null,null,"This section is only to be used in the event of a recapture for an applicable AHP owner-occupied project or AHP set-aside household as defined in our AHP-100 AHP Implementation Plan, FHC Program Guidelines, or HDP Program Guidelines.  In order to properly complete this section, please complete each of the Yellow highlighted fields.\n                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                Net Proceeds:\n\n• In the case of a sale, transfer, or assignment of title or deed of an AHP-assisted unit by a household during the AHP five-year retention period, the sales price minus reasonable and customary costs paid by the household in connection with the transaction (including real estate broker's commission, attorney's fees, and title search fees) and outstanding debt superior to the AHP subsidy lien or other legally enforceable AHP subsidy repayment obligation. \n\n• In the case of a refinancing of an AHP-assisted unit by a household during the AHP five-year retention period, the principal amount of the new mortgage minus reasonable and customary costs paid by the household in connection with the transaction (including attorney’s fees and title search fees) and the principal amount of the refinanced mortgage.\n\nTransaction Cost:\n\n• Reasonable and customary costs paid by the household in connection with the purchase of the unit including real estate broker’s commission, attorney’s fees, and title search fees (Enter this cost in the Transaction Cost field); \n\nHousehold Investment:\n\n• Any down payment paid in connection with the household’s purchase of the unit (Enter this amount in the Household Investment field); \n\n• The cost of any capital improvements made after the household’s purchase of the unit until the time of the subsequent sale, transfer, assignment of title or deed, or refinancing (Enter this amount in the Household Investment field); and \n\nOutstanding Debt:\n\n• The amount of principal on any mortgage senior to the AHP subsidy lien or other legally enforceable AHP subsidy repayment obligation repaid by the household (Enter this amount in the Outstanding Debt field). \n\nIt is the Member's responsibility to retain all documents used to arrive at Net Proceeds and the final calculation of the recapture.\n\nResponses must be provided to all highlighted fields in this form; this form will not be considered fully complete unless a response is given to every field."]
["RENTAL_SECTION_COMPLETED_FLAG",{"$f":"AND(RENTAL_SUBSIDY_AMOUNT<>\"\",RENTAL_PROJECT_COMPLETION_DATE<>\"\",RENTAL_RECAPTURE_DATE<>\"\")","v":false}]
["RENTAL_UI_MESSAGE",{"$f":"IF(AND(RENTAL_SECTION_STARTED_FLAG,NOT(RENTAL_SECTION_COMPLETED_FLAG)),\"Please Fill Out All Fields\",\"\")","v":""}]
["RENTAL_FORM_ROWS",{"$f":"ROW(D34)&\":\"&ROW(D47)","v":"34:47"},null,null,"Recapture Request Form"]
[]
[null,null,null,null,{"$f":"OWNER_UI_MESSAGE","v":""}]
[]
[null,null,null,null,"Household Name",null,"Amount of Subsidy",null,"Closing Date",null,"Maturity Date",null,"Event of Non Compliance / Payoff Date",null,"Pro Rata Calculation"]
[null,null,null,null,null,null,null,null,null,null,{"$f":"IF(OWNER_PROJECT_COMPLETION_DATE=\"\",\"\",DATE(YEAR(OWNER_PROJECT_COMPLETION_DATE),MONTH(OWNER_PROJECT_COMPLETION_DATE)+OWNER_TERM_LEN,DAY(OWNER_PROJECT_COMPLETION_DATE)-1))","v":""},null,null,null,{"$f":"OWNER_PRORATA_AMOUNT","v":""}]
[null,null,null,null,"Sale Price/New Mortgage",null,"Transaction Cost",null,"Household Investment",null,"Outstanding Debt",null,"Net Proceeds Calculation",null,"Recapture Amount"]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"OWNER_NET_PROCEEDS","v":""},null,{"$f":"OWNER_RECAPTURE_AMOUNT","v":""}]
[null,null,null,null,"Please Describe Household Investment Document(s)"]
[]
[]
[null,null,null,null,"AHP Rental Project Recapture Request"]
[]
[null,null,null,null,"Instructions"]
[null,null,null,"A1","This section is only to be used in the event of a recapture for an AHP Rental Project as defined in our AHP-100 AHP Implementation Plan. Provide an answer to all highlighted fields in this form. This form will not be considered fully complete unless a response is given to every field.  "]
[]
[null,null,null,null,"Recapture Request Form"]
[]
[null,null,null,null,{"$f":"RENTAL_UI_MESSAGE","v":""}]
[]
[null,null,null,null,"Amount of Subsidy",null,"Project Completion Date",null,"AHP Maturity Date",null,"Event of Non Compliance Date",null,"Pro Rata Recapture Amount1",null,"Full Recapture Amount1"]
[null,null,null,null,null,null,null,null,{"$f":"IF(RENTAL_PROJECT_COMPLETION_DATE=\"\",\"\",DATE(YEAR(RENTAL_PROJECT_COMPLETION_DATE),MONTH(RENTAL_PROJECT_COMPLETION_DATE)+RENTAL_TERM_LEN,DAY(RENTAL_PROJECT_COMPLETION_DATE)-1))","v":""},null,null,null,{"$f":"IF(RENTAL_RECAPTURE_AMOUNT<>\"\",RENTAL_RECAPTURE_AMOUNT,\"\")","v":""},null,{"$f":"IF(AND(RENTAL_SUBSIDY_AMOUNT<>\"\",RENTAL_PROJECT_COMPLETION_DATE<>\"\",RENTAL_RECAPTURE_DATE<>\"\"),IF(RENTAL_RECAPTURE_DATE>RENTAL_MATURITY_DATE,0,RENTAL_SUBSIDY_AMOUNT),\"\")","v":""}]
[]
[null,null,null,null,"1 The amount of the recapture (pro rata vs full) will be decided by the FHLBNY, in its sole discretion, based on the nature of the event of non-compliance."]
[]
[null,null,null,null,"Member Signoff"]
[]
[null,null,null,null,"By submitting the Recapture Request Form the Member is authorizing FHLBNY to debit their OIA account."]
[null,null,null,null,"Requested By",null,null,null,"Title",null,null,null,null,null,"Date"]

{"sheet":"$DB.CONFIG","index":1,"n_rows":5,"n_cols":4,"rows_kept":5,"cut":false,"hidden":true,"merged":[]}
["$DB.CONFIG.TBL_CONFIG"]
["CONFIG_VAR","CONFIG_DESC","CONFIG_TYPE","CONFIG_VALUE"]
["LAST_REV_DATE","Last Revision Date","Date","2019-12-10"]
["APP_VERSION","App Version","Text","2.1.0"]
["LAST_REV_DISPLAY","Last Revision Display","Text",{"$f":"CONCATENATE(MONTH(LAST_REV_DATE),\"/\",YEAR(LAST_REV_DATE))","v":"12/2019"}]

{"sheet":"$DB.LOOKUP","index":2,"n_rows":6,"n_cols":3,"rows_kept":6,"cut":false,"hidden":true,"merged":[]}
["$DB.LOOKUP.RANGE_LOOKUP_RECAPTURE_REASON",null,"$DB.LOOKUP.RANGE_LOOKUP_PROJECT_TYPE"]
["RECAPTURE_REASON",null,"PROJECT_TYPE"]
["Foreclosure",null,"Owner-Occupied"]
["Refinance",null,"Rental"]
["Sale of Property"]
["Other"]