Undoubtedly, Microsoft Excel is a ubiquitous tool for analyzing data and solving complex problems. Now, wouldn’t it be great if we could make a calculator for inflating costs? Sounds complex, right? Wrong! In this article, we’ll demonstrate the detailed procedure on **how to construct cost inflation index calculator** in Excel. In addition, we’ll also learn to use the cost inflation index to reduce taxes on capital gains and plot the cost inflation index chart.

The animated GIF above is an overview of this article, which represents the construction of a cost inflation index calculator in Excel. In the following sections, we’ll learn what the cost inflation index is and the steps to make a cost inflation index calculator with the necessary illustrations.

## What Is Cost Inflation Index?

First and foremost, let’s start with a quick explanation, so you don’t have to spend all day on this.

Simply put, the** Cost Inflation Index** is a tool for measuring inflation when computing the capital gains of assets in the long run. Since assets tend to appreciate in value over time, the owner can earn a large capital gain (difference between the selling and acquisition prices) when selling this asset.

In general, capital gains are subjected to taxes by the government. In fact, the cost inflation index helps to reduce this tax by adjusting the purchase price of the asset with the cost inflation index. Thus, decreasing the net profit and hence the capital gains tax.

## How to Construct Cost Inflation Index Calculator in Excel: 3 Easy Steps

To begin with, let’s consider the **Cost Inflation Index** table shown in the **B4:B26** cells containing the *“Sl. No.”*, *“Financial Year”*, and *“Cost Inflation Index”* columns respectively.

Here, we want to obtain the indexed cost of acquisition for a specific year by combining the **VLOOKUP** and **IFERROR** functions to look up the *“Cost Inflation Index” *value for the corresponding *“Financial Year”*. So, let’s see it in action.

Here, we have used the *Microsoft Excel 365* version; you may use any other version at your convenience.

### 📌 Step 1: Make Drop-down for Financial Years with Data Validation

- First, go to the
**C5**cell >> move to the**Data**tab >> click the**Data Validation**drop-down >> choose the**Data Validation**option.

Now, this opens the **Data Validation** dialog box.

- Next, in the
**Allow**field, select the**List**option >> click the**Arrow**button.

- Then, highlight the
**C5:C26**cells as the**Source**for the drop-down.

- Afterward, press
**OK**to close the**Data Validation**window.

- After that, choose a
*“Financial Year”*(*“2003-04”*) in the*“Acquisition Information”*table >> enter the*“Value of Asset”*(*“$1,500,000”*) in the**C6**cell.

- In the same manner, insert a second drop-down list for the
*“Sale Information”*.

**Read More: **Truck Operating Cost Calculator in Excel

### 📌 Step 2: Obtain Cost Inflation Index Using VLOOKUP Function

- Second, jump to the
**C11**cell >> enter the following equation >> press**ENTER**.

`=IFERROR(VLOOKUP(C9,'Cost Inflation Index Table'!C5:$D$26,2,FALSE)/VLOOKUP(C5,'Cost Inflation Index Table'!C5:$D$26,2,FALSE),"")`

**Formula Breakdown**

**VLOOKUP(C9,’Cost Inflation Index Table’!C5:$D$26,2,FALSE) →**looks for a value in the left-most column of a table, and then returns a value in the same row from a column you specify. Here,**C9**cell (argument) is mapped from the*lookup_value***‘Cost Inflation Index Table’!C5:$D$26**(argument) array in the*table_array**“Cost Inflation Table”*worksheet. Next,**2**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***Exact match**of the lookup value.**Output → 280**

**VLOOKUP(C5,’Cost Inflation Index Table’!C5:$D$26,2,FALSE) →**here,**C5**cell (argument) is mapped from the*lookup_value***‘Cost Inflation Index Table’!C5:$D$26**(argument) array in the*table_array**“Cost Inflation Table”*worksheet. Next,**2**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***Exact match**of the lookup value.**Output → 109**

