You work for a small mortgage broker in San Antonio called MortgagesForAll (MFA). MFA specializes in

loans to companies or individuals that have a down payment of 10% or less of the propertys purchase

price. Your boss is not very good with Excel, including formulas, absolute versus relative cell referencing,

and formatting. In other words, hes pretty useless.

He asked you to make an amortization table for a customer. The amortization table is a schedule of all of

the monthly payments over the life of the loan. The table shows the amount of interest in each

payment, the amount of principal repayment in each payment, and the amount of private mortgage

insurance (PMI) payment in each payment. PMI is explained a bit later in this document. The table also

shows the mortgage balance after each payment is made. Traditionally, each row also has a label to

show the payment number and the date of the payment.

You decide to make an amortization table for this customer, as your boss requested, but this time you

want to make an amortization table that will work for any customer and any mortgage amount. Use the

first couple rows to make a nice header so the table looks really nice when it is printed out.

Then create an area for the given information for the mortgage. That might look something like this:

Anyone who uses the new super-cool template you are

building can just type the given information into the

input cells. In my example to the left, the input cells

are shown in yellow. Since the row amount of

mortgage and monthly P&I payment are not shaded

as input cells, you will have a formula in those cells.

The formula for the amount of the mortgage is easy since

users will enter a percentage in the down payment cell.

The formula you put in the monthly P&I cell is PMT financial function in Excel. Youll have to use cell

references in the functions dialog box, and well talk about this in class.

Underneath the given information cells, youll start the amortization table. The table will have the

following

column

headings:

PMI payment

Total payment

Mortgage balance

Payment number

Payment date

Principal payment

Interest payment

Purchase price of building $889,444.00

Downpayment (enter %)

6%

Amount of mortgage $836,077.36

Annual interest rate

4.20%

PMI rate

0.01%

Monthly P&I payment $4,088.56

Mortgage term in years

30

Heres how private mortgage insurance works (slightly changed from real life). Whenever a borrower

puts down less than 20% of the propertys purchase price, MFA buys an insurance policy that helps

cover its losses if the borrower defaults on the loan. But MFA doesnt pay for the insurance premium

the borrower has to cover it in his or her monthly payments. As soon as the mortgage balance gets low

enough so that the loan-to-value ratio (lets use loan balance/purchase price as the loan-to-value ratio)

is lower than 20%, the borrower no longer has to pay the PMI premium. Suppose the PMI rate is equal

to 0.01% times the mortgage balance from the prior month.

Since you want to build an amortization table that will work for all given information, you will have to

use a formula for the PMI payment using the IF logical function in Excel. Youll need to use cell

references in the dialog box for the formula.

All of MFAs mortgages are for 30 years, which is 360 payments. Does that mean your crazy teacher

wants you to type the numbers 1 through 360 in the payment number column? Of course not! There are

a couple ways to do this quickly. One is to write a simple formula you can copy down the whole column.

Another is to use the fill function, which we will cover in class.

Theres also 360 rows for the payment date. Can you autofill that column? Yes. Use the Excel help

function or search you-tube videos to see how to do that. Use the date format 3/24/16 (for example) for

March 24, 2016. The mortgage that youre working on has the first payment due on June 1, 2019.

For all dollar amounts shown in the body of your spreadsheet, show 2 decimals, a comma to separate

the 1000s, and a dollar sign. Make sure that your columns are not too wide....you want this table to be

only one page wide. Youll need to use wrap text to format the cells for the column headings.

You also want this to print out nicely, so youll need to use print titles on the page layout tab. You

should also use freeze panes to help users view the column titles as they scroll down the worksheet.