Question

Loan Amortization Schedule
You purchase a fully loaded Honda Accord with an MSRP of $32,000 for $27,000.
You pay the 3% tax of $810 up front and put down $5,000. The dealer offers
a simple interest installment loan with an annual rate of 5% for 3 years. The projected resale value of the car after 2 years is $17,000.
Compute the loan value and monthly loan payments. (10 pts) Create a monthly amortization schedule over the entire loan period. (10 pts) Compute total interest over the entire loan period. (5 pts) Compute the total cash outflow (tax, plus down payment, plus total monthly loan payments). (8 pts) Compute the amount you would net after selling the car at 2 years. (7 pts) Does the principal portion of the payment increase or decrease as the months progress? (5 pts) Does the interest portion of the payment increase or decrease as the months progress? (5 pts) Please show all calculations and complete monthly amortization schedule.

Answer 1

Loan value = purchase price - down payment = $27,000 - $5,000 = $22,000

Monthly loan payment is calculated using PMT function in Excel :

rate = 5% / 12   (converting annual rate into monthly rate)

nper = 3*12 (3 year loan with 12 monthly payments each year)

pv = 22000 (loan amount)

PMT is calculated to be $659.36

A1 - fac =PMT(5%/12,3*12,22000) D E F B C A ($659.36)! 1

Interest in any month = principal outstanding at beginning of month * 5% / 12

Principal portion of monthly payment = monthly payment minus interest portion of payment

principal outstanding at end of month = principal outstanding at beginning of month minus principal portion of monthly payment

A A B C D E F G H I $659,36 6 7 Principal Principal outstanding outstanding 1 Month Jat beginning Payment Interest Principal at end 1 $ 22,000.00 $ 659.36 $91.67 5567.69 $ 21,432.31 2 $ 21,432.31 $ 659.36 $89.30 $570.06 $ 20,862.25 3 $ 20,862.25 $ 659.36 $ 86.93 $ 572.43 $ 20,289.81 4 $ 20,289.81 $ 659.36 $ 84.54 $ 574.82 $19,715.00 5 $ 19,715.00 $ 659.36 $ 82.15 $577.21 $ 19,137.78 6 $ 19,137.78 $659.36 $79.74 $ 579.62 $ 18,558.16 7 $ 18,558.16 $ 659.36 $77.33 $ 582.03 $ 17,976.13 8 $ 17,976.13 $ 659.36 $74.90 $ 584.46 $ 17,391.67 10 9 $17,391.67 $ 659.36 $72.47 $ 586.89 $ 16,804.78 10 $ 16,804.78 $659.36 $70.02 $589.34 $ 16,215,44 12 11 $ 16,215.44 $659.36 $67.56 $591.80 $ 15,623.64 13 12 $ 15,623.64 $ 659.36 $65.10 $ 594.26 $ 15,029.38 13 $ 15,029.38 $ 659.36 $ 62.62 $596.74 $14,432.64 14 $14,432.64 $ 659.36 $ 60.14 $599.22 $13,833.42 16 15 $ 13,833.42 $ 659.36 $57.64 $ 601.72 $13,231.70 16 $ 13,231.70 $ 659.36 $ 55.13 $604.23 $12,627.47 18 17 $12,627.47 $ 659.36 $ 52.61 $606.75 $12,020.72 19 18 $ 12,020.72 $ 659.36 $ 50.09 $609.27 $11,411.45 19 $ 11,411.45 $ 659.36 $47.55 $611.81 $10,799.64 20 $10,799.64 $ 659.36 $45.00 $614.36 $10,185.28 22 21 $ 10,185.28 $659.36 $42.44 5616.92 5 9,568.36 23 22 5 9,568.36 $ 659.36 $39.87 $619.49 $ 8,948.87 24 23 $ 8,948.87 $ 659.36 $37.29 $622.07 $ 8,326.79 251 24 $ 8,326.79 5659.36 $34.69 5624.66 5 7,702.13 17

