5.11 Chapter 5 HW - Spreadsheet Mastery // Loan Amortization // Problem 1 Please fill out the tables & list it's formulas. The bolded areas also need to be filled.

A B D H I J K E F G Calculating Loan Payments and Loan Amortization 5 6 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 9 Problem 1: You are planning to purchase a car that costs $30.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 10 years. 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 * PVIFA.. 16 = PMT [1-(1/(1+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 Solution: PV = PMT * PVIFAct 21 30.000 = PMT * [1-(1/(1+.06)*j.06 22 30,000 - PMT * [1-74726].06 23 30,000 = PMT * 4.2124 24 PMT = 30,000/4.2124 25 PMT = 57,121.83 with calculator 26 27 In Words: The yearly payments on a $30,000 loan for 5 years at 6% are $7,121.83 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 % menu button 33 34 Loan Amount -30000 35 Annual Interest Rate 6% 36 Term of Loan in Years 5 37 Monthly Interest Rate Enter monthly Interest Rate: = E35/12 38 Term of Loan in Months Enter nper in months:=E36 *12 39 40 We need the interest rate to agree with the number 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 E43 enter: = E42 + 12 44 Total Amount Paid In cell E44 enter: = E43 * E36 45 Interest Paid Over Term of Loan In cell E45 enter: -E44+E34 (a (+) sign b/c E34 is negative) 46 47 Monthly Payments are calculated by the Excel Wizard Function, or by using the formula 48 for PMT with the inputs (interest, term, principal). Check numbers: Total Amt paid = 49 50 Test Your Skills: 51 What is the monthly payment if you buy a $20,000 car, holding time and rate constant? 52