Mortgage Assignment
Deliverables (2 documents):

Excel spreadsheet with calculations based on assignment
Word document with assignment
The mortgage assignment requires 2 documents. The first document is a Word document (not a pdf or other type of document) that requires you to provide the following information on a conventional mortgage:
Describe what a conventional mortgage is.
a. 20% down payment for 30yrs.
What are the current conventional mortgage rates in the greater Boston area for a 15 and 30 year mortgage? (find the lowest rate and cite your source – bank, mortgage company, etc.)
a. For 15 yrs the rate would be 3.31% and for a 30 yrs mortgage would be 4%. (and mention where you got this rate from)
Describe what private mortgage insurance (PMI) is.
a. This insurance is usually connected to conventional loans, these are usually used when the payment is less than 20%. This is to secure the safety of the lender.
When is PMI needed and how is PMI calculated?
a. PMI is used when a person cannot make the minimum down payment for the house if it is less than 20%.
What are the down payment requirements for a conventional mortgage to avoid PMI (private mortgage insurance)?
a. Anything that is greater than or equal to the 20% down payment.
Under the FHA guidelines what is the maximum ‘debt ratio’ you can have for a mortgage? (www.fha.com is a good source for information)
What does it mean to escrow your tax payments?
What is the 2018-2019 tax rate for residential property in Boston?
How much would the insurance cost for a \$400,000 real estate insurance policy for a residence in Boston? (this may vary based on insurance agency and location in Boston – cite your source)
The second document will be an Excel document that is created by you to calculate the total monthly cost of owning a residence in Boston. The picture of a sample of the spreadsheet is provided in this document. You must have a functioning spreadsheet in order to get credit for this part of the assignment.
The Mortgage Calculator
Below there is an example of the mortgage calculator you saw in class. You can use it to model your spreadsheet after or you can design your own calculator.
You need to design the spreadsheet so that you have to enter the information in cells (in the example they are the yellow cells) in order to make this work.
For this assignment you are buying a house that is being sold to you for \$400,000 in Boston
You will need to enter the following information (shown in the yellow boxes in the example) in order for this to calculate your monthly payment correctly:
Mortgage rate (not APR)
The term of the loan – number of years (usually 30)
How much of a down payment are you putting down?
Have the spreadsheet calculate the down payment percentage of the total cost of the house (needed to determine PMI requirements)
Based on your down payment have the calculator determine if PMI is required. This will require using some logic statement formulas in Excel. If it is required, the PMI cost needs to be included in your monthly mortgage payment. This should be something you researched for the paper.
How much is the real estate insurance (based on a \$400,000 residence in Boston)?
Have the spreadsheet calculate the annual taxes in the residential property in Boston (based on your earlier research).
Finally, have the spreadsheet show you (green box) your total monthly mortgage payment.