Step 1: Review the Range Rover Consider the purchase of the latest model of the

ID: 2715646 • Letter: S

Question

Step 1: Review the Range Rover

Consider the purchase of the latest model of the Range Rover (Supercharged) at http://www.landrover.com/us/en/lr/all-new-range-rover/ (Links to an external site.)

The purchase price is $90,000.

The term of the loan is 60 months.

The interest rate is the current prime plus 5%.

The loan requires a 20% down payment.

Step 2: Create an Excel spreadsheet with four tabs

Tab One: Original Scenario

Build an amortization table which shows this loan fully amortized according to the terms listed above. A very helpful video resource can be found here:

http://www.youtube.com/watch?v=Z7Tgy49u8SU&list=UUTPOMEWVghQOMKl8W18Jisw&index=8&feature=plcp (Links to an external site.)
Minimize Video

Tab Two: Increased Monthly Payments

Show the effect of increased monthly payments. Take the original table in Tab One and increase the monthly payment by 50% in the 25th month and forward.

Tab Three: The Effect of Loan Refinancing

Show the effect of refinancing. Take the original table and decrease the interest rate by 3% in the 25th month forward.

Tab Four: Analysis

Write a brief summary and analysis of the effect of the actions taken in Tabs Two and Three. Which action is more beneficial to the purchaser of this vehicle? That is, which action allows the purchaser to pay off the loan faster: a consistent increase in monthly payments under the original terms of the loan, or refinancing the loan with a lower rate?

You will be graded on the accuracy of the three models and the accompanying analysis. Partial credit is available towards the three models. If the analysis is correct but based on incorrect models, then no credit will be given for the analysis. Each section is worth 25% of the total grade.

Step 3: Save and submit your assignment

Explanation / Answer

Down payment = 2% of purchase price = 20% of $90,000 = $18,000

Loan amount = $90,000 - $18,000 = $72,000

Current prime rate = 3.25%

Hence, loan interest rate = 3.25% + 5% = 8.25%

Monthly rate = r = 8.25% / 12 = 0.006875

Term of loan = n = 60 months

Monthly loan payment = (r*loan amount)/ 1 - (1+r)-n = (0.006875*$72,000) / 1 - 1.006875-60

= $495 / 0.3371 = $1468.53

1 Original Scenario

On the basis of above data, loan amortisation schedule can be prepared as below:

Period

Beginning loan

Monthly payment

Interest

Principle

End loan

$72,000.00

$1,468.53

$495.00

$973.53

$71,026.47

$1,468.53

$488.31

$980.22

$70,046.25

$1,468.53

$481.57

$986.96

$69,059.28

$1,468.53

$474.78

$993.75

$68,065.54

$1,468.53

$467.95

$1,000.58

$67,064.96

$1,468.53

$461.07

$1,007.46

$66,057.50

$1,468.53

$454.15

$1,014.38

$65,043.11

$1,468.53

$447.17

$1,021.36

$64,021.76

$1,468.53

$440.15

$1,028.38

$62,993.38

$1,468.53

$433.08

$1,035.45

$61,957.93

$1,468.53

$425.96

$1,042.57

$60,915.36

$1,468.53

$418.79

$1,049.74

$59,865.62

$1,468.53

$411.58

$1,056.95

$58,808.67

$1,468.53

$404.31

$1,064.22

$57,744.44

$1,468.53

$396.99

$1,071.54

$56,672.91

$1,468.53

$389.63

$1,078.90

$55,594.00

$1,468.53

$382.21

$1,086.32

$54,507.68

$1,468.53

$374.74

$1,093.79

$53,413.89

$1,468.53

$367.22

$1,101.31

$52,312.58

$1,468.53

$359.65

$1,108.88

$51,203.70

$1,468.53

$352.03

$1,116.50

$50,087.20

$1,468.53

$344.35

$1,124.18

$48,963.02

$1,468.53

$336.62

$1,131.91

$47,831.11

$1,468.53

$328.84

$1,139.69

$46,691.42

$1,468.53

$321.00

$1,147.53

$45,543.89

$1,468.53

$313.11

$1,155.42

$44,388.48

$1,468.53

$305.17

$1,163.36

$43,225.12

$1,468.53

$297.17

$1,171.36

$42,053.76

$1,468.53

