In: Finance
Calculations must be done in Excel
As the financial advisor to Upmarket Car Rentals you are evaluating the following types of cars to add to the fleet: -
Speedster: - A sporty convertible with a cost of $120,000 and a useful life of 4 years. It will produce rental income of $80,000 per year and operating costs of $15,000 per year. A major service is required after 2 years costing $20,000. A salvage value of $30,000 is expected after 4 years. The required return is 8%.
Cruncher: - A rugged off-road vehicle costing $180,000 but with an expected useful life of only 2 years, due to the harsh conditions. It will produce rental income of $150,000 per year and operating costs of $20,000 per year. A major service is required after 1 years costing $30,000. A salvage value of $45,000 is expected after 2 years. The required rate of return is 10%.
Income tax can be ignored.
Required
Please provide both the Excel calculation + explanation of how the answer was derived. Thank you.
NPV computation
Speedster
Cost |
120,000.00 |
Useful life |
4 years |
Rental income |
80,000.00 |
Operating costs |
15,000.00 |
Service cost after 2 years |
20,000.00 |
Salvage value |
30,000.00 |
Cashflow table
Year |
0 |
1 |
2 |
3 |
4 |
Initial cost |
(120,000.00) |
||||
Total Initial cashflows |
(120,000.00) |
||||
Rental income |
80,000.00 |
80,000.00 |
80,000.00 |
80,000.00 |
|
Less : Operating costs |
15,000.00 |
15,000.00 |
15,000.00 |
15,000.00 |
|
Less : Service at the end of 2 years |
20,000.00 |
||||
Total operating cashflows |
65,000.00 |
45,000.00 |
65,000.00 |
65,000.00 |
|
Terminal cashflows |
|||||
Salvage value |
30,000.00 |
||||
Net cashflows (Initial + Operating + Terminal) |
(120,000.00) |
65,000.00 |
45,000.00 |
65,000.00 |
95,000.00 |
PV factor @ 8% ---> 1/ (1+8%)^nth year |
1.00 |
0.93 |
0.86 |
0.79 |
0.74 |
PV of cashflows (Net cashflows x PV factor) |
(120,000.00) |
60,185.19 |
38,580.25 |
51,599.10 |
69,827.84 |
NPV ---> Sum of PV of cashflows |
100,192.36 |
Cruncher
Cost |
180,000.00 |
Useful life |
2 years |
Rental income |
150,000.00 |
Operating costs |
20,000.00 |
Service cost after 1 years |
30,000.00 |
Salvage value |
45,000.00 |
Cashflow table
Year |
0 |
1 |
2 |
Initial cost |
(180,000.00) |
||
Total Initial cashflows |
(180,000.00) |
||
Rental income |
150,000.00 |
150,000.00 |
|
Less : Operating costs |
20,000.00 |
20,000.00 |
|
Less : Service at the end of 1 year |
30,000.00 |
||
Total operating cashflows |
100,000.00 |
130,000.00 |
|
Terminal cashflows |
|||
Salvage value |
45,000.00 |
||
Net cashflows (Initial + Operating + Terminal) |
(180,000.00) |
100,000.00 |
175,000.00 |
PV factor @ 10% ---> 1/ (1+10%)^nth year |
1.00 |
0.91 |
0.83 |
PV of cashflows (Net cashflows x PV factor) |
(180,000.00) |
90,909.09 |
144,628.10 |
NPV ---> Sum of PV of cashflows |
55,537.19 |
Unable to provide the complete answer on account of space constraint. Please let me know if you need the googledrive lnk with the complete answer spreadsheet.
Hope this helps you answer the question. Please provide your feedback or rating on the answer.
Thanks