**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.

**Download Template**

You can download the template and practice with them.

**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**

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

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*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 cost. For this, we multiply the fuel consumption per vehicle by the fuel price. Then, divide the total cost by the annual use of the vehicle.

**Read More:** **How to Make Daily Vehicle Mileage and Fuel Report in Excel**

**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.

**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.

**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. Or you can have a glance at our other articles in the **ExcelDemy.com** blog!