User's Notes on
Conventional Home Amortization Program
CHAP is a Lotus 123 V2.0 spreadsheet template to compute conventional
(including FHA) home loan requirements, analysis of loan amortization,
prediction of the Truth-in-lending APR, summarize home closing costs, buyer
cash flow (to close the deal), and buyer loan qualification. Extensive use is
made of macros to exploit the power of 123.
CHAP is user supported software (shareware). You are granted a limited license
to use CHAP, and to copy it and distribute it, provided that:
1) No fee is charged for copying and distribution, and
2) CHAP may ONLY be distributed in its original, unmodified state.
Please feel free to modify your personal copy in any way that suits your home
purchase and lender conditions. If you find this program useful, you are
encouraged to send $10 to:
Colin K. Drummond
Lakewood, Ohio 44107-0037
Development of a printed document is in-progress and is intended to be more
comprehensive that these User's notes; please write for details or program
You may not use this product in a commercial environment or a governmental
organization without paying a license fee of $49.
WARNING: This spreadsheet is intended to faithfully perform calculations based
on YOUR inputs; CHAP assumes a specific calculation sequence - it is impossible
to warrant that CHAP will mimic calculations of every lender. CHAP provides you
with ESTIMATES - please use Common Sense. The author disclaims any and all
implied warranties of merchantability and fitness for a particular purpose, and
any warranty coverage for incidental and consequental damages.
To many novice home buyers, comparison shopping for home loans is a
confusing process. The 1-2-3 spreadsheet program CHAP is designed to be your
friend in comparing loans, and allowing you to play "what-if" games to lock in
on the feature(s) of a home purchase package that best suits your needs (low
monthly payment or low down payment ?). For simplicity, we focus in on the
"conventional" loan which is simply a loan with a fixed interest rate, and
therefore, fixed monthly payments for the borrower. The important features of
the spreadsheet are:
1. Multiple loan rate input with a loan profile for any one quote,
2. Amortization and closing cost summary,
3. Detailed estimate of settlement costs,
4. Borrower cash flow estimate,
5. Loan qualification estimation.
6. Truth-in-lending APR calculation.
Item 3 is often a large source of confusion for many buyers. It is
important to recognize that a federal statute, the Real Estate Settlement
Procedures Act of 1974 (RESPA), helps to protect the buyer during the home
purchase process. Many of the features of this act are described in an
excellent booklet entitled "Settlement Costs" by the U.S. Department of Housing
and Urban Development (HUD). This 50 page booklet should be read by all
first-time buyers before purchasing a home. The region of the 1-2-3 spreadsheet
on estimated closing costs is modeled after the guidelines presented in the HUD
booklet. The booklet does an excellent job summarizing typical settlement
charges; the closing cost section of the spreadsheet is designed to make it
easier to interface the Section L HUD guidelines with the remainder of your
finance concerns. Please read the HUD booklet for line-by-line settlement
Part 1 of this document describes the input required for the Bank loan
rate profile panel of the worksheet. Information contained in this section is
probably going change the most during the your loan investigation effort. Some
remarks on the input for Closing cost data panel are also made.
Part 2 briefly mentions the input needed in the Borrower cash flow
estimate panel of the spreadsheet. Only three basic questions (Y/N) need be
answered; data in this section is not likely to vary much, perhaps even from
lender to lender.
Part 3 discusses Loan qualification data.
1) This program has been tested on two conventional home loan transactions;
beacuse of certain rounding of numbers by the lenders involved, the predictions
have been within a few dollars of actual transcation amounts. At the time of
this writing two beta tests are in-progress.
2) Due to the current 123 format, the home bid price is limited to a maximum
of $999,999. Minimum FHA bids are at $25,000 (some additional logic in the FHA
down payment calculation is required for lower bids).
Basic CHAP Input Data
User data is required in four sections of the spreadsheet. This part of the
document primarily discusses the input for the Bank loan rate profile section.
When you press the rate input panel appears. The spreadsheet in
in a protected mode, except where you are to customarily place information as
1. Property title/location: Self-explanitory
2. Proposed purchase offer: What you plan to bid for the house
3. Estimated Property taxes: Enter roughly what the area commands; data
sheets you get from "open houses" can help you assess this figure.
4. Estimated fire insurance: Call your insurance company to get a rough
idea for houses in the area and price range of interest to you.
5. Lender & date of quote: Create several versions of CHAP, say, CHAP1,
CHAP2, etc, for each of the lenders you contact. Chances are the bank
will have "their" preffered escrow company in mind, so the closing
costs will be fixed for a specified bank. What is more likely to change
is their interest rate and point requirements.
6. Loan option to explore: REMEMBER THAT ALL SUBSEQUENT CALCULATIONS WILL
DEPEND ON THE LOAN OPTION YOU ENTER HERE !!!
7. Loan options offered: Option 3 is reserved for FHA calculations; keep
this in mind.
8. Contract interest rate: Advertised basic loan rate (NOT the actual
Annual Percentage Rate, APR, required by law to eventually be revealed;
compare APR when comparing loans)
9. Period of loan: Self-explanitory
10. Loan discount points: Enter bank quote.
11. Percent down: Enter bank quote.
12. Required PMI or MIP: This is Private Mortgage Insurance (PMI) that is
often required for loans where less than 20% down is offered, OR the
FHA Mortgage Insurance Premium required by FHA loans. This premium must
be paid up-front. In the case of FHA loans you have the option to
finance the premium; it is important to place Y for yes or N for no in
response to the question "Fin(ance) MIP?". See how much it affects your
13. Additional monthly PMI: Sometimes you are required to pay a monthly PMI
in addition to the up-front premium.
14. Financed closing costs: FHA allows certain closing costs to be
financed, consult the lender as to the standard amount they finance.
Note the BOTTOM LINE sub-panel summarizes three figures often of prime interest
to the prospective buyer.
The Estimated Contract Closing Costs section of the spreadsheet is
accessed by pressing . Consult your lender or an escrow company for
more details. A baseline for a "typical" FHA transaction is provided as the
Borrower Cash Flow Estimate
There can be some confusion about the closing costs involved in transaction and
the amount of cash the buyer needs to close on the deal; access the Borrower
cash flow estimate panel with C> to summarize the estimated cash flow the
buyer will experience. The result hinges on Items the borrower pays in advance
and Adjustments on closing date. The user need only answer the three questions
on which costs lenders (often) require to be paid in advance (or even at the
time of loan application) and fill in any Other payments not included.
This is an important facet of the loan process that dictates whether you home
purchase goal is realistic for your present financial situation. The rules of
thumb are pretty simple for a conventional loan; there may be, however, some
variation in Income or Debt ratio an institution will set, or even a slight
variation of the computational procedure assumed in this panel. Regardless, the
basic calculation offered here should provide a good estimate of what the total
montly payment (PITI, principal, interest, taxes, and insurance) ceiling a
lender will consider. Call a few lenders to get their limits.