CODEWITHSUNDEEP
sqlmath
Hevant Bhojaram
Hevant Bhojaram

Reputation: 27

Calculate Annual Escalations in SQL

I have a dataset (in SQL) where I need to calculate the market value over the entire period. I want to automate this calculation in SQL. The Initial value is 2805.00 per month payable for 36 months. But the value is escalated at 5.5% after each block of twelve months. I have included a picture below to show the values and how they escalate. In terms of what fields I have in SQL, I have the length of the term (36 months [months]). I also have an escalation percentage (5.5% in this case [percentage]) and the starting value [starting_value], 2805.00 in this case. The total value (Expected Result [result]) is 106 635.72. I am after what the calculation should look like in SQL so that I can apply it across all market points.

Market Calc

Upvotes: 0

Views: 165

Answers (1)

Roberto C. Rodriguez-Hidalgo
Roberto C. Rodriguez-Hidalgo

Reputation: 632

Here is a fast performance formula to calculate the expected result [result] if you need to calculate every, annual salary with respect to the first one:

[result] = (N * (N + 1) / 2) * [increment] * 12 + M * (N + 1) * [increment] + [starting_value] * [months]

Where:

N = TRUNC([months] / 12) - 1// TRUNC --> Integer value of dividing [months] / 12
M = [months] MOD 12 // Rest of integer division of [months] / 12
[increment] = [percentage] * [starting_value] / 100

On the other hand, if you need to calculate each annual salary with respect to its predecessor you will need the formula below:

∑y=0TRUNC([months]/12)−1{([percentage]/100 + 1)y * 12 * [starting_value]} + ([percentage]/100 + 1)TRUNC([months]/12) + 1 * ([months] MOD 12) * [starting_value]

This is a bit confusing but there is no way to place formulas in Stack overflow. Moreover, if you need to run this in some DBMS you should assure someone that allows you to make loops, as the sum will need it.

You would need to adapt both formulas to the DBMS, taking into account the comments I placed before. Hope this to be helpful.

Upvotes: 1

Related Questions