Question

5 X FILE HOME INSERT X Arial Amortization with equal payments - Excel PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In 12 - A A % Alignment Number Conditional Format as Cell Cells Editing Formatting Table Styles Styles fx Prepare an amortization schedule for a five-year loan of $67,500. The interest Paste B 1 U Clipboard Font C3 HE с D E G H 1 2 2 3 Prepare an amortization schedule for a five-year loan of $67,500. The interest rate is 7 percent per year, and the loan calls for equal annual payments. How much total interest is paid over the life of the loan? 4

MUST SHOW ALL CORRECT EXCEL FORMULAS

Answer 1

AB C E F G H I 4 5 6 Loan amount Interest rate 67,500 7% 7 8 Loan term 5 9 10 11 12 13 Loan payment $16,462.62 14 15 16 17 Year Beginning balance 1 $ 67,500.00 2 $ 55,762.38 3 $ 43,203.12 4 $ 29,764.72 5 $ 15,385.63 Total payment Interest paid $16,462.62 $ 4,725.00 $16,462.62 $ 3,903.37 $16,462.62 $ 3,024.22 $16,462.62 $ 2,083.53 $16,462.62 $ 1,076.99 Principal paid Ending balance $11,737.62 $ 55,762.38 $12,559.26 $ 43,203.12 $13,438.40 29,764.72 $14,379.09 $ 15,385.63 $15,385.63 $ 18 19 20 21 22 Total interest paid $ 14,813.11 23 24 25 26

Excel formulas:

A B с D E F G I I 4 5 09 Loan amount Interest rate Loan term 67500 0.07 7 5 8 9 10 11 12 13 Loan payment --PMT(D7,D8, D6) 14 15 Year Beginning balance 16 1 =D6 Total payment =D13 =F16 =F17 Interest paid =D16*D7 =D17*D7 =D18*D7 17 2 =116 Principal paid |=F16-G16 =F17-G17 =F18-G18 =F19-619 Ending balance D16-H16 =D17-H17 D18-H18 =D19-H19 18 3 =117 19 4 =118 =F18 =F19 1=D19%D7 =D20*D7 20 5 =119 I=F20-G20 =D20-H20 21 22 Total interest paid =SUM(G16:20) 23 24