Question

** Please read the bold statement after the question, I have the answers, but I need to make sure they are correct. Thanks**

#1 Using a spreadsheet application, create an amortization schedule for a 30 year, fixed rate (4.58%) $200k loan. Answer the following: what is the monthly payment? how much total interest will you pay? Print out enough of your spreadsheet to defend your work and answers.

Assume that you took the loan in #1 and paid your monthly payments for 5 years. Your friend tells you about a great new rate to refinance your home. You can refinance the outstanding principal by taking out a new 30 year, fixed rate (3.28%) loan. What is your new monthly payment? how much TOTAL interest will you pay for your original $200k loan? What are the pros/ cons of refinancing? Print out enough of your spreadsheet to defend your work and answers.

Assume that you took the loan in #1. Instead of making the minimum payment, you decide that you can swing an extra $75 each month towards the payments. Answer the following: what are you paying each month toward the loan? how much total interest will you pay? how long will it take you to pay off your loan? What are the pros/ cons of making an extra payment? Print out enough of your spreadsheet to defend your work and answers.

** Please use Excel! Someone tried to Solve this, but I'm not sure it it is fully answered. Please go over the answer to the question below, and make corrections as needed. Thanks **

Answer the following: 1) what is the monthly payment? - $ 1022.90 2) how much total interest will you pay - $ 168243.854 3) You can refinance the outstanding principal by taking out a new 30 year, fixed-rate (3.28%) loan. What is your new monthly payment? - Ans $ 797.42 4) how much TOTAL interest will you pay for your original $200k loan? - $ 44606.44 Refinancing Pro Cons Low Your Monthly payment Your 30 Years Will reset and you will pay more in Total Interest Reduce overall Interest payments Increase the duration and will not save in long Run Predictability Stability and Cost Saving IF rate Drop need to Look another refinancing option Saving in Interest Monthly payment will be higher Availability of Cash for Other Needs Losing more money in the long run : assume that you took the loan in #1. Instead of making the minimum payment, you decide that you can swing an extra $75 each month towards the payments. Answer the following: what are you paying each month toward the loan? Ans - Each Month payment - $ 1097.90 : how much total interest will you pay? Ans $ 142483.08 : how long will it take you to pay off your loan - Ans 312 EMIS. That is 26 years Pros: • Less interest translates to more money saved • Improved credit score if you're free of debt • Free money to use for whatever you please - reinvesting, splurging, etc • Opportunity to get a new loan which might offer a better rate • Ongoing fees can be avoided

Answer 1

Answer 1) Monthly Interest Paid $ 1022.90 2) Interest Will Be pay $168243.85 3) After Refinancing Montly EMI Will Be $797.42 4) Total Interest will be payable After Refinancing $148443.71 Pros Low your monthly payments Reduce Overall Interest payments Saving In Interest Availability of cash for other needs Cons No of EMI should be increased Increase the duration and not saving in long run You took the loan in# 1 and make the $75 minimum loan repayment monthly with EM 1) Monthly Interest Paid $ 1097.90 2) Interest Will Be pay $ 142483.09 3) you will pay emi 312 instead of 360 i.e. 26 years Pros Less Interest payment Reduce The no of EMIS Reduce Amount Cons Less Saving done in EMI Period Make More EMI Payment Loan amount Annual interest rate Loan period in years Number of payments per year Start date of loan Scheduled payment Scheduled number of payments Actual number of payments Total early payments Total interest 1) 1022.9 200000 4.58 % 360 30 360 12 01-01-2019 0 168243.9 2) After Refinancing After 5 years Completion Loan amount Annual interest rate Loan period in years Number of payments per year Start date of loan Scheduled payment Scheduled number of payments Actual number of payments Total early payments Total interest 182535.79 797.4159 3.28 % 360 360 30 12 0 01-01-2019 104533.9 3) if make additional payment $75 Scheduled payment Scheduled number of payments Actual number of payments Loan amount Annual interest rate Loan period in years Number of payments per year Start date of loan Optional extra payments 200000 1022.9 4.58% 360 30 313 Total early payments Total interest 12 23334 47 01-01-2019 142483.1 75 EMI should be $1097.9

