Question

Loans Excel Homework Assignment Mortgage Amortization Schedule Excel Assignment 1. Excel: Complete the amortization table provided in the Excel document posted in in the homework area by setting the appropriate values for a $165,000, 30-year mortgage at 4.5% interest and using Excel's autofill (drag) feature to fill in the cells to the end of the mortgage period. Use this to answer the following: a. How much of the first payment goes towards the principal? How much goes toward the interest? b. How much of the last payment goes towards the principal? How much goes toward the interest? c. Find the total interest paid by the end of the mortgage. d. How much will be owed on the mortgage after making payments for 10 years (hint this is 120 months)? e. How much interest has been paid total on the mortgage at the end of 10 years? f. How many months will it take to pay at least half of the principal? 2. Change the spreadsheet from the last question so that the mortgage is for 15 years rather than 30 years. a. Find the total interest paid by the end of the mortgage. b. What are some advantages and disadvantages of getting a shorter term mortgage? Excel Amortization table tips: Do enter the APR as a decimal value (Excel is formatted to then correctly represent it as a percentage). In order to correctly copy the month numbers in 'column A', you must select at least 2 consecutive months to establish a pattern to copy and drag. In 'column B' only drag cell "B3" - do not select 'BZ" as part of the drag and copy

Answer 1

M IK 2 Year L opening Balance N Interest Part EMI Principal Part Ending Balance 3 4 1 5 =K4+1 6 =K5+1 7 =K6+1 8 =K7+1 9 =K8+1 10 =K9+1 11 = 10+1 12 =K11+1 13 =K12+1 14 =K13+1 15 =K14+1 16 =K15+1 17 = 16+1 18 =K17+1 19 =K18+1 20 =K19+1 21 =K20+1 22 =K21+1 165000 =P4 =P5 =P6 =P7 =P8 =P9 =P10 =P11 =P12 =P13 =P14 =P15 =P16 =P17 =P18 =P19 =P20 =P21 836.030761212703 =L4*4.5%/12 =M4 =L5*4.5%/12 =M5 =L6*4.5%/12 =M6 =L7*4.5%/12 =M7 L8*4.5%/12 =M8 =L9*4.5%/12 =M9 =L10*4.5%/12 =M10 =L11*4.5%/12 =M11 =L12*4.5%/12 =M12 =L13*4.5%/12 =M13 =L14*4.5%/12 =M14 =L15*4.5%/12 =M15 =L16*4.5%/12 =M16 =L17*4.5%/12 =M17 =L18*4.5%/12 =M18 =L19*4.5%/12 =M19 =L20*4.5%/12 =M20 =L21*4.5%/12 EM21 =L22*4.5%/12 =M4-N4 M5-N5 =M6-N6 =M7-N7 =M8-N8 =M9-N9 =M10-N10 =M11-N11 =M12-N12 =M13-N13 =M14-N14 =M15-N15 =M16-N16 =M17-N17 =M18-N18 =M19-N19 =M20-N20 =M21-N21 EM22-N22 boots boot7 L4-04 =L5-05 =L6-06 =L7-07 L8-08 =L9-09 =L10-010 =L11-011 =L12-012 =L13-013 =L14-014 =L15-015 =L16-016 =L17-017 =L18-018 =L19-019 =L20-020 =L21-021 =L22-022 CLICEO botez chacC cha cho c hota

Use the above formulas and drag it to 360 months, you'll get all the answers below

a.
Principal part = $ 217.28
Interest part = $ 618.75

b.
Principal part = $ 832.91
Interest part = $ 3.12

c.
Total Interest 135,971.07

d.
Amount outstanding will be $ 132,147.57

e.
Total interest is $ 67,471.26

f.
It is at least 237 months

2.
a.
Total Interest $ 62,203.01

b.
Advantages- Lesser interest as can be seen in the example above. Loan would be paid off early.
Disadvantage - Heavy monthly payments.