AB C D E F G Principal Principal outstanding outstanding 1 Month Jat beginning Payment Interest Principal at end 26 25 $ 7,702.13 $ 659.36 $ 32.09 $627.27 $ 7,074.86 26 $ 7,074.86 $ 659.36 $ 29.48 $ 629.88 $ 6,444.98 27 $ 6,444.98 $ 659.36 $26.85 $ 632.51 $ 5,812.47 28 $ 5,812.47 $ 659.36 $ 24.22 $635.14 $ 5,177.33 29 $ 5,177.33 $ 659.36 $ 21.57 $637.79 $ 4,539.54 30 $ 4,539.54 $ 659.36 $18.91 $ 640.44 $ 3,899.10 31 $ 3,899.10 $ 659.36 $ 16.25 $ 643.11 $ 3,255.99 32 $ 3,255.99 $ 659.36 $13.57 $ 645.79 $ 2,610.19 33 $ 2,610.19 $ 659.36 $10.88 $ 648.48 $ 1,961.71 34 $ 1,961.71 $ 659.36 $ 8.17 5651.19 $ 1,310.52 35 $ 1,310.52 $ 659.36 $ 5.46 $653.90 $ 656.62 36 $ 656.62 $ 659.36 $ 2.74 $656.62 S .

А в с D E F G H =PMT(5%/12,3 12,-22000) N =F2 =F7 N 000 =F9 Principal outstanding 1 Month at beginning Payment 2 1 22000 =$H$2 =$H$2 =F3 =$H$2 =F4 =$H$2 =$H$2 =$H$2 =$H$2 =F8 =$H$2 =$H$2 11 10 =F10 =$H$2 12 11 =F11 =$H$2 13 12 =F12 =$H$2 14 13 =F13 =$H$2 15 14 =F14 =$H$2 16 15 =F15 =$H$2 17 16 =F16 =$H$2 18 17 =F17 =$H$2 =F18 =SH$2 20 19 =F19 =SH$2 21 20 =F20 =SH$2 =F21 =$H$2 23 22 =F22 =$H$2 =F23 =$H$2 25 24 =F24 =$H$2 Interest =B2*5%/12 =B35%/12 =B4.5%/12 =B5.5%/12 =B6*5%/12 =B7*5%/12 =B8*5%/12 =B9.5%/12 =B10*5%/12 =B11*5%/12 =B12.5%/12 =B13*5%/12 =B14.5%/12 =B15*5%/12 =B16*5%/12 =B1705%/12 =B185%/12 =B19*5%/12 =B20*5%/12 =B21*5%/12 =B22*5%/12 =B23*5%/12 =B24*5%/12 =B25*5%/12 Principal =C2-D2 =C3-D3 =C4-D4 =C5-D5 =C6-D6 =C7-07 =C8-D8 =C9-D9 =C10-D10 =C11-D11 =C12-D12 =C13-D13 =C14-D14 =C15-D15 =C16-D16 =C17-D17 =C18-D18 =C19-D19 =C20-D20 =C21-D21 =C22-D22 =C23-D23 =C24-D24 =C25-D25 Principal outstanding at end =B2-E2 =B3-E3 =B4-E4 =B5-E5 =B6-E6 =B7-E7 =B8-E8 =B9-E9 =B10-E10 =B11-E11 =B12-E12 =B13-E13 =B14-E14 =B15-E15 =B16-E16 =B17-E17 =B18-E18 =B19-E19 =B20-E20 =B21-E21 =B22-E22 =B23-E23 =B24-E24 =B25-E25 22 21

4 A B C D E G Principal outstanding at end Principal outstanding 1 Month Jat beginning Payment =F25 =SH$2 =F26 =SH$2 =F27 =$H$2 =$H$2 =$H$2 =$H$2 =F31 =$H$2 =F32 =$H$2 =F33 =$H$2 =F34 =$H$2 =F35 =$H$2 =F36 =SH$2 29 Interest =B26.5%/12 =B27.5%/12 =B28.5%/12 =B29*5%/12 =B30*5%/12 =B31.5%/12 =B32.5%/12 =B335%/12 =B34.5%/12 =B35.5%/12 =B36.5%/12 =B37.5%/12 Principal =C26-D26 =C27-D27 =C28-D28 =C29-D29 =C30-D30 =C31-D31 =C32-D32 =C33-D33 =C34-D34 =C35-D35 =C36-D36 =C37-D37 =B26-E26 =B27-E27 =B28-E28 =B29-E29 =B30-E30 =B31-E31 =B32-E32 =B33-E33 =B34-E34 =B35-E35 =B36-E36 =B37-E37

Total interest = (monthly payment * total number of months) - loan amount

Total interest = ($659.36 * 3 * 12) - $22,000 = $1,736.95

Total cash outflow = tax + down payment + (monthly payment * total number of months)

Total cash outflow = $810 + $5,000 + ($659.36 * 3 * 12) = $29,546.95

The principal portion of the payment increases as the months progress.

The interest portion of the payment decreases as the months progress.