Answered step by step
Verified Expert Solution
Link Copied!

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 spreadsheet model is shown in Figure 1. C D E A B 1 Vehicle Funding Decision (to-Lease OR not-to-lease) 2 3

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 ... blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Financial Accounting A User Perspective

Authors: Robert E Hoskin, Maureen R Fizzell, Donald C Cherry

6th Canadian Edition

470676604, 978-0470676608

More Books

Students also viewed these Accounting questions