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.
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.
You are to do a cash-flow and then net present value analysis of a car purchase....
Present value. A smooth-talking used-car salesman who smiles considerably is offering you a great deal on a "pre-owned" car. He says, "For only 4 annual payments of $2800, this beautiful 1998 Honda Civic can be yours." A) If you can borrow money at 7%, what is the price of this car? Assume the payment is made at the end of each year.
Present value. A smooth-talking used-car salesman who smiles considerably is offering you a great deal on a "pre-owned" car. He says, "For only 7 annual payments of $2 comma 800, this beautiful 1998 Honda Civic can be yours." If you can borrow money at 8%, what is the price of this car? Assume the payment is made at the end of each year.
Present value. A smooth-talking used-car salesman who smiles considerably is offering you a great deal on a "pre-owned" car. He says, "For only 4 annual payments of $2,700, this beautiful 1998 Honda Civic can be yours if you can borrow money at 8%, what is the price of this car? Assume the payment is made at the end of each year. If you can borrow money at 8%, what is the price of this car? (Round to the nearest cent.)
Please need help Present value. A smooth-talking used-car salesman who smiles considerably is offering you a great deal on a "pre-owned" car. He says, "For only 6 annual payments of $2,800, this beautiful 1998 Honda Civic can be yours." If you can borrow money at 8%, what is the price of this car? Assume the payment is made at the end of each year. If you can borrow money at 8%, what is the price of this car? $ (Round...
Cost Benefit Analysis over 10 years Do a cost benefit analysis to decide wheteher to switch from a gas car built before 2005 to an electric car. Do a cost benefit analysis to decide wheteher to switch from a gas car built after 2005 to an electric car. HINT: Make cash flow table for 10 years reflecting the total savings of switching from pre 2005 gas car to electric car HINT: Make cash flow table for 10 years reflecting the...
You deciding between purchasing an electric car and an all-gas model. The electric model sells for 38,000 and can drive 258 miles per full charge.The all−gas model gets an effective gas mileage of 22 MPG and sells for 38,000 and can drive 258 miles per full charge.The all−gas model gets an effective gas mileage of 22 MPG and sells for 22,000. Assuming you drive 15,000 miles a year, which of the two cars would be the more economical over ten...
You purchased a car using some cash and borrowing $12,000 (the present value) for 53 months at 11% per year. Calculate the monthly payment (annuity). Then assume you have made ten payments. What is the balance (present value) of your loan? The monthly payment at the specified loan rate over the given period is $______. (Round to the nearest cent.)
You purchased a car using some cash and borrowing $18,000 the present value for 25 months at 15% per year. Calculate the monthly payment annuity payments. What is the balance (present value) of your loan? Then assume you have made ten The monthly payment at the specified loan rate over the given period is (Round to the nearest cent.) Enter your answer in the answer box and then click Check Answer.
net present value method Net Present Value Method 20% Rapid Delivery, Inc., is considering the purchase of an additional delivery vehicle for $32,000 on January 1, 2016. The truck is expected to have a five-year life with an expected residual value of $7,000 at the end of five years. The expected additional revenues from the added delivery capacity are anticipated to be $51,000 per year for each of the next five years. A driver will cost $36,000 in 2016, with...
You are considering the purchase of a brand new Mercedes car but are worried about the maintenance cost of the car relative to other cars. You plan to keep the car for 10 years and have done some online research that seems to indicate that the cost of maintain the car will be $500 in the first year and increase on average by 5% per year. If you wanted to have a fund that would cover the maintenance costs, how...