EMI Shedule for first 5 [email protected] % SCHEDULE EXTRA PAYMEN CUMULATI VE INTEREST ENDING PAYMENT PMT NO DATE 101-01-2019 201-02-2019 301-03-2019 4 01-04-2019 501-05-2019 6 01-06-2019I D PRINCIPA BEGINNING BALANCE PAYMENT T TOTAL PAYMENT L INTEREST BALANCE 763.33 1,99,740.43 762.34 1,99,479.88 761.35 1,99,218.33 2,00,000.00 1,022.90 1,022.90 259.57 763.33 1,022.90 1,525.68 1,99,740.43 1,99,479.88 1,99,218.33 1,022.90 1,022.90 1,022.90 260.56 1,022.90 261.5S 2,287.02 760.35 1,98,955.78 759.35 1,98.692.22 758.34 1,98,427.67 757.33 1,98,162.10 756.32 1,97,895.52 755.30 1.97.627.92 754.28 1,97,359.30 753.25 1,97,089.66 752.23 196.818.98 1,022.90 262.55 3,047.37 1,98,955.78 1,022.90 1,022.90 263.55 3,806.72 1,022.90 1,022.90 1,98,692.22 264.56 4,565.06 7 01-07-2019 1,98,427.67 1,022.90 1,022.90 265.57 5,322.40 8 01-08-2019 1,98.162.10 1,97.895.52 1,022.90 1,022.90 266.58 6,078.71 9 01-09-2019 10 01-10-2019 11 01-11-2019 1.022.90 1,022.90 267.60 6,834.02 1,022.90 7,588.30 1,97,627.92 1,022.90 268.62 1,97,359.30 1,022.90 1,022.90 269.64 8,341.55 12 01-12-2019 13 01-01-2020 1,97,089.66 1,022.90 1,022.90 270.67 9,093.78 9,844.97 750.16 1,96,274.53 10,595.12 1,96,818.98 751.19 1,96,547.27 1,022.90 1,022.90 271.71 1,022.90 14 01-02-2020 1,96,547.27 1,022.90 272.74 15 01-03-2020 16 01-04-2020 1701-05-2020 18 01-06-202ol 19 01-07-2020 1,96,274.53 1,022.90 1,022.90 273.79 749.11 1,96,000.74 11,344.24 1,022.90 1,96,000.74 1,022.90 274.83 748.07 1,95,725.91 12,092.31 1,022.90 747.02 1,95,450.04 12,839.33 745.97 1,95,173.10 13,585.30 1,94,895.11 14,330.21 743.85 1,94,616.06 15,074.06 742.78 1,94,335.95 15,816.84 741.72 1,94,054.77 16,558.56 740.64 1,93,772.51 17,299.20 739.57 1,93,489.17 18,038.76 738.48 1,93,204.76 18,777.25 737.40 1,92,919.26 19,514.65 1,95,725.91 1,022.90 275.88 1,022.90 1,95,450.04 1,022.90 276.93 1,95,173.10 1,022.90 1,022.90 277.99 744.91 20 01-08-2020 1,022.90 1,022.90 1,94,895.11 279.05 21 01-09-2020 22 01-10-2020 23 01-11-2020 24 01-12-2020 25 01-01-2021 26 01-02-2021 27 01-03-2021 28 01-04-2021 29 01-05-2021 30 01-06-2021 31 01-07-2021 32 01-08-2021 33 01-09-2021 34 01-10-2021 1,94,616.06 1,022.90 280.11 1,022.90 1,94,335.95 1,022.90 1,022.90 281.18 1,94,054.77 1,022.90 1,022.90 282.26 1,93,772.51 1,022.90 1,022.90 283.33 1,022.90 1,93,489.17 1,93,204.76 1,92,919.26 1,022.90 1,022.90 284.42 1,022.90 285.50 1,022.90 1,022.90 286.59 736.31 1,92,632.67 20,250.95 1,022.90 1,022.90 1,022.90 735.21 1,92,344.98 20,986.17 734.12 1,92,056.20 21,720.29 733.01 1,91,766.31 22,453.30 731.91 1,91475.32 23,185.21 730.80 1,91,183.22 23,916.01 729.68 1,90,890.00 24,645.69 728.56 1,90,595.67 25,374.25 727.44 1,90,300.21 26,101.69 287.68 1,92,632.67 1,022.90 1,92,344.98 1,022.90 288.78 1,92,056.20 1,91,766.31 1,91,475.32 1,91,183.22 1,022.90 289.89 1,022.90 1,022.90 290.99 1,022.90 1,022.90 292.10 1,022.90 1,022.90 293.22 1,90,890.00 1,022.90 1,022.90 294.34 35 01-11-2021 36 01-12-2021 37 01-01-2022 38 01-02-2022 39 01-03-2022: 40 01-04-2022 41 01-05-2022 1,90,595.67 1,022.90 1,022.90 295.46 1,90,300.21 1,022.90 1,022.90 296.59 726.31 1,90,003.62 26,828.00 725.18 1,89,705.90 27,553.19 724.04 1,89,407.04 28,277.23 722.90 1,89,107.05 29,000.13 1,88,805.91 29,721.89 1,022.90 1,90,003.62 1,022.90 297.72 1,89,70590 1,022.90 1,022.90 298.86 1,022.90 1,022.90 1,89,407.04 300.00 1.89.107.05 301.14 1,022.90 1,022.90 721.76 1,88,805.91 1,022.90 1,022.90 302.29 720.61 1,88,503.62 30,442.50 42 01-06-2022 1,88,503.62 1,022.90 1,022.90 303.44 719.46 1,88,200.17 31,161.96 1,022.90 1,022.90 1,022.90 43 01-07-2022 1,88,200.17 1,022.90 304.60 718.30 1,87895.57 31,880.25 44 01-08-2022 45 01-09-2022 46 01-10-2022 717.13 1,87,589.81 32,597.39 715.97 1,87,282.87 33,313.36 305.76 1,87,895.57 1,022.90 1,87,589.81 1,022.90 306.93 1,87,282.87 1,022.90 1,022.90 308.10 714.80 1,86,974.77 34,028.15 1,022.90 1,022.90 1,022.90 713.62 1,86,665.49 34,741.77 47 01-11-2022 1,86,974.77 1,022.90 309.28 310.46 48 01-12-2022 49 01-01-2023 50 01-02-2023 712.44 1,86,355.03 35,454.21 711.26 186,043.39 36.165.47 1,86,665.49 1,022.90 1.86.355.03 1,022.90 311.64 710.07 1,85,730.55 36,875.53 708.87 1,85,416.53 37,584.40 1,85,101.30 38,292.08 1,86,043.39 1.022.90 1,022.90 312.83 1,022.90 51 01-03-2023 52 01-04-2023 1,85,730.55 1,022.90 314.03 1,85,416.53 1,85,101.30 1,022.90 1,022.90 315.23 707.67 53 01-05-20234 1,022.90 1,022.90 316.43 706.47 1,84,784.87 38,998.55 1,022.90 54 01-06-2023 1,84,467.23 39,703.81 1,84,784.87 1,022.90 317.64 705.26

55 01-07-2023 56 01-08-2023 57 01-09-2023 58 01-10-2023 59 01-11-2023 704.05 1,84,148.38 40,407.86 702.83 1,83828.32 41,110.69 701.61 1,83,507.03 41,812.30 700.39 1,83,184.51 42,512.69 699.15 1,82,860.77 43,211.84 697.92 1,82,535.79 43,909.76 1.022.90 1,84,467.23 1,022.90 318.85 320.07 1,84,148.38 1,022.90 1,022.90 1,022.90 1,022.90 1,022.90 321.29 1,83.828.32 1,83,507.03 1,022.90 322.51 183.184.51 1,022.90 1,022.90 323.75 60 01-12-2023 1,82,860.77 1.022.90 1,022.90 324.98