Method 1 – Input General Conditions for Vehicle
- Insert the period of vehicle ownership in the year. We have ten years of vehicle ownership.
- The discount rate we input is in percentage. We get a 2% discount.
- Put the number of bids or offers in the column.
Method 2 – Compute Acquisition Costs
- Input the number of vehicles, then the purchase price per unit, and the lease price (if any). We do not have any lease price, so we keep the cell empty.
- Insert the following formula.
=IF(D11>0,-PV($D$6*0.01,$D$5,D13)+D12,0)- Press Enter to see the result.
How Does the Formula Work?
- -PV($D$6*0.01,$D$5,D13): To acquire the present value, we multiply the discount rate by 1%, which is 0.01 in decimal. We retain the cell as an absolute value so that the value of that cell won’t change. We take the period of vehicle ownership and the lease price as the arguments of the PV function. We get the result -0.
- -PV($D$6*0.01,$D$5,D13)+D12: Simply add the purchase price with the present value. And we get $230.
- IF(D11>0,-PV($D$6*0.01,$D$5,D13)+D12,0): Estimating the formula we get the acquisition cost.
Method 3 – Calculate Operating Cost Per Vehicle
- The annual use of the vehicle, which is 80000 km.
- Type of the fuel, in this case, we use diesel.
- The fuel consumption per vehicle is 45.
- Next is the price of the fuel, which is $5.
- Inserting all the components that we use for the calculation of the operating cost per unit, put the formula for the calculation.
=((D19/100)*D17*D20)/D17- Press Enter. Get the result in the resulting cell, and the formula will appear in the formula bar.
How Does the Formula Work?
The formula for operating cost, so we first obtain the total fuel cost. We multiply the fuel consumption per vehicle per 100 km by the fuel price and annual use of the car. Divide the total cost by the annual use of the vehicle.
Method 4 – Quantify Maintenance Costs Per Vehicle
- Provide the annual maintenance costs per unit for a year and an alternative yearly service agreement.
- Insert the formula.
=IF(H5>0,-PV($D$6*0.01,$D$5,(H5+H6)))- Press the Enter button on your keyboard.
How Does the Formula Work?
- -PV($D$6*0.01,$D$5,(H5+H6): Step 2, we multiply the discount rate by 1%, or 0.01 in decimal form, to obtain the present value. We keep the cell’s value as an absolute value to ensure that it won’t change. Periods of ownership are used as the arguments in the PV function. We add the annual maintenance costs per unit and the annual service agreement. We don’t have an annual service agreement.
- =IF(H5>0,-PV($D$6*0.01,$D$5,(H5+H6))): This will calculate the maintenance cost per unit.
Method 5 – Evaluate Taxes & Other Costs/Subsidies Per Vehicle
- We need the vehicle tax per unit within a year.
- The insurance cost during a year, per unit.
- The infrastructure.
- We can just set the infrastructure annual cost.
- Calculate the total other costs and savings per unit and insert the formula into the selected cell.
=-PV(D6*0.01,D5,(H10+H11+H13))+H12- Press Enter to compute the total costs and savings.
How Does the Formula Work?
Use those arguments for the PV function. Add the vehicle tax, insurance cost, and infrastructure, and we get the sum of those parameters.
Method 6 – Insert End of Life
- Input the remnant value per unit.
- Put the SUM function to generate the End of Life.
=Sum(H17)- Hit the Enter button on your keyboard.
Method 7 – Calculate Total Life Cycle Cost
- Calculate the total life cycle cost per unit. Put the formula into the resulting cell.
=D14+D21+H7+H14-H18- Hit Enter.
- Calculate the total cost and type the formula.
=H20*D11- Finish the procedure, press the Enter key.
Method 8 – Construct Diagram
- Select both cells of the total life cycle cost per unit and the total life cycle cost.
- Go to the Insert tab from the ribbon.
- Click on the Insert Column or Bar Chart drop-down menu from the Charts category.
- Choose the Clustered Column from the 2-D Column.
- See the diagram for the LCCA for vehicles.
Download Template
You can download the template and practice with them.
Related Articles
- How to Create Electricity Cost Calculator in Excel
- Opportunity Cost Calculator in Excel
- How to Create Shipping Cost Calculator in Excel
- How to Construct Cost Inflation Index Calculator in Excel
- How to Create Fuel Cost Calculator Using Excel Formula
- How to Use Cost Benefit Analysis Calculator in Excel
<< Go Back to Cost Calculator | Finance Template | Excel Templates
Get FREE Advanced Excel Exercises with Solutions!
Hello, I would like to ask, can you do like a life cycle cost analysis for a duration of 25 years per se for the car using this method?
Hello AFIF,
Thank you for your question. Yes, we can perform the vehicle life cycle cost analysis for a duration of 25 years; just change the period of ownership to 25 and Excel should automatically show the updated results.
We have also included the Excel file with this message for you to download.
Vehicle-Life-Cycle-Cost-Analysis.xlsx
Regards,
ExcelDemy
Dear ExcelDemy,
Thanks for sharing much appreciated! Here in Uganda, the acquisition cost includes the vehicle cost and all associated taxes, can I ignore the ‘Taxes & Other Costs/Subsidies Per Vehicle’ section please? Does maintenance cost include Scheduled and Unscheduled maintenance costs?
Nicholas
Dear Tanjim Reza,
Many thanks for the response, much appreciated! Does it mean that I can leave the ‘Taxes and other costs’ blank?
Hello, ENG NICHOLAS LUMANYIKA!
You are welcome.
About your query, yes, if you do not have any taxes and other costs associated with your vehicle, you can leave those cells blank. The given formula will analyze your vehicle life cycle accordingly.
With Regards,
Md. Tanjim Reza Tanim
Team Leader, ExcelDemy
Hello, Nicholas Lumanyika!
Thank you for your query.
Regarding your query, Acquisition cost in vehicle life cycle cost analysis refers to the initial cost incurred when purchasing a vehicle. It includes the total expenses associated with acquiring the vehicle, such as the actual purchase price, taxes, licensing fees, delivery charges, and any additional costs directly related to the procurement of the vehicle.
So, if you had to pay tax and other subsidies when purchasing the vehicle, you must enlist these in the acquisition cost calculation.
And, regarding your second query, yes, maintenance cost include both scheduled and unscheduled maintenance costs.
With Regards,
Md. Tanjim Reza Tanim
Team Leader, ExcelDemy
Dear ExcelDemy,
Kindly clarify the Operating cost/unit of $0.00281, I am failing to understand it. Is it operating cost per km? Where does the 100km figure go? The total cost of 45 x 5 should be for 100km.
Hello, Nicholas Lumanyika!
Thank you so much for your valuable feedback.
Yes, you are right. The total cost of 45*5 would be for 100 km. So, In this calculation, we have to find our total fuel cost per vehicle first. Then we have to divide the total fuel cost by total fuel consumption. So, the final formula in the D21 cell would be:
=((D19/100)*D17*D20)/D17
We have corrected the file and formula of our article accordingly. Please check it now and use the file as you need.
If you have any further queries, confusion, or recommendation please feel free to reach out to us.
With Regards,
Md. Tanjim Reza Tanim
Team Leader, ExcelDemy.
Dear Tanjim Reza,
Many thanks for the quick response, much appreciated!
Eng. Lumanyika Nicholas