Mortgage and Equity Calculator

"Back-of-the-Envelope" Mortgage and Equity Calculator On the "Mortgage" spreadsheet, enter data in the "yellow" cells You will need to enter the following information: 1 2 3 4 5 6 7 8 Number of units by unit-type Rents by unit-types All Expenses for all units per year Vacancy Rate. Leave at 7% if you have not other vacancy data. Amount of Replacement Reserve per unit, per year (PUPY) Debt Service Coverage required by lender Interest on permanent loan Number of years in Loan term Given these assumptions, the amount of the permanent mortgage that can be supported will be shown at bottom of sheet. On the Equity Worksheet, be sure to choose from all "drop down" lists: E-3 Elevator or Non-Elevator F-38 State: Wisconsin, Michigan or Indiana G-3 Acquisition/Rehab or New Construction F-38 Qualified Census Tract (QCT) or Non-QCT F-43,H43 This month's tax credit rates. If you don't know, go to Low-Income Housing Tax Credit Results of the Debt and Equity Calculation are summarized on "Total sources & Uses" worksheet Fill in "soft debt," grants, and other equity to see if any "Gap" remains Final "Gap" may not be more than 70% of the Total Development Fee. If you want to manipulate any of the other cells, you must "unprotect" the worksheet on the "Tools" menu As with all of your tax-credit needs, the password is "CapFund" (note: Case Sensative) If you have problems with this spreadsheet or you have suggestions for improvements, contact: Edward Bobinchak ebobinchak@capfund.net Great Lakes Capital Fund (313) 841-3751 Income Limits and Maximum Gross Rents Check your Rents & AMI by going to this site: ------------------> HUD Statistical Area (MSA, PSA, County): Median Income For Location (Family of Four): http://www.novoco.com/products/rentincome.php Wayne County/Detroit Metro Area $66,900 For Year: Percent of Area Median Income Family Income Limits at: 30% 40% 50% Family Size Adjustment Household Size Factor One Two Three Four Five Six Seven Eight 0.70 0.80 0.90 1.00 1.08 1.16 1.24 1.32 Maximum Income $14,049 $16,056 $18,063 $20,070 $21,676 $23,281 $24,887 $26,492 Maximum Income $18,732 $21,408 $24,084 $26,760 $28,901 $31,042 $33,182 $35,323 Maximum Income $23,415 $26,760 $30,105 $33,450 $36,126 $38,802 $41,478 $44,154 Percent of Area Median Income Maximum Gross Unit Rents at: Unit Size (Number of Bedrooms) Assumed Household Size Unit Size Adjustment Factor 30% Maximum Gross Rent 40% Maximum Gross Rent 50% Maximum Gross Rent 0 1 2 3 4 5 6 1.0 1.5 3.0 4.5 6.0 7.5 9.0 0.70 0.75 0.90 1.04 1.16 1.28 1.40 $351.00 $376.00 $452.00 $522.00 $582.00 $642.00 $702.00 $468.00 $502.00 $602.00 $696.00 $776.00 $856.00 $937.00 $585.00 $627.00 $753.00 $870.00 $970.00 $1,070.00 $1,171.00 Note: These calculations are rounded to the nearest dollar. Your Tax Credit Allocating Agency may use a different rounding methodolo so confirm the specific unit rents and household income limits for your jusrisdiction. Using Financial Calculator for amount of Amortized Debt Step 1 Calculate the rental Income Rent/Mo $ $ $ $ $ $ $ $ $ $ $ $ $ TOTAL # 0 Total Potential Rent Minus Vacancy Net Rental Income Total / Project Total Project /yr - # Bedrooms Number of Units $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 7% Step 2: Calculate total Expenses per year Per Unit Total Operating Expenses $ 3,200 Replacement Reserves PUPY $ 300 Subtotal (to deduct from Rental Income) Total Cashflow available for Debt Service Per Year $ $ $ $ Directions for Capital Fund Calculator (Make sure "Mode" is set to "Fin") <--Divide by Debt Service Coverage Step 3: Calculate Debt Service Coverage Enter the required Debt Service Coverage 1.15 Calculate Mortgage a b c d e f (HP 12-C directions) $ - <--Divide by 12 for Monthly Payment Step 4: Divide DSC by 12 (monthly payment) Change Case CHS Enter as Payment PMT Enter Interest Rate 7.50% g- INT Enter # of Years 30 g-AMORT Solve for Present Value PV $ $ $ 0.63% 360 $0.00 $0.00 <-- Push "PMT" Interest Rate as whole Number. Push "INT" Enter # monthly payments Push "nPMT" Push "SOLVE" Then Push "PV" Difference < $1,000 due to rounding error Maximum amount of Mortgage supported by cashflow: Calculating the amount of Tax Credit Equity . . . NOTE: ONLY ENTER DATA IN YELLOW Step 1 Select the appropriate categories: Non-Elevator For Construction Tax Credit Select State For Acquisition Tax Credit Acquisition/Rehab Step 2 Calculating Basis for Tax Credits Total ALL costs except Developer Fee & Consultant Calculation of Developer/Consultant Fee Total Development Cost (TDC) Subtract the estimated cost of the LAND Subtract the ACQUISITION cost of the BUILDING Subtract "off site" development costs Subtract site costs not related to building Subtract Permanent Loan Fees Subtract Permanent Legal Fees Subtract any Reserves Subtract Syndication Fees Subtract Marketing Expenses Subtract Relocation (if not within project) Subtract any GRANTS being used for the Development TOTAL BASIS FOR TAX CREDITS $ #DIV/0! #DIV/0! $ - #DIV/0! "basis" <--Construction/Acq.---> $ - Step 3 Adjust for Maximum Tax Credits by State: $ #DIV/0! #DIV/0! Step 4 Applicable Fraction Total Number of units in this development How many units are Market Rate Divide # market rate by # total Multiply TOTAL BASIS by percentage 0 0 Deduct Marke-rate units 1.00 This is the "Applicable Fraction" #DIV/0! "adjusted basis" $ 0 0 1.00 - Step 5 Calculating Basis Boost (if applicable) Select whether or not property is in Qualified Census Tract or Difficult to Develop area Non-QCT Total Qualified Basis #DIV/0! No boost for Acqusition $ - Step 6 Multiply by value of the Tax Credit Insert this month's value for the 4% and 9% credit: Multiply Adjusted Basis by tax credit value Total Credit Generated in this project Equity Value of the credit Multiply Total Tax Credit by 10 years Multiply 10-year Credit amount by pay-in amount (pay-in price for each tax credit dollar) Total Equity Generated in this project 8.19% #DIV/0! #DIV/0! $ 3.51% - Step 7 #DIV/0! $ #DIV/0! 0.92 Sources & Uses drawn from Mortgage & Tax Credit worksheets. Enter information only in yellow boxes. USES: Total Development Costs #DIV/0! SOURCES: Total Mortgage Proceeds Total Tax Credit Equity Sub-Total Debt & Equity #DIV/0! OTHER SOURCES: Deferred-payment Loans Grants 0 0 Other Equity (Deferred Dev. Fee) Sub-Total Other Sources: TOTAL SURPLUS/(GAP) #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! $0 $0