Question

You are to do a cash-flow and then net present value analysis of a car purchase. You have a choice beween a Chevrolet Bolt electric car costing $35,000 (after tax incentives) that has an electric motor efficiency of 4.2 miles per kWh (6.7 km/kWh), and a Honda Civic Hybrid that costs $25,000 and has a gas mileage of 45 miles/gallon (5.3 liters/100 km). Assume an initial gasoline cost of $2.50 per escalating at 5%/yr over the ten-year lifetime of the car. Electricity initially costs $0.10/kWh and escalates at 3%/year. You drive 10,000 miles per year. You have little money, so you pay $3000 initially and finance the rest of the purchase price at a rate of 5%/year. Your personal discount rate is 10%, showing moderate impatience with respect to payback time. Set this up as a spreadsheet and construct a cash-flow diagram and determine whether the net present value is positive or negative, and by how much. Could you have done the calculation analytically, i.e. without the spreadsheet and just using formulas? If so, show how it would be set up. If not, explain why not.

Answer 1

Chevorlet Bolt
Initial cost	35000
Electricity	0.1
Escalation cost	3%
Total distance per year	10000
Motor efficiency	4.2 miles per kWh
Upfront payment	3000
Remaining payment over 10 years	3500
Year/Item	1	2	3	4	5	6	7	8	9	10
Initial payment excl. upfront payment	32,000.0
Electricity cost	0.100	0.103	0.106	0.109	0.113	0.116	0.119	0.123	0.127	0.130
Total distance travelled	10,000.0	10,000.0	10,000.0	10,000.0	10,000.0	10,000.0	10,000.0	10,000.0	10,000.0	10,000.0
Electricity used	2,381.0	2,381.0	2,381.0	2,381.0	2,381.0	2,381.0	2,381.0	2,381.0	2,381.0	2,381.0
Total electricty cost	238.1	245.2	252.6	260.2	268.0	276.0	284.3	292.8	301.6	310.7
Remaining payment	3,500.0	3,500.0	3,500.0	3,500.0	3,500.0	3,500.0	3,500.0	3,500.0	3,500.0	3,500.0
Interest	175.0	175.0	175.0	175.0	175.0	175.0	175.0	175.0	175.0	175.0
Total outflow	3,913.1	3,920.2	3,927.6	3,935.2	3,943.0	3,951.0	3,959.3	3,967.8	3,976.6	3,985.7
NPV of car	7,780
Honda Civic Hybrid
Initial cost	25000
Gasoline cost	2.5
Escalation cost	5%
Total distance per year	10000
Gasoline efficiency	45 miles/gallon
Upfront payment	3000
Remaining payment over 10 years	2500
Year/Item	1	2	3	4	5	6	7	8	9	10
Initial payment excl. upfront payment	22,000.0
Gasoline cost	2.500	2.575	2.652	2.732	2.814	2.898	2.985	3.075	3.167	3.262
Total distance travelled	10,000.0	10,000.0	10,000.0	10,000.0	10,000.0	10,000.0	10,000.0	10,000.0	10,000.0	10,000.0
Total gallons used	222.2	222.2	222.2	222.2	222.2	222.2	222.2	222.2	222.2	222.2
Total gasoline cost	555.6	572.2	589.4	607.1	625.3	644.0	663.4	683.3	703.8	724.9
Remaining payment	2,500.0	2,500.0	2,500.0	2,500.0	2,500.0	2,500.0	2,500.0	2,500.0	2,500.0	2,500.0
Interest	125.0	125.0	125.0	125.0	125.0	125.0	125.0	125.0	125.0	125.0
Total outflow	3,180.6	3,197.2	3,214.4	3,232.1	3,250.3	3,269.0	3,288.4	3,308.3	3,328.8	3,349.9
NPV of car	2,046

Presented above is the excel snapshot for the spreadsheet.

We could also have done this analytically using compound interest formulas for input cost escalation and discounting. Although it would be very long, it could have done without excel too.