System Admin Max Points: 5.0 You are in the process of buying a house. Your mort

Question

System Admin Max Points: 5.0 You are in the process of buying a house. Your mortgage lender reviewed your credit score, employment history, debt-to-income ratio, and available funds you’ve set aside for the down payment. He quoted you a 2.75% interest rate for a 15-year loan, and a 3.50% rate for a 30-year mortgage loan. The house you want is $200,000 and you can make a 10% down payment. Using Excel, construct an amortization schedule for the amount you need to borrow from your mortgage lender for each loan option. Which of the two is a better financing decision, and why? Show all formulas required to perform these calculations and fully explain your decision making process.

Explanation / Answer

AMOUNT OF LOAN 200000 AMOUNT OF LOAN 200000 DOWN PAYMENT 10% 20000 DOWN PAYMENT 10% 20000 LOAN AMOUNT 180000 BALANCE LOAN AMOUNT 180000 BALANCE YEAR INSTALLMENT INTEREST PRINCIPAL 180000 YEAR INSTALLMENT INTEREST PRINCIPAL 180000 1 $14,806.65 4950 $9,856.65 $170,143.35 1 $9,786.84 6300 $3,486.84 $176,513.16 2 $14,806.65 $4,678.94 $10,127.71 $160,015.64 2 $9,786.84 $6,177.96 $3,608.88 $172,904.28 3 $14,806.65 $4,400.43 $10,406.22 $149,609.42 3 $9,786.84 $6,051.65 $3,735.19 $169,169.09 4 $14,806.65 $4,114.26 $10,692.39 $138,917.03 4 $9,786.84 $5,920.92 $3,865.92 $165,303.17 5 $14,806.65 $3,820.22 $10,986.43 $127,930.59 5 $9,786.84 $5,785.61 $4,001.23 $161,301.94 6 $14,806.65 $3,518.09 $11,288.56 $116,642.03 6 $9,786.84 $5,645.57 $4,141.27 $157,160.67 7 $14,806.65 $3,207.66 $11,599.00 $105,043.04 7 $9,786.84 $5,500.62 $4,286.22 $152,874.45 8 $14,806.65 $2,888.68 $11,917.97 $93,125.07 8 $9,786.84 $5,350.61 $4,436.23 $148,438.22 9 $14,806.65 $2,560.94 $12,245.71 $80,879.36 9 $9,786.84 $5,195.34 $4,591.50 $143,846.72 10 $14,806.65 $2,224.18 $12,582.47 $68,296.89 10 $9,786.84 $5,034.64 $4,752.20 $139,094.51 11 $14,806.65 $1,878.16 $12,928.49 $55,368.40 11 $9,786.84 $4,868.31 $4,918.53 $134,175.98 12 $14,806.65 $1,522.63 $13,284.02 $42,084.38 12 $9,786.84 $4,696.16 $5,090.68 $129,085.30 13 $14,806.65 $1,157.32 $13,649.33 $28,435.05 13 $9,786.84 $4,517.99 $5,268.85 $123,816.45 14 $14,806.65 $781.96 $14,024.69 $14,410.37 14 $9,786.84 $4,333.58 $5,453.26 $118,363.18 15 $14,806.65 $396.29 $14,410.37 $0.00 15 $9,786.84 $4,142.71 $5,644.13 $112,719.05 16 $9,786.84 $3,945.17 $5,841.67 $106,877.38 TOTAL INTEREST $42,099.77 17 $9,786.84 $3,740.71 $6,046.13 $100,831.25 18 $9,786.84 $3,529.09 $6,257.75 $94,573.50 OPTION FIRST OF 15 YEARS IS A BETTER ONE BECAUSE YOU WILL HAVE TO PAY ONLY 42099 AS INTEREST WHILE IN 2ND CASE IT IS 113605 19 $9,786.84 $3,310.07 $6,476.77 $88,096.74 20 $9,786.84 $3,083.39 $6,703.45 $81,393.28 21 $9,786.84 $2,848.76 $6,938.07 $74,455.21 22 $9,786.84 $2,605.93 $7,180.91 $67,274.30 23 $9,786.84 $2,354.60 $7,432.24 $59,842.06 24 $9,786.84 $2,094.47 $7,692.37 $52,149.69 25 $9,786.84 $1,825.24 $7,961.60 $44,188.09 26 $9,786.84 $1,546.58 $8,240.26 $35,947.84 27 $9,786.84 $1,258.17 $8,528.67 $27,419.17 28 $9,786.84 $959.67 $8,827.17 $18,592.00 29 $9,786.84 $650.72 $9,136.12 $9,455.88 30 $9,786.84 $330.96 $9,455.88 ($0.00) TOTAL INTEREST $113,605.19

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.