$289.12

$1,179.41

$40,874.35

$1,468.53

$281.01

$1,187.52

$39,686.83

$1,468.53

$272.85

$1,195.68

$38,491.15

$1,468.53

$264.63

$1,203.90

$37,287.24

$1,468.53

$256.35

$1,212.18

$36,075.06

$1,468.53

$248.02

$1,220.51

$34,854.55

$1,468.53

$239.63

$1,228.90

$33,625.64

$1,468.53

$231.18

$1,237.35

$32,388.29

$1,468.53

$222.67

$1,245.86

$31,142.43

$1,468.53

$214.10

$1,254.43

$29,888.00

$1,468.53

$205.48

$1,263.05

$28,624.95

$1,468.53

$196.80

$1,271.73

$27,353.22

$1,468.53

$188.05

$1,280.48

$26,072.74

$1,468.53

$179.25

$1,289.28

$24,783.46

$1,468.53

$170.39

$1,298.14

$23,485.32

$1,468.53

$161.46

$1,307.07

$22,178.25

$1,468.53

$152.48

$1,316.05

$20,862.20

$1,468.53

$143.43

$1,325.10

$19,537.09

$1,468.53

$134.32

$1,334.21

$18,202.88

$1,468.53

$125.14

$1,343.39

$16,859.50

$1,468.53

$115.91

$1,352.62

$15,506.88

$1,468.53

$106.61

$1,361.92

$14,144.96

$1,468.53

$97.25

$1,371.28

$12,773.67

$1,468.53

$87.82

$1,380.71

$11,392.96

$1,468.53

$78.33

$1,390.20

$10,002.76

$1,468.53

$68.77

$1,399.76

$8,603.00

$1,468.53

$59.15

$1,409.38

$7,193.61

$1,468.53

$49.46

$1,419.07

$5,774.54

$1,468.53

$39.70

$1,428.83

$4,345.71

$1,468.53

$29.88

$1,438.65

$2,907.06

$1,468.53

$19.99

$1,448.54

$1,458.51

$1,468.53

$10.03

$1,458.50

$0.01

Total

$88,111.80

$16,111.81

$71,999.99

2 Increased monthly payments

The monthly payment is increased by 50% from 25th month onwards

Increased monthly payment = $1,468.53 * 150% = $2,202.80

End balance of the loan after 24th payment = $46,691.42

Inserting these values in the formula we used for calculating payment, we can find the balance period of the loan

$2,202.80 = (0.006875*$46,691.42) / 1 – 1.006875-n

1 – 1.006875-n = $321 / $2,202.80 = 0.1457

1.006875-n = 1-0.1457 = 0.8543

-n log(1.006875) = log(0.8543)

-n*0.002976 = -0.06839

N = 0.06839 / 0.002976 = 22.98

Hence remaining period of loan after 24th payments is 23 months

Total period of loan = 24+23 = 47 months

Total payment = ($1468.53 * 24 months) + ($2202.80 * 23 months) = $85,909.12

3 The effect of loan refinancing

In case of loan refinancing, interest rate is decreased by 3% from 25th month onward. The monthly payment shall remain same as $1,468.53

Interest rate of loan = 8.25% - 3% = 5.25%

r = 0.0525 / 12 = 0.004375

Following the same process as in part 2, we can find the remaining period of loan as below:

$1,468.53 = (0.004375*$46,691.42) / 1 – 1.004375-n

1 – 1.004375-n = $204.28 / $1,468.53 = 0.1391

1.004375-n = 1 – 0.1391 = 0.8609

-n log(1.004375) = log(0.8609)

n = 0.06505 / 0.001896 = 34.31

Remaining period of loan is about 34 months

Total period of loan = 24 + 34 = 58 months

Total payment = $1468.53 * 58 months = $85,174.74

4 Analysis of the actions

The comparative analysis of the actions taken can be tabulated as below:

Action

Total Amount paid

Term of loan

Original terms

$88,111.80

60 months

Increased monthly payments

$85,909.12

47 months

Refinancing

$85,174.74

58 months

From the above table it can be seen that in case of increased monthly payments, repayment of loan is fastest. Hence it is beneficial to the purchaser of this vehicle.

Period

Beginning loan

Monthly payment

Interest

Principle

End loan

