Spreadsheet project 1

Loans

Professor John purchased a house on September 1, 2003. The house cost $184,000. He made a down payment of $40,000 and borrowed the rest in the form of a mortgage (real estate loan where real estate is collateral until final repayment).

Professor Albrecht was quoted a fixed rate of 6.25% (doesnt change during life of mortgage) for a 30 year mortgage with no points (interest payment up front), and 12 monthly payments per year.

Required:

Part 1: Prepare an amortization table that will be useful in accounting for this mortgage, assuming the mortgage was taken out September 1, 2003, and the first payment was due on October 1, 2003. Place this amortization table on page A of the spreadsheet. In an e-mail, answer these questions, How much is the unpaid mortgage balance immediately after making the Feb 1, 2009 loan payment? and How much is the 360th and final payment?

Part 2: Professor John has been paying an extra $100 every month, starting with the first payment. Prepare an amortization table that accounts for each additional $100 payment made so far. Place this amortization table on page B of the spreadsheet. In the e-mail, answer these questions, How much is the unpaid mortgage balance immediately after he makes his mortgage payment on Feb 1, 2009? and If Professor John continues paying an extra $100 every month, in what year and month will the mortgage be paid off?

Spreadsheet project 2

Loans

Professor John is trying to make a decision about how to finance the purchase of a vacation cottage in picturesque Accounting villa, Michigan. He can afford a down payment, but needs to borrow $98,000 to completely fund the purchase.

The Finance4Suckers Mortgage Company has two financial products for Professor John, a fifteen year 6.55% mortgage loan and a twenty year 7.05% mortgage loan. Both are fixed rate mortgages, meaning the rate doesnt change during the life of the mortgage, and both require 12 monthly payments per year. The final payment amount floats to the number needed to pay interest and the final amount of principal.

Required:

Part 1: Prepare a table of initial values for each mortgage loan. For guidance, refer to the resource titled Amortization Tables on MS Excel.

Part 2: For each mortgage, prepare an amortization table that would be useful in accounting for it, assuming the mortgage is taken out Feb 1, 2009, and the first payment is due on March 1, 2009. These amortization tables can be placed side by side on the same page or your spreadsheet. Include a field in your spreadsheet where you show the total interest paid over the life of each loan. This field can be placed in the table of initial values.

Part 3: Answer these questions for each mortgage. These answers should also be incorporated in you spreadsheet files.

(1) How much is a normal monthly payment?

(2) How much is the final payment?

(3) How much interest is paid over the life of the loan?

General Instructions:

-Refer to the instructional resource titled, Amortization tables on MS Excel, for instructions in creating an amortization table using a spreadsheet program.

-Work on this assignment individually

-Your work also will be evaluated based on your understanding to the problem.