How to Calculate Delta Percentage in Excel (4 Easy Methods)

What Is Delta Percentage?

The delta percentage is the percentage change in any quantity. For example, if a person’s weight in June is W1= 70 Kg and in July is W2=75 Kg, then the change in weight, ΔW=W2-W1=75-70=5 Kg. Then, the Delta Percentage = (Change in Weight/Initial Weight)*100% = ΔW/W1*100%

Calculate the Delta Percentage in Excel: 4 Useful Methods

We’ll use a dataset containing the stock’s old price and new price.

Method 1 – Use a Mathematical Formula

The formula that we will use here is below.

Delta Percentage = (New Value-Old Value)*100/ Old Value

Steps:

In cell E5, insert the following formula.

=(D5-C5)/C5

Hit Enter.

If your result doesn’t show in percentage form, format the cell as Percentage.

Use the Fill Handle to get the results for other cells.

Read More: How to Calculate Delta in Excel

Method 2 – Calculating the Delta Percentage Using an Alternative Formula

Delta percentage can also be calculated by the following formula: Delta Percentage= (New Value/Old Value-1)*100%

Steps:

In cell E5, insert the formula below.

=(D5/C5)-1

Press Enter.

Use the Fill Handle to get the results of the remaining cells.

Method 3 – Using the Excel SUM Function to Calculate the Delta Percentage

Steps:

In cell E5, use the following formula.

=SUM(D5,-C5)/C5*100

Hit Enter.

Use the Fill Handle to apply the formula to the rest of the cells.

Method 4 – Using VBA Code

Steps:

Open the VBA window by pressing Alt + F11.

Open a new module by going to Insert and selecting Module.

Copy-paste the following code.

Sub Delta_Percent_Formula()
Dim AA_rOld As Range
Dim AA_rNew As Range
Dim sOld As String
Dim AA_sNew As String
Dim AA_sFormula As String
    On Error GoTo ErrExit
    Set AA_rNew = Application.InputBox( _
            "Select the cell that contains the NEW Value", _
            "Select New Cell", Type:=8)
    Set AA_rOld = Application.InputBox( _
            "Select the cell that contains the OLD Value", _
            "Select Old Cell", Type:=8)
    AA_sNew = AA_rNew.Address(False, False)
    sOld = AA_rOld.Address(False, False)
    AA_sFormula = "=IFERROR((" & AA_sNew & " - " & sOld & ")/" & sOld & ",0)"
    ActiveCell.Formula = AA_sFormula
ErrExit:
End Sub

How Does the Code Work?

We have created a Subroutine named Delta_percent_Formula. We have declared 2 variables (AA_rold & AA_rNew) as Range type and 3 variables (sOld, AA_sNew and AA_sFormula) as String. The code will open prompt windows asking New Value and Old Value Cells. We calculate the delta percentage using the AA_sFormula. We also used an InputBox to set a prompt window to input the values.

Select cell E5, then go to the developer window and run the code by clicking F5.
In a pop-up window, select the cell that contains the New Value and click OK.