One of your work colleagues has approached you for help with completing a spreadsheet model that they are constructing to determine whether they should sell their existing motor vehicle to NLC and then to enter into a notated lease arrangement with NLC NLC have offered them $36,000 for their car with the residual payable to NCL at the end of the 3 year lease being $16,335. The budget annual out of pocket expenses from the lease will be $13,500. If they decide to retain ownership of the vehicle their annual out of pocket's vehicle expenses are $6,500  Your colleagues has decided that they should use a rate of 4% which is equivalent to their home mortgage rate as their cost of capital for the lease decision:
A)Suggest the formula behind cellB26 that calculated the net present value?
B) suggest the formula behind cellB32 to implement the decision (to lease or not to lease)?

A spreadsheet model is shown in Figure 1. C D E A B 1 Vehicle Funding Decision (to-Lease OR not-to-lease) 2 3 DATA SECTION 4 5 Lease option 6 Proceeds from sale of vehicle 7 Annual reduction in take-home pay 8 Residual payment at end of lease 9 10 Non-lease annual 'out-of-pocket' expenses: 11 Fuel 12 Maintenance & Tyres 13 Insurance 14 Registration 15 16 Cost of capital (Discount rate) 17 18 REPORT SECTION 19 20 Option 1 (Leasing) 21 36,000 13,500 16,335 4,000 1,200 550 750 4% Year -$15,985.48 22 Cash for Car 23 Annual reduction in take-home pay 24 Residual 25 Net Cash Flow 26 NPV 27 28 Option 2 (Current position - not to lease) 29 30 Total Annual Running Costs 31 NPV 32 Decision: Figure 1. Vehicle Novated Lease spreadsheet model. Year 0 36,000 -$18,038.09 Lease 1 0 36,000 -13,500 F -13,500 -13,500 1 -6,500 2 -13,500 26 3 -13,500 -16,335 -29,835 -6,500 -6,500