COSC1371 Project-7 Due: Thursday, 10/27/2011
Mortgage Payment Table
(EXCEL-3)
Objectives:
1. Continued use of EXCEL2010 in generating a Mortgage payment table.
2. Create a table showing monthly mortgage payments for various terms and
various APR for the fixed principal of $100000.
3. Optionally (for 5 extra points), create another table showing the remaining balance at end
of each year when $100000 is borrowed for 5%APR and 30-year term.
4. Insert a chart such as a Column Chart.
5. Use some useful functions such as PMT(rate, nPer, Principal, fv),
FV(rate, nPer, amount, pv, type) and POWER(number,exponent)
Steps:
1. Open the EXCEL from the Programs Group.
2. Enter a header that must include your full-name and date/time.
For this, you can use "Header & Footer" Command on the INSERT Tab.
3. Enter the table headings in E2 and E3, centered.
4. Enter 'TERM|APR' in A5.
5. Enter the 6 APR's in C5:H5
6. Enter the 21 terms in A7:A27
7. Enter into C7: =PMT($C$5/12, A7*12, -100000) //5%APR (C5), 10 year term (A7)
(Note that the principal must be negative to get a positive function value.)
8. Copy C7 into each of the next five cells (D7:H7) and modify both references
in each copied cell.
Each copied cell has one absolute cell reference and one relative cell reference.
Both must be adjusted because the one that should remain the same has changed
while the other that should have been adjusted remains the same.
For copying a cell into neighboring cells, you can use the Fill Handle of the cell.
9. Copy the entire row#7 (of steps 7 and 8 above) into each of the next 20 rows.
10. Save and name this file: Payment.XLSX
11. Insert a column chart for the entire table including the column headings that show
respective APR's. (like 5% APR, 6%APR, etc)
12. Optionally, for the extra bonus of up to 10 points,
create and save a Mortgage Balanace Table under the name of Balance.XLSX,
that shows the remaining balance at the end of each year for a 5%APR, 30 Year
mortgage for the principal of $100,000.
To see this table, click:
Mortgage Balance Table
You will need three functions: PMT(), FV(), and POWER() for this.
13. Turn in a USB that contains above one or two Excel files as usual.
--------------------------------------------------------------------------
11:30AM, 3/6/2009, by Prof. H. Koh
MORTGAGE PAYMENT TABLE
for the principal of $100,000
TERM|APR 5% 6% 7% 8% 9% 10%
---------------------------------------------------------
10: 1060.66 1110.21 1161.08 1213.28 1266.76 1321.51
11: 986.45 1036.70 1088.41 1141.54 1196.08 1251.99
12: 924.89 975.85 1028.38 1082.45 1138.03 1195.08
13: 873.06 924.72 978.07 1033.07 1089.68 1147.85
14: 828.87 881.24 935.40 991.32 1048.94 1108.20
15: 790.79 843.86 898.83 955.65 1014.27 1074.61
16: 757.68 811.44 867.21 924.93 984.52 1045.90
17: 728.66 783.10 839.66 898.26 958.80 1021.21
18: 703.03 758.16 815.50 874.96 936.44 999.84
19: 680.28 736.08 794.19 854.50 916.90 981.26
20: 659.96 716.43 775.30 836.44 899.73 965.02
21: 641.72 698.86 758.47 820.43 884.58 950.78
22: 625.28 683.07 743.42 806.18 871.17 938.25
23: 610.41 668.85 729.92 793.45 859.27 927.18
24: 596.90 655.98 717.76 782.05 848.66 917.39
25: 584.59 644.30 706.78 771.82 839.20 908.70
26: 573.34 633.68 696.84 762.60 830.72 900.98
27: 563.04 623.99 687.81 754.28 823.13 894.10
28: 553.57 615.12 679.61 746.76 816.30 887.96
29: 544.86 607.00 672.13 739.95 810.16 882.48
30: 536.82 599.55 665.30 733.76 804.62 877.57