Financial Excel Spreadsheets

Sheet1 This spreadsheet analyzes the lease/purchase decision. In the case of a purchase, it is assumed sales tax is paid on the negotiated price at the time of purchase, and property taxes are paid annually. In the case of a lease, it is assumed sales tax is paid on the difference between the list price and the payoff at the time of purchase, sales tax is paid on the payoff when the lease expires, and property taxes are paid by the lessor. This spreadsheet assumes the item is purchased at the payoff price at the time the lease expires. The lease has additional value to the lessee because of the option not to purchase. The features of your lease may be different than assumed here. Assumptions List price 26,000 $ Negotiated price 24,000 $ Lease period (months) 36 Payoff 20,000 $ Interest rate (APR) 9.00% Effective annual rate 9.38% Sales tax rate 6% Property tax (annual) 300 $ Lease payment 280 $ Results Purchase Lease Present value of payments 24,000 $ 24,154 $ Present value of sales tax 1,440 $ 1,277 $ Present value of property tax 754 $ Total 26,194 $ 25,431 $ The following table provides a check on the lease calculations above. Sales PV Cumul Month Payment Tax Total Factor PV PV 0 280 $ 360 640 $ 100.0% 640.00 640.00 1 280 $ 280 $ 99.3% 277.92 917.92 2 280 $ 280 $ 98.5% 275.85 1193.76 3 280 $ 280 $ 97.8% 273.79 1467.56 4 280 $ 280 $ 97.1% 271.76 1739.31 5 280 $ 280 $ 96.3% 269.73 2009.04 6 280 $ 280 $ 95.6% 267.72 2276.77 7 280 $ 280 $ 94.9% 265.73 2542.50 8 280 $ 280 $ 94.2% 263.75 2806.25 9 280 $ 280 $ 93.5% 261.79 3068.04 10 280 $ 280 $ 92.8% 259.84 3327.88 11 280 $ 280 $ 92.1% 257.91 3585.79 12 280 $ 280 $ 91.4% 255.99 3841.78 13 280 $ 280 $ 90.7% 254.08 4095.86 14 280 $ 280 $ 90.1% 252.19 4348.05 15 280 $ 280 $ 89.4% 250.31 4598.36 16 280 $ 280 $ 88.7% 248.45 4846.81 17 280 $ 280 $ 88.1% 246.60 5093.41 Page 1Sheet1 18 280 $ 280 $ 87.4% 244.76 5338.17 19 280 $ 280 $ 86.8% 242.94 5581.11 20 280 $ 280 $ 86.1% 241.13 5822.25 21 280 $ 280 $ 85.5% 239.34 6061.58 22 280 $ 280 $ 84.8% 237.56 6299.14 23 280 $ 280 $ 84.2% 235.79 6534.93 24 280 $ 280 $ 83.6% 234.03 6768.96 25 280 $ 280 $ 83.0% 232.29 7001.25 26 280 $ 280 $ 82.3% 230.56 7231.81 27 280 $ 280 $ 81.7% 228.85 7460.66 28 280 $ 280 $ 81.1% 227.14 7687.80 29 280 $ 280 $ 80.5% 225.45 7913.25 30 280 $ 280 $ 79.9% 223.77 8137.02 31 280 $ 280 $ 79.3% 222.11 8359.13 32 280 $ 280 $ 78.7% 220.45 8579.58 33 280 $ 280 $ 78.1% 218.81 8798.39 34 280 $ 280 $ 77.6% 217.18 9015.58 35 280 $ 280 $ 77.0% 215.57 9231.14 36 20,000 $ 1,200 $ 21,200 $ 76.4% 16199.96 25431.10 Page 2Sheet1 property taxes Page 3