Amortization Schedule Excel HW

RE 3010.010 Homework 2 Mortgages: Amortization Schedule & APR

Objective:

To create a mortgage amortization spreadsheet and compare APR for alternative

loans. Learn and utilize excel formulas and graphing tools.

Due date: Tuesday, November 10, 2015 by 9:30 AM

Notes: Use Excel formulas. If not, you will get zero.

You may work with up to one other classmate on this assignment. Please submit

one Excel spreadsheet file for the entire team on D2L Brightspace -

Dropbox. Any team member can make the submission, but please make sure

everyone's name is listed on the spreadsheet, and please include all last names in

the file name. Late assignments will incur a 20% penalty, and will not be accepted

more than 48 hours after the due date.

Question 1: Loan amortization

Consider the loan information in the “Amortization Template” Excel spreadsheet

1. Fill in the blank blue cells in the Mortgage Information section

2. Complete the mortgage amortization schedule

3. Insert the graphs for:

a. Interest & Principal payments

b. Ending balance

Question 2: APR comparison

1. Fill in the blank blue cells to compare the two loans’ APRs in Question 2

2. Briefly comment on the result. Which loan would you choose? (A couple sentences is

sufficient)

Your answer sheet should look similar to the example on the next page:

Homework #2 Name

Question 1 Question 2

Mortgage information

Property Price $250,000 Loan A Loan B Loan Amount $200,000 Months 360 360 Down Payment $50,000 Loan amount $200,000 $200,000 LTV 80.0% Rate 4.1% 3.5% Interest rate 3.5% (annual) Points 0% 2% Compounding Monthly Payment ($966.40) ($898.09) Term 30 (in years) APR 4.10% 3.79% Loan payment ($898.09) (principal & interest only)

Your comment: Amortization Schedule

Month Beg balance Interest Principal End balance 1 $200,000 $583 $314.76 $199,685 2 $199,685 $582 $315.67 $199,370 3 $199,370 $581 $316.59 $199,053 4 $199,053 $581 $317.52 $198,735 5 $198,735 $580 $318.44 $198,417 6 $198,417 $579 $319.37 $198,098 7 $198,098 $578 $320.30 $197,777 8 $197,777 $577 $321.24 $197,456 9 $197,456 $576 $322.18 $197,134

10 $197,134 $575 $323.12 $196,811 11 $196,811 $574 $324.06 $196,487 12 $196,487 $573 $325.00 $196,162 13 $196,162 $572 $325.95 $195,836 14 $195,836 $571 $326.90 $195,509 15 $195,509 $570 $327.86 $195,181 16 $195,181 $569 $328.81 $194,852 17 $194,852 $568 $329.77 $194,522 18 $194,522 $567 $330.73 $194,192 19 $194,192 $566 $331.70 $193,860 20 $193,860 $565 $332.66 $193,527 21 $193,527 $564 $333.63 $193,194 22 $193,194 $563 $334.61 $192,859 23 $192,859 $563 $335.58 $192,524 24 $192,524 $562 $336.56 $192,187 25 $192,187 $561 $337.54 $191,849 26 $191,849 $560 $338.53 $191,511 27 $191,511 $559 $339.52 $191,171 28 $191,171 $558 $340.51 $190,831 29 $190,831 $557 $341.50 $190,489 30 $190,489 $556 $342.50 $190,147

$0

$50,000

$100,000

$150,000

$200,000

$250,000

1 23 45 67 89 11 1

13 3

15 5

17 7

19 9

22 1

24 3

26 5

28 7

30 9

33 1

35 3

End balance

End balance

$0

$100

$200

$300

$400

$500

$600

$700

$800

$900

$1,000

1 20 39 58 77 96 11 5

13 4

15 3

17 2

19 1

21 0

22 9

24 8

26 7

28 6

30 5

32 4

34 3

Interest

Principal