B DE Enter your name in this box F G H ANN L M N 5 6 Tutorial 6 - Calculating Loan Payments and Loan Amortization You will determine the loan payment and amortization schedule fee a fixed rate loan (such as a car loan or a house 7 mortgage) B 9 Problem 1: You are planning to purchase a car that costs $20,000. You can secure a loan from a credit union. The loan officer informed you of the loan terms. The annual percentage rate (APR) is 6%, and the loan must be repaid in 5 years 10 What will be your monthly payments to the credit union? 11 12 PVIFA is used to cakuhate loan payments 13 14 15 Finance Concept: PV - PMT PVIFA. 16 = PMT [1- (1/(1+r) 17 PMT PVI (1-(1/(1+r))} 18 We "invert the formula for PV of an annuity, and solve for payment or cash flows. 19 20 Numerical Solution: PV - PMT PVIFA 21 20,000 - PMT [1-(141+06).06 22 20,000 - PMT [1-7472606 23 20,000 - PMT. 4.2124 24 PMT20,000/4 2124 25 PMT= $4,747.88 with calculator 26 27 In Hors: The yearly payments on a $20,000 loan for 5 years at 6% are $4,747.93 28 by the Wizard The difference is due to rounding errors 29 30 Excel Solution for Monthly Payments: 31 32 Note Interest Rate is formatted in percent by clicking on mer button 33 34 Loan Amount -20000 35 Amal Interest Rate 6% 38 Term of Loan in Years 5 37 Monthly Interest Rate Enter monthly Interest Rate: E35/12 read me Tutorial 6 5 696 ER by the Wizard. The difference is due to rounding errors. 9 O Excel Solution for Monthly Payments: 1 2 Note Interest Rate is formatted in percent by clicking on % menu button 3 Loan Amount -20000 Annual Interest Rate 8 Term of Loan in Years 5 7 Monthly Interest Rate Enter monthly Interest Rate: -E35/12 8 Term of Loan in Months Enter sper in months: E36 *12 9 0 We need the interest rate to agree with the number of periods 1 Solutions 2 Monthly Payment In cell E42 enter: -PAIT(E37,E38 E34), or use PMT Wizard 3 Annual Amount Paid In cell E43 entor: E42 * 12 4 Total Amount Paid In cell E44 enter: = E43 E36 5 Interest Paid Over Term of Loan In cell E45 enter-E44E34 () sign We E34 is negative) 6 7 Monthly Payments are calculated by the Excel Wizard Function, or by using the formula for 8 PMT with the inputs (interest, term. principal) Check numbers: Total Amt paid = $23,199 9 0 Test Your Skills 1 What is the monthly payment if you buy a $30,000 car, hoking time and rate constant? 2 answer is $579,98 3 4 Problem 2: You are planning to purchase a car that costs $20,000. You want to shop around and compare different loan rates and terms before you make a decision. Now you'll want to use a Table to compute your total interest and principal 5 so they can be compared 6 7 Loan Amount -20000 Annual Interest Rate see table Step 1. In cell D6 enter PMTYC64/12 $F$60 SE$57) 69 Term of Loan in Years 5 Watch $ signs. Copy by dragging the cursor 30 Total number of months 60 Excel function: PMT= (rate, term, principal) 31 Divide the interest rate by 12 to convert to monthly 52 With Various Interest Rates: L H N You are planning to purchase a car that costs $20,000. You want to shop around and compare different loan rates and terms before you make a decision. Now you'll want to use a Table to compute your total interest and principal 55 so they can be compared 56 57 Loan Amount -20000 58 Ammad Interest Rate see table Step 1 In cell D64 enter-PMT(C61/12.$E$60, SES57) 59 Term of Loan in Years 5 Watch $ signs. Copy by dragging the cursor. 60 Total manber of months 60 Excel function: PMT= (rate, term, principal) 61 Divide the interest rate by 12 to convert to monthly 62 With Various Interest Rates: 63 Rate PMT Total Interest Interest it Principal 84 2.0% PMT @2% 351 85 3,0% Int+ Prin=21,033 68 40% 67 5.0% 68 60% 69 7.094 70 8.096 71 72 Step 2. Total Interest Column. In E64 enter: D64'60-SES57 and copy down. 73 Step 3. Total amount paid = InterestPrincipal in Glenter: E64-SES57 and copy down. 74 75 With Various Terms (Years): Monthly 78 Years PMT Total Interest Interest - Principal 77 1 Inputs 78 3 rate 0.12 79 5 loan prin-20000 80 7 term in yrs 12 81 9 82 11 PMT= (rate, term. principal) 83 12 (05/12 yrs *12,-20000) 84 85 Step 1. In cell D77 enter: PMT(SKS78/12,SKS80*12.SK$79) and copy down. 86 87 Step 2. In E77 enter: -177 C77 SKSSD-SKS79 88 89 Step 3. In G77 enter =E77-SKS79 90 check members 12 yrs pat 3263, Int+Prin=$37,826 91 Step 4. Copy down cells in table ! x 0 R . B C D E F G H I J K 91 Step 4. Copy down cells in table 92 93 What is your choice of loan rates and terms? 94 95 96 Problem 3 97 You are planning to purchase a car for $20,000. You decide to get the loan from the credit union. It is for 6% amically but you decide to pay it off in 3 years. You want to see the loan amortization table for your loan to see how the 98 interest and principal are being paid off. Fill in the inputs and the table will automatically calculate for you. 99 100 Loan Enter the loan as-20000 101 Monthly PM - You must calculate PMT - (rate/12 term 12. principal 102 Rate annual rate (0.06) 103 years of loan (3) 105 Loan Amortization Table Constant Per 50.00 Principal 0 Term 106 107 Total Monthly PMT 0 Monthly Loan Principal Reduction Beg Principal Balance 0 0 0 0 0 Ending Principal Balance 0 0 0 0.00 0.00 0.00 0 0 0 0 0 0 0 0 0.00 0 0 0 0 109 Month 109 1 110 2 111 3 112 4 113 114 6 115 116 8 117 9 118 10 119 11 120 12 121 13 122 14 123 15 124 16 125 17 128 18 127 19 0 0 0 0 0 0 0 0 0 0 0 0 O 0 0 0 0 0 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 0 0 0 0 0 0 0 0 0 0 0 0 0.00 0 0 0 0 0 0.00 0.00 0.00 0.00 0 0 0 0 0 read me Tutorial DE F H J 0 0 0 0 0 0 0 0 OOOOOOOOom 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 o 0 0 0 0 0 0 0 0 0 A B 118 10 1191 11 120 12 121 13 122 14 123 15 124 16 125 17 126 18 127 19 128 20 129 21 130 22 131 23 132 24 133 25 134 26 135 27 136 28 137 29 138 30 139 31 140 32 141 33 34 143 35 144 36 145 146 Test your skills: 147 148 149 150 151 152 153 154 155 156 G 0.00 0.00 000 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 O 0 0 0 0 0 0 0 0 0 O 0 0 0 0 0 142 0 0 0 0 0 D D 0 0 1. The Loan amortization table contains formulas in their respective cells Keeping the term (years) constant, what is the payment for a $30,000 car at 5% interest? Copy your answer to cell F148 2. Using the table again, what is the exact amount of your last payment if you reduce the years to two? Inputs: loan principal --30000, rate = 5% years=2 3. After copying your answers to questions 1 and 2, reset the inputs in cells D100-D103 The problem states the inputs 157 Note: If you calculate and the cell looks like this: ### it means it needs to be widened. 158 End of Spreadsheet 6. Don't forget to enter your name & peint. o You will determine the loan payment and amortization schedule for a fixed rate loan (such as a car loan or a house 7 mortgage) 8 Problem 1: You are planning to purchase a car that costs $20,000. You can secure a loan from a credit union. The loan officer informed you of the loan terms The annual percentage rate (APR) is 6%, and the loan must be repaid in 5 years, 10 What will be your monthly payments to the credit union? 11 12 PVIFA is used to calculate loan payments. 13 14 15 Finance Concept: PV - PMT + PVIFAA 16 = PMT1-(1/(1+) 17 PMT = PV/([1 (1/(1+r))]/r} 18 We invert" the formula for PV of an annuity, and solve for payment or cash flows 19 20 Numerical Solutions PV = PMT PVIFAX 21 20.000 = PMT + [1-(1/(1+06) 1/06 22 20,000 = PMT [1-.74726]/06 23 20.000 - PMT +42124 24 PMT=20,000/42124 25 PMT + $4,747.88 with calculator 26 27 In Words: The yearly payments on a $20,000 loan for 5 years at 6% are $4,747 93 28 by the Wizard. The difference is due to rounding errors] 29 30 Excel Solution for Monthly Payments: 31 32 Note Interest Rate is formatted in arcunt bunn Monthly Payments: Note Interest Rate is formatted in percent by clicking on % menu button -20000 6% 31 32 33 34 35 36 37 38 39 20 Loan Amount Annual Interest Rate Term of Loan in Years Monthly Interest Rate Term of Loan in Months 5 Enter monthly Interest Rate - E35/12 Enter nper in months =E36 *12 We need the interest rate to agree with the momber of periods 41 Solutions 42 Monthly Payment In cell E42 enter PMT(E37,E38, E34), or use PMT Wizard 43 Annual Amount Paid In cell 43 enter E42 + 12 44 Total Amount Paid In cell 144 enter E43. E36 45 Interest Paid Over Term of Loan In cell E45 enter E44+E34 (a (+) sign We E34 is negative) 46 47 Monthly Payments are calculated by the Excel Wizard Function, or by using the formula for 48 PMT with the inputs (interest, term, principal) Check numbers Total Amt paid = $23,199 49 50 Test Your Skills 51 What is the monthly payment if you buy a $30,000 car, holding time and rate constant? 52 answer is $579 98 53 54 Problem 2: You are planning to purchase a car that costs $20,000. You want to shop around and compare different loan rates and terms before you make a decision. Now you'll want to use a Table to compute your total interest and principal 55 so they can be compared 56 67 You are planning to purchase a car that costs $20,000 You want to shop around and compare different loan rates and terms before you make a decision. Now you'll want to use a Table to compute your total interest and principal so they can be compared Loan Amount -20000 Annual Interest Rate see table Term of Loan in Years 5 Total number of months 60 Step 1 In cell D64 enter =PMT(C64/12 SE$60 $E$57) Watch S signs. Copy by dragging the cursor Excel function PMT= (rate, term, principal) Divide the interest rate by 12 to convert to monthly Total Interest Interest + Principal PMT @ 2% = 351 Int+ Prin = 21,033 With Various Interest Rates: Rate PMT 2.0% 3.0% 4.0% 5.0% 6.0% 70% 8.0% Step 2 Total Interest Column. In E64 enter D64-60-SES57 and copy down. Step 3 Total amount paid = Interest + Principal In G64 enter E64 SE$57 and copy down With VariousTerms (Years): Monthly Years PMT Total Interest Interest + Principal 3 S 7 9 11 Inputs rate 0.12 loan prin 20000 term in yrs 12 PMT (rate term nincinn Stern. In cell DY7 enter: SK878/12,SRS 19) and copy down. 6 7 Step 2 In E77 enter =D77077*SKS80+SKS79 8 9 Step 3. In G77 enter = E77 SK$79 10 check numbers 12 yrs pmt=$263, Int+Prin=$37,826 Step 4. Copy down cells in table 22 23 What is your choice of loan rates and terms? 34 35 36 Problemi 3: 27 You are planning to purchase a car for $20,000 You decide to get the loan from the credit union. It is for 6% annually but you decide to pay it off in 3 years. You want to see the loan amortization table for your loan to see how the 98 interest and principal are being paid off Fill in the inputs and the table will automatically calculate for you. -99 100 Loan Enter the loan as-20000 101 Monthly PMT You must calculate PMT = (rate/12, term*12. principal) 102 Rate annual rate (0.06) 103 years of loan (3) 105 106 Loan Amortization Table Constant PMT $0.00 Principal 107 Terin Beg Principal Balance 0 0 Total Monthly PMT 108 Month 109 110 2 Monthly Loan Interest 0 Principal Reduction 0 Ending Principal Balance 0 0 122 0 0 0 118 10 19 11 120 12 121 13 14 123 15 124 16 125 17 126 18 127 19 128 20 129 21 130 22 131 23 132 24 133 25 134 26 135 27 136 28 137 29 138 30 139 31 140 32 141 33 142 34 143 35 144 36 145 146 Test your skills 147 148 149 150 151 152 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 0 1. The Loan amortization table contains formulas in their respective cells Keeping the term (years) constant, what is the payment for a $30,000 car at 5% interest? Copy your answer to cell F148 2. Using the table again, what is the exact amount of your last payment if you reduce the years to two? Inputs loan principal = 30000, rate = 5%, years = 2 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 0 0 0 0 0 0 137 29 138 30 139 31 140 32 141 33 142 34 143 35 144 36 145 146 Test your skills 147 148 149 150 151 152 153 154 155 156 1. The Loan amortization table contains formulas in their respective cells Keeping the term (years) constant, what is the payment for a $30,000 car at 5% interest? Copy your answer to cell F148 2. Using the table again, what is the exact amount of your last payment if you reduce the years to two? Inputs loan principal = -30000, rate 5% years = 2. 3. After copying your answers to questions 1 and 2, reset the inputs in cells D100-D103 The problem states the inputs. 157 Note: If you calculate and the cell looks like this f****d, it means it needs to be widened. 158 End of Spreadsheet 6. Don't forget to enter your name & Print 159 160 161 162 163 164 B DE Enter your name in this box F G H ANN L M N 5 6 Tutorial 6 - Calculating Loan Payments and Loan Amortization You will determine the loan payment and amortization schedule fee a fixed rate loan (such as a car loan or a house 7 mortgage) B 9 Problem 1: You are planning to purchase a car that costs $20,000. You can secure a loan from a credit union. The loan officer informed you of the loan terms. The annual percentage rate (APR) is 6%, and the loan must be repaid in 5 years 10 What will be your monthly payments to the credit union? 11 12 PVIFA is used to cakuhate loan payments 13 14 15 Finance Concept: PV - PMT PVIFA. 16 = PMT [1- (1/(1+r) 17 PMT PVI (1-(1/(1+r))} 18 We "invert the formula for PV of an annuity, and solve for payment or cash flows. 19 20 Numerical Solution: PV - PMT PVIFA 21 20,000 - PMT [1-(141+06).06 22 20,000 - PMT [1-7472606 23 20,000 - PMT. 4.2124 24 PMT20,000/4 2124 25 PMT= $4,747.88 with calculator 26 27 In Hors: The yearly payments on a $20,000 loan for 5 years at 6% are $4,747.93 28 by the Wizard The difference is due to rounding errors 29 30 Excel Solution for Monthly Payments: 31 32 Note Interest Rate is formatted in percent by clicking on mer button 33 34 Loan Amount -20000 35 Amal Interest Rate 6% 38 Term of Loan in Years 5 37 Monthly Interest Rate Enter monthly Interest Rate: E35/12 read me Tutorial 6 5 696 ER by the Wizard. The difference is due to rounding errors. 9 O Excel Solution for Monthly Payments: 1 2 Note Interest Rate is formatted in percent by clicking on % menu button 3 Loan Amount -20000 Annual Interest Rate 8 Term of Loan in Years 5 7 Monthly Interest Rate Enter monthly Interest Rate: -E35/12 8 Term of Loan in Months Enter sper in months: E36 *12 9 0 We need the interest rate to agree with the number of periods 1 Solutions 2 Monthly Payment In cell E42 enter: -PAIT(E37,E38 E34), or use PMT Wizard 3 Annual Amount Paid In cell E43 entor: E42 * 12 4 Total Amount Paid In cell E44 enter: = E43 E36 5 Interest Paid Over Term of Loan In cell E45 enter-E44E34 () sign We E34 is negative) 6 7 Monthly Payments are calculated by the Excel Wizard Function, or by using the formula for 8 PMT with the inputs (interest, term. principal) Check numbers: Total Amt paid = $23,199 9 0 Test Your Skills 1 What is the monthly payment if you buy a $30,000 car, hoking time and rate constant? 2 answer is $579,98 3 4 Problem 2: You are planning to purchase a car that costs $20,000. You want to shop around and compare different loan rates and terms before you make a decision. Now you'll want to use a Table to compute your total interest and principal 5 so they can be compared 6 7 Loan Amount -20000 Annual Interest Rate see table Step 1. In cell D6 enter PMTYC64/12 $F$60 SE$57) 69 Term of Loan in Years 5 Watch $ signs. Copy by dragging the cursor 30 Total number of months 60 Excel function: PMT= (rate, term, principal) 31 Divide the interest rate by 12 to convert to monthly 52 With Various Interest Rates: L H N You are planning to purchase a car that costs $20,000. You want to shop around and compare different loan rates and terms before you make a decision. Now you'll want to use a Table to compute your total interest and principal 55 so they can be compared 56 57 Loan Amount -20000 58 Ammad Interest Rate see table Step 1 In cell D64 enter-PMT(C61/12.$E$60, SES57) 59 Term of Loan in Years 5 Watch $ signs. Copy by dragging the cursor. 60 Total manber of months 60 Excel function: PMT= (rate, term, principal) 61 Divide the interest rate by 12 to convert to monthly 62 With Various Interest Rates: 63 Rate PMT Total Interest Interest it Principal 84 2.0% PMT @2% 351 85 3,0% Int+ Prin=21,033 68 40% 67 5.0% 68 60% 69 7.094 70 8.096 71 72 Step 2. Total Interest Column. In E64 enter: D64'60-SES57 and copy down. 73 Step 3. Total amount paid = InterestPrincipal in Glenter: E64-SES57 and copy down. 74 75 With Various Terms (Years): Monthly 78 Years PMT Total Interest Interest - Principal 77 1 Inputs 78 3 rate 0.12 79 5 loan prin-20000 80 7 term in yrs 12 81 9 82 11 PMT= (rate, term. principal) 83 12 (05/12 yrs *12,-20000) 84 85 Step 1. In cell D77 enter: PMT(SKS78/12,SKS80*12.SK$79) and copy down. 86 87 Step 2. In E77 enter: -177 C77 SKSSD-SKS79 88 89 Step 3. In G77 enter =E77-SKS79 90 check members 12 yrs pat 3263, Int+Prin=$37,826 91 Step 4. Copy down cells in table ! x 0 R . B C D E F G H I J K 91 Step 4. Copy down cells in table 92 93 What is your choice of loan rates and terms? 94 95 96 Problem 3 97 You are planning to purchase a car for $20,000. You decide to get the loan from the credit union. It is for 6% amically but you decide to pay it off in 3 years. You want to see the loan amortization table for your loan to see how the 98 interest and principal are being paid off. Fill in the inputs and the table will automatically calculate for you. 99 100 Loan Enter the loan as-20000 101 Monthly PM - You must calculate PMT - (rate/12 term 12. principal 102 Rate annual rate (0.06) 103 years of loan (3) 105 Loan Amortization Table Constant Per 50.00 Principal 0 Term 106 107 Total Monthly PMT 0 Monthly Loan Principal Reduction Beg Principal Balance 0 0 0 0 0 Ending Principal Balance 0 0 0 0.00 0.00 0.00 0 0 0 0 0 0 0 0 0.00 0 0 0 0 109 Month 109 1 110 2 111 3 112 4 113 114 6 115 116 8 117 9 118 10 119 11 120 12 121 13 122 14 123 15 124 16 125 17 128 18 127 19 0 0 0 0 0 0 0 0 0 0 0 0 O 0 0 0 0 0 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 0 0 0 0 0 0 0 0 0 0 0 0 0.00 0 0 0 0 0 0.00 0.00 0.00 0.00 0 0 0 0 0 read me Tutorial DE F H J 0 0 0 0 0 0 0 0 OOOOOOOOom 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 o 0 0 0 0 0 0 0 0 0 A B 118 10 1191 11 120 12 121 13 122 14 123 15 124 16 125 17 126 18 127 19 128 20 129 21 130 22 131 23 132 24 133 25 134 26 135 27 136 28 137 29 138 30 139 31 140 32 141 33 34 143 35 144 36 145 146 Test your skills: 147 148 149 150 151 152 153 154 155 156 G 0.00 0.00 000 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 O 0 0 0 0 0 0 0 0 0 O 0 0 0 0 0 142 0 0 0 0 0 D D 0 0 1. The Loan amortization table contains formulas in their respective cells Keeping the term (years) constant, what is the payment for a $30,000 car at 5% interest? Copy your answer to cell F148 2. Using the table again, what is the exact amount of your last payment if you reduce the years to two? Inputs: loan principal --30000, rate = 5% years=2 3. After copying your answers to questions 1 and 2, reset the inputs in cells D100-D103 The problem states the inputs 157 Note: If you calculate and the cell looks like this: ### it means it needs to be widened. 158 End of Spreadsheet 6. Don't forget to enter your name & peint. o You will determine the loan payment and amortization schedule for a fixed rate loan (such as a car loan or a house 7 mortgage) 8 Problem 1: You are planning to purchase a car that costs $20,000. You can secure a loan from a credit union. The loan officer informed you of the loan terms The annual percentage rate (APR) is 6%, and the loan must be repaid in 5 years, 10 What will be your monthly payments to the credit union? 11 12 PVIFA is used to calculate loan payments. 13 14 15 Finance Concept: PV - PMT + PVIFAA 16 = PMT1-(1/(1+) 17 PMT = PV/([1 (1/(1+r))]/r} 18 We invert" the formula for PV of an annuity, and solve for payment or cash flows 19 20 Numerical Solutions PV = PMT PVIFAX 21 20.000 = PMT + [1-(1/(1+06) 1/06 22 20,000 = PMT [1-.74726]/06 23 20.000 - PMT +42124 24 PMT=20,000/42124 25 PMT + $4,747.88 with calculator 26 27 In Words: The yearly payments on a $20,000 loan for 5 years at 6% are $4,747 93 28 by the Wizard. The difference is due to rounding errors] 29 30 Excel Solution for Monthly Payments: 31 32 Note Interest Rate is formatted in arcunt bunn Monthly Payments: Note Interest Rate is formatted in percent by clicking on % menu button -20000 6% 31 32 33 34 35 36 37 38 39 20 Loan Amount Annual Interest Rate Term of Loan in Years Monthly Interest Rate Term of Loan in Months 5 Enter monthly Interest Rate - E35/12 Enter nper in months =E36 *12 We need the interest rate to agree with the momber of periods 41 Solutions 42 Monthly Payment In cell E42 enter PMT(E37,E38, E34), or use PMT Wizard 43 Annual Amount Paid In cell 43 enter E42 + 12 44 Total Amount Paid In cell 144 enter E43. E36 45 Interest Paid Over Term of Loan In cell E45 enter E44+E34 (a (+) sign We E34 is negative) 46 47 Monthly Payments are calculated by the Excel Wizard Function, or by using the formula for 48 PMT with the inputs (interest, term, principal) Check numbers Total Amt paid = $23,199 49 50 Test Your Skills 51 What is the monthly payment if you buy a $30,000 car, holding time and rate constant? 52 answer is $579 98 53 54 Problem 2: You are planning to purchase a car that costs $20,000. You want to shop around and compare different loan rates and terms before you make a decision. Now you'll want to use a Table to compute your total interest and principal 55 so they can be compared 56 67 You are planning to purchase a car that costs $20,000 You want to shop around and compare different loan rates and terms before you make a decision. Now you'll want to use a Table to compute your total interest and principal so they can be compared Loan Amount -20000 Annual Interest Rate see table Term of Loan in Years 5 Total number of months 60 Step 1 In cell D64 enter =PMT(C64/12 SE$60 $E$57) Watch S signs. Copy by dragging the cursor Excel function PMT= (rate, term, principal) Divide the interest rate by 12 to convert to monthly Total Interest Interest + Principal PMT @ 2% = 351 Int+ Prin = 21,033 With Various Interest Rates: Rate PMT 2.0% 3.0% 4.0% 5.0% 6.0% 70% 8.0% Step 2 Total Interest Column. In E64 enter D64-60-SES57 and copy down. Step 3 Total amount paid = Interest + Principal In G64 enter E64 SE$57 and copy down With VariousTerms (Years): Monthly Years PMT Total Interest Interest + Principal 3 S 7 9 11 Inputs rate 0.12 loan prin 20000 term in yrs 12 PMT (rate term nincinn Stern. In cell DY7 enter: SK878/12,SRS 19) and copy down. 6 7 Step 2 In E77 enter =D77077*SKS80+SKS79 8 9 Step 3. In G77 enter = E77 SK$79 10 check numbers 12 yrs pmt=$263, Int+Prin=$37,826 Step 4. Copy down cells in table 22 23 What is your choice of loan rates and terms? 34 35 36 Problemi 3: 27 You are planning to purchase a car for $20,000 You decide to get the loan from the credit union. It is for 6% annually but you decide to pay it off in 3 years. You want to see the loan amortization table for your loan to see how the 98 interest and principal are being paid off Fill in the inputs and the table will automatically calculate for you. -99 100 Loan Enter the loan as-20000 101 Monthly PMT You must calculate PMT = (rate/12, term*12. principal) 102 Rate annual rate (0.06) 103 years of loan (3) 105 106 Loan Amortization Table Constant PMT $0.00 Principal 107 Terin Beg Principal Balance 0 0 Total Monthly PMT 108 Month 109 110 2 Monthly Loan Interest 0 Principal Reduction 0 Ending Principal Balance 0 0 122 0 0 0 118 10 19 11 120 12 121 13 14 123 15 124 16 125 17 126 18 127 19 128 20 129 21 130 22 131 23 132 24 133 25 134 26 135 27 136 28 137 29 138 30 139 31 140 32 141 33 142 34 143 35 144 36 145 146 Test your skills 147 148 149 150 151 152 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 0 1. The Loan amortization table contains formulas in their respective cells Keeping the term (years) constant, what is the payment for a $30,000 car at 5% interest? Copy your answer to cell F148 2. Using the table again, what is the exact amount of your last payment if you reduce the years to two? Inputs loan principal = 30000, rate = 5%, years = 2 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0 0 0 0 0 0 0 137 29 138 30 139 31 140 32 141 33 142 34 143 35 144 36 145 146 Test your skills 147 148 149 150 151 152 153 154 155 156 1. The Loan amortization table contains formulas in their respective cells Keeping the term (years) constant, what is the payment for a $30,000 car at 5% interest? Copy your answer to cell F148 2. Using the table again, what is the exact amount of your last payment if you reduce the years to two? Inputs loan principal = -30000, rate 5% years = 2. 3. After copying your answers to questions 1 and 2, reset the inputs in cells D100-D103 The problem states the inputs. 157 Note: If you calculate and the cell looks like this f****d, it means it needs to be widened. 158 End of Spreadsheet 6. Don't forget to enter your name & Print 159 160 161 162 163 164