Question

For each of these problems, please use first a mathematical formula to solve the problem. Second use Excel spreadsheet to also solve the problem.

You are thinking about leasing a car. The purchase price of the car is $30,000. The residual value (the amount you could pay to keep the car at the end of the lease) is $15,000 at the end of 36 months. Assume the first lease payment is due one month after you get the car. The interest rate implicit in the lease is 6% APR, compounded monthly. What will your lease payments be for a 36-month lease?

Answer 1

The present value of series of lease payment, PV = the purchase price of the car – Present value of residual value at the end of 36 months at 0.5% monthly interest rate

= $30,000 - $15,000/ (1+0.5%) ^36

= $30,000 - $12,534.67

= $17,465.33

The monthly amount of lease payment can be calculated with the help of PV of an Annuity formula

PV = PMT * [1-(1+i) ^-n)]/i

Where,

The present value of series of lease payment, PV = $17,465.33

PMT = Monthly payment =?

Annual interest rate = 6% per year, therefore monthly interest i = 6%/12 = 0.5% per month

n = N = number of payments = 36 months

Therefore,

$17,465.33= PMT* [1- (1+0.005)^-36]/0.005

PMT = $531.33

Therefore lease payments for 36 months will be $531.33 per month

Calculation In excel:

Price of car =	$30,000
FV of lease payment =	$15,000.0
PV of lease payment =	$17,465.3
Monthly payment =	$531.33
Interest rate (annual) =	6.00%
Interest rate (monthly) =	0.50%
Time period	36 months
Amortization Schedule
Months	Interest Payment @ 0.5% of new balance of previous period	Monthly lease Payment	Ending Balance (Previous balance + Interest - monthly Payment)
0	$0.00	$0.00	$30,000.00
1	$150.00	$531.33	$29,618.67
2	$148.09	$531.33	$29,235.44
3	$146.18	$531.33	$28,850.28
4	$144.25	$531.33	$28,463.21
5	$142.32	$531.33	$28,074.19
6	$140.37	$531.33	$27,683.23
7	$138.42	$531.33	$27,290.32
8	$136.45	$531.33	$26,895.44
9	$134.48	$531.33	$26,498.59
10	$132.49	$531.33	$26,099.76
11	$130.50	$531.33	$25,698.93
12	$128.49	$531.33	$25,296.09
13	$126.48	$531.33	$24,891.24
14	$124.46	$531.33	$24,484.37
15	$122.42	$531.33	$24,075.46
16	$120.38	$531.33	$23,664.51
17	$118.32	$531.33	$23,251.50
18	$116.26	$531.33	$22,836.43
19	$114.18	$531.33	$22,419.29
20	$112.10	$531.33	$22,000.05
21	$110.00	$531.33	$21,578.72
22	$107.89	$531.33	$21,155.29
23	$105.78	$531.33	$20,729.74
24	$103.65	$531.33	$20,302.06
25	$101.51	$531.33	$19,872.24
26	$99.36	$531.33	$19,440.27
27	$97.20	$531.33	$19,006.14
28	$95.03	$531.33	$18,569.84
29	$92.85	$531.33	$18,131.36
30	$90.66	$531.33	$17,690.69
31	$88.45	$531.33	$17,247.82
32	$86.24	$531.33	$16,802.73
33	$84.01	$531.33	$16,355.41
34	$81.78	$531.33	$15,905.86
35	$79.53	$531.33	$15,454.06
36	$77.27	$531.33	$15,000.00