**Life Cycle Cost Analysis** (**LCCA**) evaluates the overall cost of asset possession. It takes into account costs involved in buying, caring for, and discarding a venture or an item. The goal of **LCCA** is to determine the entire project costs of different designs. Also, choose the one that will result in a lower overall residual value for a facility of comparable features. We can easily construct **LCCA **with Microsoft Excel, as it has built-in tools and functions to perform different calculations. In this article, we will demonstrate the procedures to make a vehicle life cycle cost analysis spreadsheet in Excel.

**What Is Life Cycle Cost Analysis?**

A technique for determining the overall cost of facility management is life-cycle cost analysis (**LCCA**). It accounts for all expenses related to purchasing, maintaining, and getting rid of construction or formation.

The overall cost of resource ownership is investigated and evaluated using life cycle cost analysis. It takes into account costs associated with purchasing, caring for, using, and discarding a project or an item. When numerous projects meet the same performance standards but have different running expenses and startup prices, it is notably utilized to choose the best project. These costs must be compared in order to choose the approach that would maximize net savings.

The formula for **Life Cycle Cost** is,

**LCC = Costs of Acquisition + Operating Cost + Maintenance Costs + Total Other Costs & Savings – End of Life**

**How to Make Vehicle Life Cycle Cost Analysis Spreadsheet in Excel: Step-by-Step Procedures**

As we already know, **Life Cycle Cost Analysis** (**LCCA**) draws upon the well-established concepts of financial evaluation. Compared to other economic methodologies that solely consider initial expenses or short-term operational costs, **LCCA** greatly outperforms in evaluating long-term cost-effectiveness. So, letâ€™s look at the process to construct the **LCCA** in Excel.

**Step 1: Input General Conditions for Vehicle**

To start, we need to input the general conditions of the vehicle.

- Firstly, insert the period of vehicle ownership in the year. In our case, we have
**ten**years of the period of vehicle ownership. - Secondly, the discount rate we input is in percentage. We get a
**2%**discount. - Thirdly, suppose we get two offers, so, we put the number of bids or offers in the column.

**Step 2: Compute Acquisition Costs**

To compute the acquisition costs we will need to combine **the IF function **and **the PV function**. The **PV** function is categorized under the financial function in Excel. This function returns the present value of any investment. And we use the **IF **function to match any condition. This is one of the logical functions in Excel. Now, assume that our vehicle model is a **Mercedes Citaro**, now we need to calculate the costs of acquisition per unit.

- In the first place, input the number of vehicles, then the purchase price per unit, and also the lease price (
**if any**). In our example, we do not have any lease price, so we keep the cell empty. - Then, insert the following formula.

`=IF(D11>0,-PV($D$6*0.01,$D$5,D13)+D12,0)`

- Further, 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. Then, we take the period of vehicle ownership and the lease price as the arguments of the**PV**function. And, we get the result**-0**.**-PV($D$6*0.01,$D$5,D13)+D12:**Now, 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):**By estimating the formula we get the acquisition cost.

**Step 3: Calculate Operating Cost Per Vehicle**

Now, we will calculate the operating cost per vehicle. The vehicle operating cost refers to the entire costs that drivers incur when they use their automobiles to travel from one location to another.

- For this, we need the annual use of the vehicle which is
**80000 km**. - Then, the type of the fuel, in this case, we use
**diesel**. - Also, the fuel consumption per vehicle is
**45**. - Next is the price of the fuel, which is
**$5**. - After 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`

- Finally, press
**Enter**. And you will get the result in the resulting cell and the formula will appear in the formula bar.

**🔎**** How Does the Formula Work?**

Here, we use the formula for operating cost, so we first obtain the total fuel cost. For this, we multiply the fuel consumption per vehicle per 100 km by the fuel price and annual use of the vehicle. Then, divide the total cost by the annual use of the vehicle.

**Step 4: Quantify Maintenance Costs Per Vehicle**

Maintenance costs are the single or multiple ongoing expenses a business has to pay to maintain its assets, vehicles, and equipment. To calculate the maintenance cost, again we are going to merge the **IF **and the **PV** functions.

- To begin with, we provide the annual maintenance costs per unit for a year and also an annual service agreement alternatively.
- Secondly, insert the formula.

`=IF(H5>0,-PV($D$6*0.01,$D$5,(H5+H6)))`

- Lastly, press the
**Enter**button on your keyboard.

**🔎**** How Does the Formula Work?**

**-PV($D$6*0.01,$D$5,(H5+H6):**Likewise step 2, we multiply the discount rate by**1%**, or**0.01**in decimal form, in order to obtain the present value. To ensure that the value of the cell won’t change, we keep it as an absolute value. Then, periods of ownership are used as the arguments in the**PV**function. Next, we just add the annual maintenance costs per unit and the annual service agreement. But in our case, we donâ€™t have the annual service agreement.**=IF(H5>0,-PV($D$6*0.01,$D$5,(H5+H6))):**This will calculate the maintenance cost per unit.

**Read More:** How to Create an Export Price Calculator in Excel

**Step 5: Evaluate Taxes & Other Costs/Subsidies Per Vehicle**

In this step, we will evaluate the taxes and other costs or subsidies per vehicle.

- First, we need the vehicle tax per unit within a year.
- Second, the insurance cost during a year, per unit.
- Further, the infrastructure.
- Instead of this, we can just set the infrastructure annual cost.
- Furthermore, to calculate the total other costs and savings per unit, 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?**

Similarly, as in the previous steps, we use those arguments for the **PV** function. Here, we also add the vehicle tax, insurance cost, and infrastructure. And we get the sum of those parameters.

**Step 6: Insert End of Life**

A vehicle may be stripped, damaged, or otherwise unusable due to mechanical breakdown in order to be designated **End of Life**. Although some components are revenues to fund from car part manufacturers. Some are recovered and sold for use in fixing other vehicles. These latter parts are then reconditioned and supplied to auto supply retailers.

- We input the remnant value per unit.
- Then, simply put
**the****SUM function**to generate the**End of Life**.

`=Sum(H17)`

- Lastly, hit the
**Enter**button on your keyboard.

**Step 7: Calculate Total Life Cycle Cost**

Now, the main step, to calculate the total life cycle cost we have to follow the sub procedures.

- Firstly, we will calculate the total life cycle cost per unit. For this, put the formula into the resulted cell.

`=D14+D21+H7+H14-H18`

- Hit
**Enter**.

- Further, to calculate the total cost type the formula.

`=H20*D11`

- Similarly, to finish the procedure, press the
**Enter**key.

**Step 8: Construct Diagram**

Spreadsheet administrators may visualize data sets using Excel charts. We can construct a diagram for visualizing the total life cycle cost.

- Select both cells of the total life cycle cost per unit and the total life cycle cost.
- Then, go to the
**Insert**tab from the ribbon. - Now, from the
**Charts**category, click on the**Insert Column or Bar Chart**drop-down menu. - Next, choose the
**Clustered Column**from the**2-D Column**.

- And, you will be able to see the diagram for the
**LCCA**for vehicles.

**Final Template**

This is the final template for the vehicle life cycle cost analysis spreadsheet in Excel.

**Download Template**

You can download the template and practice with them.

**Conclusion**

The above procedures will assist you to **Make Vehicle Life Cycle Cost Analysis** **Spreadsheet** in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.

## 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
- How to Create Cost of Delay Calculator in Excel

**<< Go Back to Cost Calculator |Â Finance Template |Â Excel Templates**

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.xlsxRegards,

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)/D17We 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