I need to do this problem with Excel. can you pls help me with this problem. Tha
ID: 2766112 • Letter: I
Question
I need to do this problem with Excel. can you pls help me with this problem. Thanks
You are purchasing a house for dollar400,000 and have 20% as down payment. The bank is offering a 30-year fixed loan at an APR of 4.0%. Use two (2) decimal places in your answers. Answer the following: Using the Excel template shown below, prepare an amortization schedule for the length of the loan. How much total interest did you pay in the first year of the loan? How much of your principal balance was reduced in the first year? How much total interest did you pay in the 15^th year of the loan? How much of your principal balance was reduced in the 15th year? How much interest did you pay in the last year of the loan? When would the loan be paid off if you added an extra dollar 200 each month starting with the first payment? What is the amount of the last payment under this scenario (g)?Explanation / Answer
Due to space constraint I have uploaded the schedule for 1 year.
b) 14559.94
c) 6446.33
d) 10636.35
f) 6.34
g) there will be 301 payments if $200 is paid extra with the total payments that is in the year.
h) 2109.66
PmtNo. Payment Date Beginning Balance Scheduled Payment Extra Payment Total Payment = Total Payment + Extra payment Principal Interest = P x rate / 12 Ending Balance = Beginning balance - Principal payment Cumulative Interest 1 2/1/2016 $ 400,000.00 $ 1,909.66 $ - $ 1,909.66 $ 576.33 $ 1,333.33 $ 399,423.67 $ 1,333.33 2 3/1/2016 399,423.67 1,909.66 - 1,909.66 578.25 1,331.41 398,845.42 2,664.75 3 4/1/2016 398,845.42 1,909.66 - 1,909.66 580.18 1,329.48 398,265.25 3,994.23 4 5/1/2016 398,265.25 1,909.66 - 1,909.66 582.11 1,327.55 397,683.14 5,321.78 5 6/1/2016 397,683.14 1,909.66 - 1,909.66 584.05 1,325.61 397,099.09 6,647.39 6 7/1/2016 397,099.09 1,909.66 - 1,909.66 586.00 1,323.66 396,513.09 7,971.06 7 8/1/2016 396,513.09 1,909.66 - 1,909.66 587.95 1,321.71 395,925.14 9,292.77 8 9/1/2016 395,925.14 1,909.66 - 1,909.66 589.91 1,319.75 395,335.23 10,612.52 9 10/1/2016 395,335.23 1,909.66 - 1,909.66 591.88 1,317.78 394,743.35 11,930.30 10 11/1/2016 394,743.35 1,909.66 - 1,909.66 593.85 1,315.81 394,149.50 13,246.11 11 12/1/2016 394,149.50 1,909.66 - 1,909.66 595.83 1,313.83 393,553.67 14,559.94 12 1/1/2017 393,553.67 1,909.66 - 1,909.66 597.82 1,311.85 392,955.85 15,871.79