$72,000.00

$1,468.53

$495.00

$973.53

$71,026.47

$1,468.53

$488.31

$980.22

$70,046.25

$1,468.53

$481.57

$986.96

$69,059.28

$1,468.53

$474.78

$993.75

$68,065.54

$1,468.53

$467.95

$1,000.58

$67,064.96

$1,468.53

$461.07

$1,007.46

$66,057.50

$1,468.53

$454.15

$1,014.38

$65,043.11

$1,468.53

$447.17

$1,021.36

$64,021.76

$1,468.53

$440.15

$1,028.38

$62,993.38

$1,468.53

$433.08

$1,035.45

$61,957.93

$1,468.53

$425.96

$1,042.57

$60,915.36

$1,468.53

$418.79

$1,049.74

$59,865.62

$1,468.53

$411.58

$1,056.95

$58,808.67

$1,468.53

$404.31

$1,064.22

$57,744.44

$1,468.53

$396.99

$1,071.54

$56,672.91

$1,468.53

$389.63

$1,078.90

$55,594.00

$1,468.53

$382.21

$1,086.32

$54,507.68

$1,468.53

$374.74

$1,093.79

$53,413.89

$1,468.53

$367.22

$1,101.31

$52,312.58

$1,468.53

$359.65

$1,108.88

$51,203.70

$1,468.53

$352.03

$1,116.50

$50,087.20

$1,468.53

$344.35

$1,124.18

$48,963.02

$1,468.53

$336.62

$1,131.91

$47,831.11

$1,468.53

$328.84

$1,139.69

$46,691.42

$1,468.53

$321.00

$1,147.53

$45,543.89

$1,468.53

$313.11

$1,155.42

$44,388.48

$1,468.53

$305.17

$1,163.36

$43,225.12

$1,468.53

$297.17

$1,171.36

$42,053.76

$1,468.53

$289.12

$1,179.41

$40,874.35

$1,468.53

$281.01

$1,187.52

$39,686.83

$1,468.53

$272.85

$1,195.68

$38,491.15

$1,468.53

$264.63

$1,203.90

$37,287.24

$1,468.53

$256.35

$1,212.18

$36,075.06

$1,468.53

$248.02

$1,220.51

$34,854.55

$1,468.53

$239.63

$1,228.90

$33,625.64

$1,468.53

$231.18

$1,237.35

$32,388.29

$1,468.53

$222.67

$1,245.86

$31,142.43

$1,468.53

$214.10

$1,254.43

$29,888.00

$1,468.53

$205.48

$1,263.05

$28,624.95

$1,468.53

$196.80

$1,271.73

$27,353.22

$1,468.53

$188.05

$1,280.48

$26,072.74

$1,468.53

$179.25

$1,289.28

$24,783.46

$1,468.53

$170.39

$1,298.14

$23,485.32

$1,468.53

$161.46

$1,307.07

$22,178.25

$1,468.53

$152.48

$1,316.05

$20,862.20

$1,468.53

$143.43

$1,325.10

$19,537.09

$1,468.53

$134.32

$1,334.21

$18,202.88

$1,468.53

$125.14

$1,343.39

$16,859.50

$1,468.53

$115.91

$1,352.62

$15,506.88

$1,468.53

$106.61

$1,361.92

$14,144.96

$1,468.53

$97.25

$1,371.28

$12,773.67

$1,468.53

$87.82

$1,380.71

$11,392.96

$1,468.53

$78.33

$1,390.20

$10,002.76

$1,468.53

$68.77

$1,399.76

$8,603.00

$1,468.53

$59.15

$1,409.38

$7,193.61

$1,468.53

$49.46

$1,419.07

$5,774.54

$1,468.53

$39.70

$1,428.83

$4,345.71

$1,468.53

$29.88

$1,438.65

$2,907.06

$1,468.53

$19.99

$1,448.54

$1,458.51

$1,468.53

$10.03

$1,458.50

$0.01

Total

$88,111.80

$16,111.81

$71,999.99

Navigate

Step 1: Researchers conducted a study on each state’s SAT Mathematics score and

Step 1: STATE: What is the practical question that required a statistical test?

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

Step 1: Review the Range Rover Consider the purchase of the latest model of the

Question

Explanation / Answer

Related Questions

Navigate