Question

1) You wish to borrow $150,000 from a lending institution for the purchase of a house. The bank will lend this amount at an Annual Percentage Rate of 4.5% to be paid-off with equal monthly mortgage payments over a 30-year period. This is a 4.5% APR, 30-year fixed-rate mortgage loan. You wish to know how this loan will affect your federal income tax burden, as only the interest paid on a home mortgage, not the principal, is tax deductible. Construct an Excel spreadsheet that: 1. Calculates your monthly payments: Calculates for each month, how much of each payment goes toward paying down the principal, and how much is interest payment. 2. Use 8-point font size in the main table, and use only 3 pages for constructing your table, with suggested margins of about 0.5 inches on all sides. Include page numbers (Insert, Footnote, etc) 3. Include your name on each page. A good way to start is: FI G J K L EEE 30000, Homework 1, Spreadsheet Basics 1) Home Mortgage Principal Borrowed Annual Interest Rate 150,000 0.045 360 EEE 30000 Your Name Num. Monthly Payments Interest Paid Principal Paid Frst Year Interest 123610.0 150000.00 Monthly Payment 760.03 Month Interest Principal New Principal 10 150000.00 Month Interest Principal New Principal Month Interest Principal New Principal 48338 276 65128624 45 2 482.34 27789 128346 77 13T3 56102 1990114940552451 23552 139632.93 481.30 278.73 128068 04 53 14980247 1.7 19827 149604.20 1562 50 197 46 52626 233.76 140103.14 47 52539 234 64 139868.50 On page 4: 2) From summing the interest payments in the table in problem 1, calculate the total interest paid on the loan. Compare this value with that calculated with the built-in function CUMIPMT. (Place your answer on page 1 also.

Answer 1

Amortization table is as below (The entire table cannot be placed here since there is a length restriction on the answer)

The first 10 and last 10 instalments are given below.

Loan Amount	Interest Rate	Term in Years	Monthly Payment
$150,000.00	4.50%	30	$760.03
Month	StartingBalance	Interest	Principal	EndingBalance	TotalInterest
1	$150,000.00	$562.50	$197.53	$149,802.47	$562.50
2	$149,802.47	$561.76	$198.27	$149,604.20	$1,124.26
3	$149,604.20	$561.02	$199.01	$149,405.19	$1,685.28
4	$149,405.19	$560.27	$199.76	$149,205.43	$2,245.54
5	$149,205.43	$559.52	$200.51	$149,004.93	$2,805.06
6	$149,004.93	$558.77	$201.26	$148,803.67	$3,363.83
7	$148,803.67	$558.01	$202.01	$148,601.65	$3,921.85
8	$148,601.65	$557.26	$202.77	$148,398.88	$4,479.10
9	$148,398.88	$556.50	$203.53	$148,195.35	$5,035.60
10	$148,195.35	$555.73	$204.30	$147,991.05	$5,591.33

350	$8,175.22	$30.66	$729.37	$7,445.85	$123,455.63
351	$7,445.85	$27.92	$732.11	$6,713.74	$123,483.56
352	$6,713.74	$25.18	$734.85	$5,978.89	$123,508.73
353	$5,978.89	$22.42	$737.61	$5,241.28	$123,531.15
354	$5,241.28	$19.65	$740.37	$4,500.91	$123,550.81
355	$4,500.91	$16.88	$743.15	$3,757.76	$123,567.69
356	$3,757.76	$14.09	$745.94	$3,011.82	$123,581.78
357	$3,011.82	$11.29	$748.73	$2,263.09	$123,593.07
358	$2,263.09	$8.49	$751.54	$1,511.55	$123,601.56
359	$1,511.55	$5.67	$754.36	$757.19	$123,607.23
360	$757.19	$2.84	$757.19	$0.00	$123,610.07

Sample calculations

Total INTEREST = 123610.07 as per excel

Using excel function

=CUMIPMT(4.5%/12,360,150000,1,360,0)