You are considering purchasing a car with a sticker price of $36,270 (non negotiable with no down payment required). You wish to make monthly payments for six years and the most that you would like to pay is $600 a month. Your local bank/credit union has agreed to loan you the money at a 4.29% annual interest rate. Create an amortization table reporting the beginning/ending loan balance, total payment, the portion of payment going to interest and principal, and remaining loan balance. Create a IF statement that answers the question of whether you can afford the loan. What is your monthly loan payment and what is the total interest paid on the loan

1. Amortization Table:

Amortization Schedule

 Beginning BalanceInterestPrincipalEnding Balance

1$36,270.00$129.67$442.59$35,827.41

2$35,827.41$128.08$444.17$35,383.24

3$35,383.24$126.50$445.76$34,937.48

4$34,937.48$124.90$447.35$34,490.12

5$34,490.12$123.30$448.95$34,041.17

6$34,041.17$121.70$450.56$33,590.61

7$33,590.61$120.09$452.17$33,138.44

8$33,138.44$118.47$453.79$32,684.65

9$32,684.65$116.85$455.41$32,229.25

10$32,229.25$115.22$457.04$31,772.21

11$31,772.21$113.59$458.67$31,313.54

12$31,313.54$111.95$460.31$30,853.23

Year #1 End

13$30,853.23$110.30$461.96$30,391.27

14$30,391.27$108.65$463.61$29,927.67

15$29,927.67$106.99$465.26$29,462.40

16$29,462.40$105.33$466.93$28,995.47

17$28,995.47$103.66$468.60$28,526.88

18$28,526.88$101.98$470.27$28,056.60

19$28,056.60$100.30$471.95$27,584.65

20$27,584.65$98.62$473.64$27,111.01

21$27,111.01$96.92$475.33$26,635.68

22$26,635.68$95.22$477.03$26,158.64

23$26,158.64$93.52$478.74$25,679.90

24$25,679.90$91.81$480.45$25,199.45

Year #2 End

25$25,199.45$90.09$482.17$24,717.29

26$24,717.29$88.36$483.89$24,233.40

27$24,233.40$86.63$485.62$23,747.77

28$23,747.77$84.90$487.36$23,260.42

29$23,260.42$83.16$489.10$22,771.32

30$22,771.32$81.41$490.85$22,280.47

31$22,280.47$79.65$492.60$21,787.86

32$21,787.86$77.89$494.36$21,293.50

33$21,293.50$76.12$496.13$20,797.37

34$20,797.37$74.35$497.91$20,299.46

35$20,299.46$72.57$499.69$19,799.78

36$19,799.78$70.78$501.47$19,298.31

Year #3 End

37$19,298.31$68.99$503.26$18,795.04

38$18,795.04$67.19$505.06$18,289.98

39$18,289.98$65.39$506.87$17,783.11

40$17,783.11$63.57$508.68$17,274.43

41$17,274.43$61.76$510.50$16,763.93

42$16,763.93$59.93$512.32$16,251.60

43$16,251.60$58.10$514.16$15,737.45

44$15,737.45$56.26$515.99$15,221.45

45$15,221.45$54.42$517.84$14,703.61

46$14,703.61$52.57$519.69$14,183.92

47$14,183.92$50.71$521.55$13,662.37

48$13,662.37$48.84$523.41$13,138.96

Year #4 End

49$13,138.96$46.97$525.28$12,613.68

50$12,613.68$45.09$527.16$12,086.52

51$12,086.52$43.21$529.05$11,557.47

52$11,557.47$41.32$530.94$11,026.53

53$11,026.53$39.42$532.84$10,493.70

54$10,493.70$37.51$534.74$9,958.95

55$9,958.95$35.60$536.65$9,422.30

56$9,422.30$33.68$538.57$8,883.73

57$8,883.73$31.76$540.50$8,343.23

58$8,343.23$29.83$542.43$7,800.81

59$7,800.81$27.89$544.37$7,256.44

60$7,256.44$25.94$546.31$6,710.12

Year #5 End

61$6,710.12$23.99$548.27$6,161.86

62$6,161.86$22.03$550.23$5,611.63

63$5,611.63$20.06$552.19$5,059.43

64$5,059.43$18.09$554.17$4,505.27

65$4,505.27$16.11$556.15$3,949.12

66$3,949.12$14.12$558.14$3,390.98

67$3,390.98$12.12$560.13$2,830.85

68$2,830.85$10.12$562.14$2,268.71

69$2,268.71$8.11$564.15$1,704.57

70$1,704.57$6.09$566.16$1,138.40

71$1,138.40$4.07$568.19$570.22

72$570.22$2.04$570.22$0.00

Year #6 End

2. IF monthly payment is <$600, then take the loan.

3. The monthly payment is $572.26

4. The total interest paid on the loan is $4,932.42.

Explanation:

a) Data and Calculations:

Car loan = $36,270

Expected payment per month = $600

Loan interest rate = 4.29%

Period of payments = 6 years or 72 months

Results:

Payment Every Month   $572.26

Total of 72 Payments   $41,202.42

Total Interest   $4,932.42