GLCF 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:

Number of units by unit-type

Rents by unit-types

All Expenses for all units per year

Vacancy Rate. Leave at 8% if you have no 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 for amortization and 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

Click on Tools/protection/unprotect sheet

To directly request investment in a LIHTC property, please use the on-line application

GLCF Online Equity Tool on this website.

If you have problems with this spreadsheet or you have suggestions for improvements, contact:

Great Lakes Capital Fund

(313) 841-3751 x1319

Back


Instructions > Rent Calculator > Mortgage > Tax Credits > Total Sources & Uses

Income Limits and Maximum Gross Rents

DATE:

Check your Rents & AMI by going to this site: ------------------>

http://www.novoco.com/products/rentincome.php

HUD Statistical Area (MSA, PSA, County):

Wayne County/Detroit Metro Area

Median Income For Location (Family of Four):

For Year:

2009

Percent of Area Median Income

Family Income Limits at:

Household Size

Family Size Adjustment Factor

Maximum Income

One

Two

Three

Four

Five

Six

Seven

Eight

Percent of Area Median Income

Maximum Gross Unit Rents at:

Unit Size (Number of Bedrooms)

Assumed Household Size

Unit Size Adjustment Factor

Maximum Gross Rent

1.0

Note: These calculations are rounded to the nearest dollar. Your Tax Credit Allocating Agency may use a different rounding methodology,

so confirm the specific unit rents and household income limits for your jusrisdiction.

Back


Instructions < Rent Calculator > Mortgage > Tax Credits > Total Sources & Uses

Using Financial Calculator for amount of Amortized Debt

Step 1

Calculate the rental Income

This section counts # units by bedroom type

Do not change: changes automatically

# Bedrooms

Number of Units

Rent/Mo

Total / Project

Total Project /yr

Efficiency

1bedroom

2bedroom

3bedroom

4bedroom

TOTAL #

Total Potential Rent

Minus Vacancy

Net Rental Income

Step 2:

Calculate total Expenses per year

Per Unit

Per Year

Total Operating Exp

Replacement Reserv

Subtotal (to deduct f

Total Cashflow avail

Step 3:

Calculate Debt Service Coverage

Enter the required Debt Service Coverage

Step 4:

Calculate Mortgage

(HP 12-C directions)

Divide DSC by 12 (monthly payment)

Change Case

CHS

Enter as Payment

PMT

Enter Interest Rate

g- INT

Enter # of Years

g-AMORT

Solve for Present Value

Maximum amount of Mortgage supported by cashflow:

Back


Instructions < Rent Calculator < Mortgage > Tax Credits > Total Sources & Uses

Calculating the amount of Tax Credit Equity

Calculating the amount of Tax Credit Equity . . .

For Construction

For Acquisition

NOTE: ONLY ENTER DATA IN YELLOW

Tax Credit

Step 1

Select the appropriate categories:

Step 2

Calculating Basis for Tax Credits

Calculation of Developer/Consultant Fee

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)

Do not change: Calculates basis against HUD Limits

Subtract any GRANTS being used for the Development

15% of Dev. Cost

"basis"

combined

TOTAL BASIS FOR TAX CREDITS

<--Construction/Acq.--->

Acquistion

Step 3

Adjust for Maximum Tax Credits by State:

Rehab

Step 4

Applicable Fraction

Total Number of units in this development

How many units are Market Rate

Deduct Marke-rate units

Divide # market rate by # total

This is the "Applicable Fraction"

Multiply TOTAL BASIS by percentage

"adjusted basis"

Step 5

Calculating Basis Boost (if applicable)

Select whether or not property is in Qualified Census Tract or Diffic

No boost for Acqusition

Total Qualified Basis

Multiply by value of the Tax Credit

Step 6

Insert this month's value for the 4% and 9% credit:

Multiply Adjusted Basis by tax credit valu

Total Credit Generated in this project

Equity Value of the credit

Step 7

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

Note: Michigan limits the amount of Eligible Bais to 110% of the published HUD Limits. Look for Limits in "Tab I"

HUD 221(

Non Elevator

# of Units

Elevator Buildings

Efficiency

1 BR

2 BR

3 BR

4 BR

TOTAL FOR ALL UNITS

Tax credit Basis cannot be more than this per unit.

The values in green are for Wayne County. Change to match the county where project is located.

Total number of units for each bedroom type feeds from Mortgage worksheet.

Updated September 2006

Note: Each State sets limits on how much can be earned as a Developer Fee, Developer Overhead or Development Consultant:

DEV.FEE

TRUE if small

TRUE if 9%

Multiplyer

NC/Rehab

Max Dev. Fee

QAP

Michigan

=all 9% deals

Actual Dev. Fee

Michigan

=4% deals less than 50 units

Michigan

15%

2,000,000

=4% deals over 49 units

Indiana

=9% New Construction under 3

Indiana

=9% New Construction over 35

Indiana

=9% Rehab under 36 units

Indiana

=9% Rehab Over 35 units

Indiana

=4% New Construction under 3

Indiana

=4% New Construction Over 35

Indiana

=4% Rehab under 36 units

Indiana

=4% Rehab Over 35 units

Wisconsin

Allows up to 15% of the total development costs minus the Developer Fee.

Maximum Credits available in Indiana

Max Developer Fee in Wisconsi

# of Units

non-QCT

QCT

Less than 25

More than 24

Max Credits

$ 9,015

$ 11,719

New Construction Developer Fe

$ 8,453

$ 10,988

Selects Max Credit amount f

$ 7,891

$ 10,258

Acquisition: Rehab less than 20

$ 7,323

$ 9,520

Acquisition: Rehab is 20% & 39

Acquisition: rehab is 40% & 59

Maximum Credits available in Michig

Acquisition: Rehab is 60% & 79% of Acquisition

Acquisition: Rehab if over 80% of Acquisition

Rehab Credit to be added to ac

Developer Fee: Acquisition Reh

Back


Instructions < Rent Calculator < Mortgage < Tax Credits > Total Sources & Uses

Sources & Uses drawn from Mortgage & Tax Credit worksheets.

Enter information only in yellow boxes.

USES:

Total Development Costs

15% of TDC

SOURCES:

Total Mortgage Proceeds

Total Tax Credit Equity

Sub-Total Debt & Equity

OTHER SOURCES:

Deferred-payment Loans

Grants

Other Equity (Deferred Dev. Fee)

Sub-Total Other Sources:

TOTAL SURPLUS/(GAP)

Back


Instructions < Rent Calculator < Mortgage < Tax Credits < Total Sources & Uses

Please fill all required fields