**IFERROR(VLOOKUP(C9,’Cost Inflation Index Table’!C5:$D$26,2,FALSE)/VLOOKUP(C5,’Cost Inflation Index Table’!C5:$D$26,2,FALSE),””)****→**becomes**IFERROR(280/109,””)****→**returns value_if_error if the expression has an error and the value of the expression itself otherwise. Here,**280/109**is theargument, and*value***“”**is theargument. In this case, the function returns the result of the division.*value_if_error***Output → 2.57**

📃 *Note:**Please make sure to use **Absolute Cell Reference** by pressing the F4 key on your keyboard.*

**Read More: **How to Use Cost Benefit Analysis Calculator in Excel

### 📌 Step 3: Calculate Indexed Cost of Acquisition

- Third, proceed to the
**C13**cell >> insert the expression into the**Formula Bar**.

`=IFERROR(C6*C11,"")`

Here, the **IFERROR** function checks whether the product of the **C6** and **C11** cells is valid, in which case it returns the result; otherwise, it returns blank.

Finally, the results should look like the image shown below.

**Read More: **How to Create Cost of Delay Calculator in Excel

## How to Use Cost Inflation Index to Reduce Taxes on Capital Gains

For one thing, we can introduce some modifications to our cost inflation index calculator to compute the tax savings on capital gains. It’s quick and simple, so just follow the steps shown below.

📌 __Steps__**:**

- Initially,
**follow the steps shown previously to get the Indexed Cost of Acquisition**shown in the picture below.

- Following this, go to the
**C16**cell >> subtract the value of the**C14**cell from the**C10**cell >> click on**ENTER**.

`=C10-C14`

For instance, the **C10** and **C14** cells refer to the *“Value of Asset”* and *“Indexed Cost of Acquisition”*.

- Not long after, calculate the
*“Tax Rate”*for the indexation method in the**C19**cell.

`=C18*C16`

For example, the **C16** and **C18** cells point to the *“Capital Gain”* and the given *“20% Tax Rate”*.

- At this point, copy and paste the formula into the
**C22**cell >> press**ENTER**to obtain the*“Regular Tax Rate”*.

`=(C10-C6)*C21`

On this occasion, the **C6** and **C10**cells indicate the *“Value of Asset”* in the *“Acquisition and Sale Information”* tables, while the **C21** cell represents the *“Regular Tax Rate of 10%”*.

- Lastly, we can return the
*“Tax Savings”*using the formula given below.

`=C22-C19`

Here, the **C19** and **C22** cells represent the *“Indexed Tax”* and the *“Regular Tax”*.

## How to Make Cost Inflation Index Chart in Excel

Last but not least, we can also make a cost inflation index chart using Excel’s powerful graphing engine. Now, allow us to demonstrate the process in the steps below.

📌 __Steps__**:**

- In the first place, choose the
**D5:D26**cells >> navigate to the**Insert**tab >> insert**2-D Column**chart.

- In turn,
**left-click**to select the chart >> press the**Select Data**button.

- Now, in the
**Select Data Source**window, press the**Edit**button.

- Not long after, select the
**C5:C26**cells as the horizontal axis labels >> press**OK**.

- Later,
**right-click**on the chart >> click on**Fill**>> choose a color, here we’ve chosen the*“White, Background 1, Darker 5%”*color.

Eventually, format the chart using the **Chart Elements** option.

- In addition to the default selection, enable the
**Axes Title**to provide axes names. Here, it is the*“Financial Year”*and*“Cost Inflation Index”*. - Now, add the
**Chart Title**, for example, “*Cost Inflation Index Chart”*. - Further, uncheck the
**Gridlines**option to give your chart a clean look.

Subsequently, this should generate the chart as shown in the figure below.

## Practice Section

We have provided a** Practice** section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

**Download Practice Workbook**

## Conclusion

To sum up, we hope this article helps you understand the process of constructing a **cost inflation index calculator** in Excel. Now, if you have any queries, please leave a comment below.

## Related Articles

- Opportunity Cost Calculator in Excel
- How to Create Shipping Cost Calculator in Excel
- How to Create Electricity Cost Calculator in Excel
- How to Create Fuel Cost Calculator Using Excel Formula
- How to Make Vehicle Life Cycle Cost Analysis Spreadsheet in Excel
- How to Create an Export Price Calculator in Excel

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