You take out a 25 year mortgage of $ 111,700 that has an interest rate of 5.47 %
ID: 2942891 • Letter: Y
Question
You take out a 25 year mortgage of $ 111,700 that has an interest rate of 5.47 % (compounded monthly)A. What are your monthly payments ? show how you arrived at your answer
B. Find how much of the first monthly payment in the 16 th year goes towards reducing the principal. show the work needed to arrive at the answer.
C. How much interest do you pay the bank during the nineteenth year of the loan ? . show how you arrived at your answer
Explanation / Answer
At the end of each month the balance is multiplied by the interest and the payment is subtracted from this product. The monthly interest is 5.47%/12months/year = .4558% = .4558/100 = .004558 At the end of the first month the amount owed is $111,700*(1.004558) - monthly payment At the end of the second month, the amount owed is ($111,700*(1.004558) - monthly payment)*(1.004558) - monthly payment At the end of the third month, the amount owed is (($111,700*(1.004558) - monthly payment)*(1.004558) - monthly payment)*(1.004558) - monthly payment. Microsoft Excel is perfect for this since we need to make 300 payments Each month we take the amount owed the month before and multiply by the monthly interest and then subtract the monthly payment. We can easily do this with two columns. The left is how much we owe at the beginning of the month, and the right is that value multiplied by the interest less the monthly payment. The contents of this cell is placed into the next cell down in the left hand column. Start of month End of month 1>111700.0000 111525.2291 2>111525.2291 111349.6616 3>111349.6616 111173.2937 4>111173.2937 110996.1219 =A4*1.0045583-$C$1 is what the formula looks like in my spreadsheet. The contents of $C$1 are what I vary to get the amount owed at the end of the 300th month very close to zero. Since the cell has the $letter$number, it does not move when I drag down the formula and I can use it in all 300 calculations and see what the result is when I try different numbers without re-typing any formulas. I copy the contents of the bottom cell and paste them up at the top, so I can see the amount owed right next to the cell for the monthly payment. Here are the top two rows of my spreadsheet. The 0.197941889 is the amount owed when I pay $683.933 each month 11170 is amount owed at beginning of the first month. 111525.2291 is amount owed at end of first month. 683.933 is the number I vary to get to zero at the 300th payment. 5.47 is the interest. 0.0045833 is the monthly interest in decimals. 300 is the number of payments. 111525.2291 is how much I owe at the beginning of the second month. 111349.6616 is how much I owe at the end of the second month. $0.19 is how much I owe after the 300th payment. 111700 111525.2291 683.933 111700 5.47 0.004558333 300 111525.2291 111349.6616 0.197941889 If I pay $683.94 each month, my last payment will be $4.28 less than my other payments. If I pay $683.93 each month, my last payment will be $2.12 more than my other payments. I arrived at these numbers by trial and error. (B) To find out how much of the first payment of the 16th year goes to reducing the principal, I had the spreadsheet calculate all of the amounts due and payments Here are the numbers for the last payment of the 15th year, and the first two for the start of the 16th year (15*12 = 180) 180 63499.21548 63104.72395 181 63104.72395 62708.43422 182 62708.43422 62310.33807 If we subtract the amount owed at the end of the month from the amount owed at the beginning of the month, we find how much we are paying down the loan. This is with $683.94 monthly payments Principal.....Interest 394.4915261 289.4484739 396.2897368 287.6502632 398.0961443 285.8438557 For the first month of the sixteenth year, we have $63,104.72- $62,708.43 = $396.29 reduction in principal. we have $683.94 - $396.29 = $287.65 in interest (C) in the nineteenth year we find how much we owe on the loan in the beginning of the year and subtract how much we owe at the end of the year. We subtract this difference from how much we paid to find how much was interest. 18*12 = 216, so the 19th year starts after the 216th payment. 217 47639.18791 47172.40162 218 47172.40162 46703.48758 219 46703.48758 46232.43609 220 46232.43609 45759.2374 221 45759.2374 45283.88174 222 45283.88174 44806.35925 223 44806.35925 44326.66008 224 44326.66008 43844.7743 225 43844.7743 43360.69193 226 43360.69193 42874.40297 227 42874.40297 42385.89736 228 42385.89736 41895.165 On the first day we owe $47,639.19. After our final payment of the 19th year we owe $41,895.17. We have paid down the debt by $5,744.02. We have made 12 payments of $683.94 for a total of $8,207.28 $8,207.28 - $5,744.02 = $2463.26 We paid $2,463.26 in interest in the nineteenth year
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.