Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Construct Cost Inflation Index Calculator in Excel

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.

Overview of how to use the Cost Inflation Index Calculator

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.


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.

Cost Inflation Index table

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.

Choosing Data Validation in the data tab

Now, this opens the Data Validation dialog box.

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

Inserting drop-down list

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

Entering source for the drop-down list

  • Afterward, press OK to close the Data Validation window.

Pressing the ok button to close 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.

Selecting financial year from drop down list

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

Inserting drop down with data validation feature

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 ( lookup_value argument) is mapped from the ‘Cost Inflation Index Table’!C5:$D$26(table_array argument) array in the “Cost Inflation Table” worksheet. Next, 2 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
    • Output → 280
  • VLOOKUP(C5,’Cost Inflation Index Table’!C5:$D$26,2,FALSE) → here, C5 cell ( lookup_value argument) is mapped from the ‘Cost Inflation Index Table’!C5:$D$26(table_array argument) array in the “Cost Inflation Table” worksheet. Next, 2 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the 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 the value argument, and “” is the value_if_error argument. In this case, the function returns the result of the division.
    • Output → 2.57

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

Using VLOOKUP function to look up cost inflation index from table

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.

Using IFERROR function to check if the product of valid

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

cost inflation index calculator excel with data validation and VLOOKUP and IFERROR functions

Read More: How to Calculate Projected Cost in Excel (4 Effective Ways)


Similar Readings


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:

Obtaining the indexed cost of acquisition

  • 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”.

Calculating capital gain by subtracting indexed cost of acquisition from value of asset

  • 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”.

Computing tax using the indexed 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 C10cells indicate the “Value of Asset” in the “Acquisition and Sale Information” tables, while the C21 cell represents the “Regular Tax Rate of 10%”.

Calculating tax using the regular tax rate

  • 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”.

using cost inflation index calculator excel to reduce taxes on capital gains


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.

Insert 2-D Column chart from the Insert tab

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

Clicking on Select data button

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

Pressing Edit in the Select Data Source window

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

Selecting horizontal axis labels

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

Choosing Fill color for column chart

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.

Formatting chart with chart elements option

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

Making Cost Inflation Index Column Chart


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.

Practice Section for cost inflation index calculator excel


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

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo