Question

You are looking to buy a 2018 Ford Focus Titanium Hatchback with sunroof and leather seats at a price of $26,000. Being a college student, you have cash to pay taxes, title, license and fees but your parents offer to give you 10% of the price, $2,600, as a down payment and you need to finance the remainder of $23,400. You smartly researched your finance options and got preapproval at a 3.6% APR with monthly compounding for 5 years on your own so you are not at the mercy of depending on financing from the car dealership. Ford is offering 0% APR financing or $3,250 cash back which would be used as an additional down payment that reduces the amount that you would need to finance. You would use your 3.6% APR pre-approved financing if you elect the $3,250 cash back option. Answer the following questions.

1. What would be your monthly car loan payment under the Ford’s 0% APR financing offer (assume a 60-month loan term)?

2. What would be your monthly car loan payment under the Ford’s $3,250 cash back offer and your 3.6% APR pre-approved financing?

3. At what APR would you be indifferent between the two offers? In other words, at what APR would you have the same monthly payment (assuming a 60-month loan term) for the $3,250 cash back offer as you would with the 0% APR financing offer?

4. Assume you go with the offer in question #2. Construct an amortization schedule for the loan for all 60 monthly payments. What is your loan balance after 36 months?

5. Now assume you were willing to make a monthly car loan payment of $450. Re-do your amortization schedule from question #4 with this monthly payment. How long will it take to pay off the loan with the $450 monthly payment?

Answer 1

Price of car = $26000                                                      

Down payment = $2600                                                

Remainder to be financed or arranged = $23400                                                

                                                               

Option 1. Monthly car loan payment under 0% APR                                                         

                                                               

Total repayment to be made in 60 months at 0% rate assuming 10% paid as down payment = $23400                                                       

Formula to calculate = Total amount/No. of months

                                               Monthly loan installment = $23400/60                                                   

Monthly loan payment amount = $ 390                                                  

                                                               

Option 2 : Monthly car payment if cashback availed                                                         

                                                               

Total repayment to be made in 60 months post cashback and down payment = $26000-$2600-$3250                                                        

Final car loan amount = $20150                                                 

Annual Percentage Rate = 3.6% (compounded monthly)                                               

Formula to calculate monthly car loan payment = [P*R*(1+R)^N]/[(1+R)^N-1]                                                     

Where P=Principal : R=Rate/12 (monthly compounding) : N= Number of months                                                

There fore putting values in formula = [20150*(3.6%/12)*(1+(3.6%/12))^60]/[(1+(3.6%)/12)^60-1]                                                           

Hence Monthly car loan payment amount = $367.4667                                                   

                                                               

Option 3 :Calculate APR to make both offers indifferent                                                

                                                               

Monthly car payment at 0% APR = $390.....refer Option 1                                                              

Monthly car payment at 3.6% APR post cashback = $367.47......refer Option 2                                                      

                                                               

Keeping APR as "R" and putting values in formula with same monthly payment as of option 1                                                       

                                                               

390 =(20150*R*(1+R)^60)/((1+R)^60-1)                                               

                                                               

Solving for R                                                      

                                                               

APR comes out to be 6.05%, Hence at APR of 6.05% both the options would be at indifferent point                                                           

                                                               

Option 4 :Amortization schedule with 3.6% APR                                                

                                                               

Monthly loan payment as calculated in option 2 = 367.4667                                                          

First month interest = P*R = $20150*(3.6%/12) : P= Outstanding Principal                                                             

First month interest = $60.45                                                      

First month principal = Installment-Interest = $367.4667-$60.45 = $ 307.02                                                           

                                                               

Second month outstanding principal = $20150-$307.02 = $19842.98                                                         

Second month interest = P*R = $19842*(3.6%/12) : P= Outstanding Principal                                                       

Second month interest = $59.53                                                

Second month principal = Installment-Interest = $367.4667-$59.53 = $ 307.94                                                     

                                                               

Calculate so on for 60 months to build complete repayment schedule.                                                   

                                                               

Year/Month Wise            Opening Outstanding     Interest Principal               Closing Outstanding

                $20,150.00           $60.45   $307.02                 $19,842.98

                $19,842.98           $59.53   $307.94                 $19,535.05

                $19,535.05           $58.61   $308.86                 $19,226.18

                $19,226.18           $57.68   $309.79                 $18,916.40

                $18,916.40           $56.75   $310.72                 $18,605.68

                $18,605.68           $55.82   $311.65                 $18,294.03

                $18,294.03           $54.88   $312.58                 $17,981.44

                $17,981.44           $53.94   $313.52                 $17,667.92

                $17,667.92           $53.00   $314.46                 $17,353.46

                $17,353.46           $52.06   $315.41                 $17,038.05

                $17,038.05           $51.11   $316.35                 $16,721.70

                $16,721.70           $50.17   $317.30                 $16,404.40

Year #1 End                                                       

                $16,404.40           $49.21   $318.25                 $16,086.14

                $16,086.14           $48.26   $319.21                 $15,766.94

                $15,766.94           $47.30   $320.17                 $15,446.77

                $15,446.77           $46.34   $321.13                 $15,125.64

                $15,125.64           $45.38   $322.09                 $14,803.55

                $14,803.55           $44.41   $323.06                 $14,480.50

                $14,480.50           $43.44   $324.03                 $14,156.47

                $14,156.47           $42.47   $325.00                 $13,831.48

                $13,831.48           $41.49   $325.97                 $13,505.50

                $13,505.50           $40.52   $326.95                 $13,178.55

                $13,178.55           $39.54   $327.93                 $12,850.62

                $12,850.62           $38.55   $328.91                 $12,521.71

Year #2 End                                                       

                $12,521.71           $37.57   $329.90                 $12,191.81

                $12,191.81           $36.58   $330.89                 $11,860.91

                $11,860.91           $35.58   $331.88                 $11,529.03

                $11,529.03           $34.59   $332.88                 $11,196.15

                $11,196.15           $33.59   $333.88                 $10,862.27

                $10,862.27           $32.59   $334.88                 $10,527.39

                $10,527.39           $31.58   $335.88                 $10,191.51

                $10,191.51           $30.57   $336.89                 $9,854.62

                $9,854.62             $29.56   $337.90                 $9,516.71

                $9,516.71             $28.55   $338.92                 $9,177.80

                $9,177.80             $27.53   $339.93                 $8,837.86

                $8,837.86             $26.51   $340.95                 $8,496.91

Year #3 End                                                       

                $8,496.91             $25.49   $341.98                 $8,154.93

                $8,154.93             $24.46   $343.00                 $7,811.93

                $7,811.93             $23.44   $344.03                 $7,467.90

                $7,467.90             $22.40   $345.06                 $7,122.84

                $7,122.84             $21.37   $346.10                 $6,776.74

                $6,776.74             $20.33   $347.14                 $6,429.60

                $6,429.60             $19.29   $348.18                 $6,081.42

                $6,081.42             $18.24   $349.22                 $5,732.20

                $5,732.20             $17.20   $350.27                 $5,381.93

                $5,381.93             $16.15   $351.32                 $5,030.61

                $5,030.61             $15.09   $352.37                 $4,678.24

                $4,678.24             $14.03   $353.43                 $4,324.80

Year #4 End                                                       

                $4,324.80             $12.97   $354.49                 $3,970.31

                $3,970.31             $11.91   $355.56                 $3,614.76

                $3,614.76             $10.84   $356.62                 $3,258.13

                $3,258.13             $9.77     $357.69                 $2,900.44

                $2,900.44             $8.70     $358.77                 $2,541.68

                $2,541.68             $7.63     $359.84                 $2,181.83

                $2,181.83             $6.55     $360.92                 $1,820.91

                $1,820.91             $5.46     $362.00                 $1,458.91

                $1,458.91             $4.38     $363.09                 $1,095.82

                $1,095.82             $3.29     $364.18                 $731.64

                $731.64                 $2.19     $365.27                 $366.37

                $366.37                 $1.10     $366.37                 $0.00

Year #5 End