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.

**Table of Contents**hide

## Download Practice Workbook

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

## 3 Steps of Constructing Cost Inflation Index Calculator in Excel

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: ****How to Calculate Cost per Unit in Excel (With Easy Steps)**

### 📌 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 Calculate Projected Cost in Excel (4 Effective Ways)**

**Similar Readings**

**How to Calculate Discount Price in Excel (4 Quick Methods)****Calculate Price Increase Percentage in Excel (3 Easy Ways)****How to Calculate Selling Price from Cost and Margin in Excel****Calculate Price Per Pound in Excel (3 Easy Ways)****How to Calculate Unit Price in Excel (3 Quick Methods)**

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

## 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. And you can read more articles like this on our website, **ExcelDemy**.

## Related Articles

**How to Create Opportunity Cost Calculator in Excel****Create an Inventory Cost Carrying Calculator in Excel****How to Create Shipping Cost Calculator in Excel (with Easy Steps)****Calculate Production Cost in Excel (3 Effective Ways)****How to Calculate Selling Price in Excel (4 Easy Methods)****Calculate Variable Cost Per Unit in Excel (with Quick Steps)****How to Calculate Bond Price in Excel (4 Simple Ways)**