Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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
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)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
Step by Step Solution
★★★★★
3.47 Rating (147 Votes )
There are 3 Steps involved in it
Step: 1
A The formula behind cell B26 that calculates the net present value NPV can be derived using the ...Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started