For the last piece of this project, you will prepare a HUD-1 Settlement Sheet.

Do not use an official form. Use only the Excel workbook that

is provided. Excel is used, as all students have access to it in their

MS Office program provided by Rasmussen. It also allows you to review

formulas and use them to create automatic totaling and subtotaling. The

Hud-1 direction link is provided for your information, to comprehend

what you are doing. You will be making changes to the Hud-1 A – K page

(sheet) and the L page (sheet) only. There is another page in some Hud-1

sheets for estimates, but you are not using that extra page. In a law

office, you may need to retype your results onto an Adobe pdf file, but

do not do that for this project, please.

You will change some costs in sheet L, add some amounts in J

and K and type in some formulas in J and K. After the changes, you

should see all the correct totals and subtotals. You will fill in

general information in A – I.

Here is the link to the Excel sheet you use:

StudentAssignmentversion_of_Module08RealEstateProject_Excel_Hud1

Additional information about HUD-1 settlement statements can be found here:

Prepare the settlement statement with the following information:

Do L first. You need those numbers to complete parts J and K.

The borrower amounts to pay in L will be typed on the second

column from the left and the seller amounts to pay will be typed on the

far right column. In an Excel sheet, use column B for borrower amounts

and totals and C for seller amounts and totals.

Carefully distinguish between Excel numbering (B12, B15) from

the Hud-1 numbering, 801 in the borrowerâ€™s column and 804 in the

borrowerâ€™s column. Using Excel makes it easier to pinpoint where to

type, but of course you should also familiarize yourself with the Hud-1

system.

- Part L: Settlement Charges
- Change B12 to $250. (Note that Excel sheet cell B12 is in row 801 in the borrowerâ€™s column in the Hud-1 form)
- Change B15 to $375. (Excel cell B15 is in row 804 in the borrowerâ€™s column)
- Change B31 to $3,000 (1003 borrowerâ€™s column)
- Change B50 to $110Ã‚ (1202 borrowerâ€™s column)
- Change C53 to $250 (1205 sellerâ€™s column)

Part J: Summary of Borrower’s Transaction (fill in only what is given here)

The columns used are different. You are now using C for borrowerâ€™s amounts. Ã‚

- Excel sheet C49, borrower column #211: Type $2,000 for real estate tax adjustment.
- Excel sheet C58Ã‚ #220: Type this formula in the cellÃ‚ =SUM(C38:C57)
- #301: This will be the same as 120
- #302: This will be the same as 220
- Excel sheet C62 #303: Type this formula in the cell =SUM(C60-C61)
- Your total for C62 #303 should be $1,835 If it is not,

recheck all your formulas and changes to dollar amounts. You are

required to use formulas, not just type in totals and subtotals. - Part K: Summary of Seller’s Transaction. You are using Excel column I for sellerâ€™s amounts.
- Excel sheet I49 #511: Type $2,000 for real estate tax adjustment.
- #520: Add up lines 501-519 Type this formula in the cell =SUM(I38:I57, )
- #601: This will be the same as line 420
- #602: This will be the same as line 520
- Excel sheet I62 #603: Type in this formula: =SUM(I60-I61)
- Your total for I62 #603 should be $197,750. If that is

not what you got, check your formulas and changes to dollar

amounts. You are required to use formulas, not just type in

totals and subtotals.

A. – I:

- Number 1: This is a conventional loan (Conv. Ins.).
- Numbers 6-8: Do not worry about the file number, loan number or mortgage insurance case number.
- Part D – Borrower: You are the borrower.
- Part E – Sellers: Dudley J. and Jayne M. Smith, 1987 Ford Street, your city or town, your state – your zip code
- Part F – Lender: First Star State Bank, 973 Main Street, (your city or town), (your state) (your zip code)
- Part G: Address of property
- Part H: First Star State Bank – Place of settlement is their location
- Part I: Today’s date