Cohousing-L Mailing List: Financial data base description

[David G Adams (dadams]

Hi again,

A bunch of people requested the description of the calculations, and no one 
requested my actual software, so I am posting the descriptions to the whole 
list.  I will still fax reports to those who asked...

Hope this helps!

--Dave

-----------------------------
These tables are used for maintaining financial information about households 
in a potential cohousing group.  They do not maintain dues payments, capital 
payments, or any of the bookkeeping that coho groups need to do.  Nor do 
they contain all the info you would want in a membership / outreach 
database.  They are used to look at the group's ability to afford various 
plans, and to raise confidence in the households' abilities to get 
mortgages.

There are 3 tables: fees, person, and member (household)

FEES:
Style_ID        The time of home that has these prices
Taxes           The expected real estate tax on a unit type
Insurance       Expected share of common insurance costs
Fees            Anticipated condo fees
Low_price       minimum expected price
High_price      maximum expected price

PERSON:
<Note that if you we integrating the financial data and membership data 
functions, you would put birthdays, hobbies, committees the person serves 
on, etc in this table.>
Household_id    An identifier for the household.  Relates to the MEMBER 
                record
Last_name       
First_name
Income          The person's yearly gross income


MEMBER:  This table has one record per household
<note that you might need address information for individuals rather than 
households if your have group households forming in the community that do 
not currently live together>
Household_id    An internal ID for the household.  
Address         Street Address
City            City name
State           State abbreviation
Postal_code     ZIP or equivalent
Country         Only if necessary
Home_phone      This information may be kept in a separate membership DB
Fax_phone
Date_joined
Member_type     Full member, associate member, prospect, former member
Style_ID        The type of unit this household desires
Notes           General info about special circumstances, notes about data 
still needed, etc.
<Assets>
Cash            Household's cash assets
Savings         Not sure how this differs from cash
Property_value  The value of property that may be used to get a mortgage
Home_value      The household's total home(s) value
Life_ins_value  The cash value of any life insurance policies
Stocks          Total value of owned stocks / mutual funds
Car_value       Resale value of the car(s)
Other_assets    Other assets that might be used toward qualifying for a 
mortgage
<Debts>
Credit_debt     Total outstanding credit card balances
Other_debt      Other types of credit debt
Auto_loan       Total outstanding automobile debt
Mortgage        Outstanding mortgage balance
Equity_loan     Outstanding home equity loan(s) balance
Student_loan
Insurance_loan  Life insurance loan balance
<Monthly payments>
Auto_payment    Monthly car payment
Alimony         Alimony and child support payments
Credit_payment  Minimum monthly total credit payments

Calculations:
Household_income = Sum of person.income for each household_id
Total_assets = Cash + Savings + Property_Value + Home_Value 
                + Life_ins_val + Other_Assets + Stocks + Car_Value

Total_debts = Credit_Debt + Other_Debt + Auto_Loan + Mortgage 
              + Equity_Loan + Student_Loans + Insurance Loan

Net_worth = Total_assets - total_debts

These are "max housing cost formulas common for mortgage prequalification.  
The lower of either 31% of monthly income -or- 37% minus monthly payments.
Monthly_pmt1 = (Household_income / 12) * 0.31
Monthly_pmt2 = (("Household Income" / 12) * 0.37) - Car_Payment 
               - Credit_Payments - Alimony

Max mortgage is based on interest rates and monthly payment minus fees for 
desired unit type
Max_mort_pmt = Minimum(Monthly_pmt1, Monthly_pmt2) - FEES.Taxes - 
FEES.Insurance - FEES.Fees

The maximum mortgage can be calculated by multiplying the Max_mort_pmt by 
1000 and dividing by commonly published "mortgage factors".  Or, you can use 
an HP11c or equivalent calculator to do the math.  Some "mortgage factors":

Percent    Term    Factor
8          15      9.557
9          15      10.143
8          30      7.338
9          30      8.046

So a monthly mortgage payment of $1200 at 8% over 30 years would pay off a 
total of
       $1200 * 1000 
       ------------   =    $ 163,532.30
           7.338

The asset and debt numbers aren't used directly in the mortgage calculation, 
but need to be considered when determining whether a household has the 
necessary downpayment for a mortgage.  The information also comes in handy 
when trying to ascertain whether the group has members capable of making 
extra investments into the group.

A household probably is not facing reality if 
    Maximum 9% 30 yr mortgage + total_assets > FEES.High_price
for the unit the household desires.

_____________________________________________________________________

  |\/\/\/|  David G. Adams           |  Homebrewer
  |____  |  U4 Consulting            |  
 OO    ) |  Arlington, MA            |  1995 Boston -> New York
 (       |  dadams [at] world.std.com     |     AIDS Ride cyclist
  |      |  CompuServe: 72630,1374   |    Please Sponsor Me!!
_____________________________________________________________________