Question

Question 4 of 5 A $85,000 loan was amortized over 14 years at 4.00 % compounded annually. Payments were made at the end of every month to clear the loan. a. What is the size of the payments at the end of every month? $0.00 Round to the nearest cent b. What was the balance at the end of 4 years? $0.00 Round to the nearest cent c.What was the interest portion of payment 84? $0.00 Round to the nearest cent d. What was the principal portion of payment 342 $0.00 Round to the nearest cent e. How much interest was paid in year 7? Round to the nearest cent Next Question MacR

Answer 1

Please refer to below spreadsheet for calculation and answer. Cell reference also provided.

$85,000.00 14 Loan Amount Term in years APR, compounded annually Monthly interest rate No. of monthly payment 4% 0.3274% 168 8 a. Monthly payment size $658.58 9 10 b. Loan balance at end of 4 years $65,267.26 Interest portion of payment 84 $159.75 12 c. 13 14 d. Principal portion of payment 34 $423.63 15 16 e. Interest paid in year 7 $2,023.24

Cell reference -

B Loan Amount Term in years APR, compounded annually Monthly interest rate No. of monthly payment 85000 14 0.04 =(1+C4)^(1/12)-1 =C3*12 8 a. Monthly payment size =PMT(C5,C6,-C2,,0) 9 Loan balance at end of 4 years =PV(C5,C6-4*12),-C8,,0) 10 b. 11 12 c. 13 14 d. Interest portion of payment 84 =IPMT(C5,84,C6,-C2,0) Principal portion of payment 34 =PPMT(C5,34,C6,-C2,,0) 15 e. Interest paid in year 7 =-CUMIPMT(C5,C6,C2,73,84,0) 16 17 18

Hope this will help, please do comment if you need any further explanation. Your feedback would be highly appreciated.