Price Index Calculation
Starting from version V1.46.4.0, the ‘Price Index Before’ for the first period is determined based on the line’s frequency. If the corresponding period is not available in the template, the system will use the nearest previous available period.
In this example, the period is January 01, 2020, to December 31, 2022. On January 01, 2021, the current Price Index is 110.5, and the base Price Index rate is 105.65 (the Price Index value at the time the contract starts). Please refer the price index master setup as shown below.
In the SBS ARCB Billing Price Adjustment page, you set Price Index Calculation to Base Price Index.
Base price index % calculation = (Current CPI – Base CPI) /Base CPI *100
Amount calculation: Previous amount + (Previous amount *CPI%) = Current escalated amount
Example for a Billing Schedule (Start: Jan 2020, Annual, 3 Periods):
-
Adjustment for Jan 01, 2021: Starting with an initial amount of 1000.00 and using CPI values of 110.5 (Current) and 105.65 (Base), the calculation is:
1000.00 + (110.5–105.65)/105.65*1000.00 = 1045.91
-
Adjustment for Jan 01, 2022: The new escalated amount of 1,045.91 becomes the base for the next period:
1045.91 + (114.25-105.65)/105.65*1045.91 = 1,131.04
In the SBS ARCB Billing Price Adjustment page, you set Price Index Calculation to Prior Price Index.
Prior price index % calculation = (Current CPI –Previous CPI) /Previous CPI *100
Example for a Billing Schedule (Start: Jan 2020, Annual):
-
Adjustment for Jan 01, 2021: With a Price Index of 110.5 and a base of 105.65, the escalated amount from 1000.00 is:
1000.00 + (110.5–105.65)/105.65*1000 = 1045.91
-
Adjustment for Jan 01, 2022: Using the new Price Index of 114.25 and the prior index of 110.5, the next amount is calculated from the previous escalated amount:
1045.91 + (114.25-110.5)/110.5*1045.91 = 1081.40
Notes:
- The price adjustment process uses the latest Price Index value, regardless of the index date.
For example, if the price adjustment happens in September, but the latest Price Index value is for July. The July index is used. No adjustments are made after the September index is entered.
Prorated Price Adjustment
If the price adjustment happens in the middle of a billing period, the amount will be prorated. For example, the billing period is August 01, 2020, to July 31, 2021. The Price Index value is 244 on Price Index Date September 01, 2019, and 250 on Price Index Date September 01, 2020. If the previous rate is 1000, the following equations show how the billing amount for this period is calculated:
- Price Index changes = (250 – 244)/244 = 2.459%
- Current Rate = 1000.00 * 2.459% = 1024.59
- Number of days @ current rate = July 31, 2021 – September 01, 2020 = 334
- Previous Rate = 1000.00
- Number of days @ previous rate = August 31, 2020 – August 01, 2020 = 31
- Total number of days in the billing period = July 31, 2021 – August 01, 2020 + 1 = 365
- The billing amount for this period = 1000.00*31/365 + 1024.59*334/365
- = 1022.50
Price Adjustment Using Price Index and %
Price adjustments can also be done by Price Index. The Price Index + 3% price adjustment starts on January 01, 2020, with Annual frequency. Amount billed for January 01, 2019 to December 31, 2020, is 4000. The billing period to be adjusted is January 01, 2020 to December 31, 2020. The Price Index value is 205.3 on the Price Index Date December 01, 2018, and also is 219.6 on the Price Index Date December 01, 2019. If previous rate is 4000.00, the following equations show the billing amount for this period is calculated:
- Price Index changes = (219.6 – 205.3)/205.3 = 6.965%
- Current Rate = 4000.00 * 6.965% - 4000.00 = 278.60
- Percentage changes = 4000.00 * 1.03 - 4000.00 = 120
- Billing amount = 4000.00 + 278.6 + 120.00 = 4398.6
In the Price Index master page, under the Price Index master fields, there is a Boolean button named % Index, which will define whether the Price index Values on the Price index lines will be in % or not.. If this is enabled, then the calculations will be in percent.
CPI + Percentage (%) with Higher or Lower
Example 1
Predefined information for Price Index Schedule:
Item Unit Price: $1,000
Price Index Formula: Percentage Due = [(Current Price Index - Base Price Index) / Base Price Index] * 100
-
For Lower of (Percentage) or (Price Index Schedule + Percentage Along Price Index):
The system uses the lower value between the standard Percentage and the combined value from the Price Index Schedule plus its associated percentage. Since the percentage index is not applicable in this case, the calculation defaults to the standard Percentage of 5%. Based on a unit price of $1,000, the price increase is calculated as 0.05 * 1000 = $50, effective 02/01/2023. Therefore, the updated unit price on that date becomes $1,050. This cumulative adjustment process continues for subsequent dates, as shown below.
-
For Higher of (Percentage) or (Price Index Schedule + Percentage Along Price Index):
The system uses the higher value between the standard Percentage and the combined value from the Price Index Schedule plus its associated percentage. Since the percentage index is not applicable in this case, the calculation proceeded as follows:
The Percentage Due from the Price Index Schedule was calculated as:
-
((Current Index - Base Index) / Base Index) * 100
-
((20 - 10) / 10) * 100 = 100%.
-
This was then combined with the Percentage Along Price Index of 4%, resulting in a total increase of 104%.
Applying this to the unit price of $1,000:
-
The price increase is $1,000 * 1.04 = $1,040.
-
Therefore, the new unit price effective 02/01/2023 is $1,000 + $1,040 = $2,040.
This cumulative adjustment process continues for subsequent dates, as shown below.
-
Example 2
Predefined information for Price Index Schedule:
Item Unit Price: $500
-
For Lower of (Percentage) or (Price Index Schedule + Percentage Along Price Index):
The system uses the lower value between the standard Percentage and the combined value from the Price Index Schedule plus its associated percentage. The percentage increase will be 2%, and the billing details below reflect this adjustment:
-
For Higher of (Percentage) or (Price Index Schedule + Percentage Along Price Index):
The system uses the higher value between the standard Percentage and the combined value from the Price Index Schedule plus its associated percentage. The percentage increase will be (CPI Schedule % + 6%).
For example, on 02/01/2022, the Price Index Schedule contributed 10%, resulting in a total increase of 16% (10% + 6%). The billing details below reflect this adjustment:
Price Index Base Date
The following examples are based on the new Price Index Base Date functionality available from version 1.46.6.0 onwards.
Example 1
Two contracts start on Aug 1, 2023. Contract A is invoiced Annually $1,000 a year. Contract B is invoice Monthly $100 a month. For both contracts, the user defines the Base Price Index Date to be Mar 1, 2023. For contract A, the user defines the Price Index Month as June.
Both contracts are set to have price adjustment starting on Jan 1, 2024. Contract A's price is escalated Annually and Contract B's price is escalated Monthly.
In December 2023, the January invoices are created for both contracts.
The CPI table at that time has the following values:
|
Date |
CPI Value |
|
Mar 1, 2023 |
156.5 |
|
Apr 1, 2023 |
156.7 |
|
May 1, 2023 |
156.5 |
|
Jun 1, 2023 |
156.6 |
|
Jul 1, 2023 |
156.8 |
|
Aug 1, 2023 |
160.1 |
|
Sep 1, 2023 |
160.0 |
|
Oct 1, 2023 |
160.2 |
January's price for both contracts are calculated as follows:
|
Contract |
Base Price Calculation |
Prior Price Calculation |
|---|---|---|
|
A |
1,000 + (156.6 – 156.5)/156.5 x 1,000 = 1,000.64 |
The same |
|
B |
100 + (160.2 – 156.5)/156.5 x 100 = 102.36 |
The same |
When the February invoice gets created in January for Contract B, the CPI table will have the following values:
|
Date |
CPI Value |
|
Mar 1, 2023 |
156.5 |
|
Apr 1, 2023 |
156.7 |
|
May 1, 2023 |
156.5 |
|
Jun 1, 2023 |
156.6 |
|
Jul 1, 2023 |
156.8 |
|
Aug 1, 2023 |
160.1 |
|
Sep 1, 2023 |
160.0 |
|
Oct 1, 2023 |
160.2 |
|
Nov 1, 2023 |
160.3 |
|
Dec 1, 2023 |
160.2 |
|
Jan 1, 2024 |
160.4 |
February's price will be calculated as follows:
|
Contract |
Base Price Calculation |
Prior Price Calculation |
|---|---|---|
|
B |
102.36 + (160.4 – 156.5)/156.5 x 102.36 = 104.91 |
102.36 + (160.4 – 160.2)/160.2 x 102.36 = 102.49 |
When March's invoice gets created for Contract B, the new CPI value is not entered, the latest CPI index is still for Jan 1, 2024. Therefore, there is no price increase for February.
When the April invoice gets created in March for Contract B, the CPI table will have the following values:
|
Date |
CPI Value |
|
Mar 1, 2023 |
156.5 |
|
Apr 1, 2023 |
156.7 |
|
May 1, 2023 |
156.5 |
|
… |
… |
|
Nov 1, 2023 |
160.3 |
|
Dec 1, 2023 |
160.2 |
|
Jan 1, 2024 |
160.4 |
|
Feb 1, 2024 |
160.6 |
February's price will be calculated as follows:
|
Contract |
Base Price Calculation |
Prior Price Calculation |
|---|---|---|
|
B |
104.91 + (160.6 – 156.5)/156.5 x 104.91 = 107.66 |
102.49 + (160.6 – 160.4)/160.4 x 102.49 = 102.62 |
When the January 2025 invoice gets created for both contracts, the CPI table will have the following values:
|
Date |
CPI Value |
|
Mar 1, 2023 |
156.5 |
|
Apr 1, 2023 |
156.7 |
|
May 1, 2023 |
156.5 |
|
Jun 1, 2023 |
156.6 |
|
Jul 1, 2023 |
156.8 |
|
… |
…. |
|
Jun 1, 2024 |
160.1 |
|
… |
|
|
Oct 1, 2024 |
161.4 |
|
Nov 1, 2024 |
161.3 |
|
Dec 1, 2024 |
161.6 |
Assume the last used index for contract B
January's price will be calculated as follows (assume contract B price is 108.41 and 105.20):
|
Contract |
Base Price Calculation |
Prior Price Calculation |
|---|---|---|
|
A |
1,000.64 + (160.1 – 156.5)/156.5 x 1,000.64 = 1,023.66 |
1,000.64 + (160.1 – 156.6)/156.6 x 1,000.64 = 1,023.00 |
|
B |
108.41 + (161.6 – 156.5)/156.5 x 108.41 = 111.94 |
105.20 + (161.6 – 161.3)/161.3 x 105.20 = 105.40 |
When the January 2026 invoice gets created for Contract A, the CPI table will have the following values:
|
Date |
CPI Value |
|
Mar 1, 2023 |
156.5 |
|
Apr 1, 2023 |
156.7 |
|
Jun 1, 2024 |
160.1 |
|
… |
|
|
Jun 1, 2025 |
162.2 |
|
Contract |
Base Price Calculation |
Prior Price Calculation |
|---|---|---|
|
A |
1,023.66 + (162.2 – 156.5)/156.5 x 1,023.66 = 1,060.94 |
1,023.00 + (162.2 – 160.1)/160.1 x 1,023.00 = 1,036.42 |
Example 2
Contract A starts on Aug 1, 2023. It is invoiced at $100 per month. The Base Price Index Date is Mar 1, 2023 and the Price Index Month is October. The price adjustment starts on Jan 1, 2024 and is escalated Annually. The calculation method is Prior.
In December 2023, the January invoice is created.
Case A: If the CPI table at that time has the following values, no escalation happens in January since the October index is not available. Only the CPI table will be updated.
|
Date |
CPI Value |
|
Mar 1, 2023 |
156.5 |
|
Apr 1, 2023 |
156.7 |
|
May 1, 2023 |
156.5 |
|
Jun 1, 2023 |
156.6 |
|
Jul 1, 2023 |
156.8 |
|
Aug 1, 2023 |
160.1 |
|
Sep 1, 2023 |
160.0 |
Assume that before the February invoice is created, the October index (160.2) is added to the CPI table. The February invoice then gets adjusted since it is not created yet. No changes are done to the January invoice since it has been already created.
Case B: If the CPI table at that time has the following values, January price will be adjusted using the March 1 CPI (156.5) and October 1 CPI (160.2).
|
Date |
CPI Value |
|
Mar 1, 2023 |
156.5 |
|
Apr 1, 2023 |
156.7 |
|
May 1, 2023 |
156.5 |
|
Jun 1, 2023 |
156.6 |
|
Jul 1, 2023 |
156.8 |
|
Aug 1, 2023 |
160.1 |
|
Sep 1, 2023 |
160.0 |
|
Oct 1, 2023 |
160.2 |
In December 2024, the January 2025 invoice is created.
Case C: If the last CPI value is entered for Sep 2024 (no October values is available yet):
-
For Case A above, no escalation will happen since the last escalation CPI value is for Oct 1, 2023 and the new escalation value for Oct 1, 2024 is not available yet.
-
For Case B above, no escalation will happen since the last escalation CPI value is for Oct 1, 2023 and the new escalation value for Oct 1, 2024 is not available yet.
Case D: If the Oct 1, 2024 CPI index is entered as 161.6:
-
For Case A above, the escalation price will be calculated using the CPI values for Oct 1, 2023 (160.2) and Oct 1, 2024 (161.6).
-
For Case B above, the escalation price will be calculated using the CPI values for Oct 1, 2023 (160.2) and Oct 1, 2024 (161.6)
Below is a summary of what the contract price would look like:
|
Month |
Case A Price |
Case B Price |
|---|---|---|
|
Dec 1, 2023 |
$100 |
$100 |
|
Jan 1, 2024 |
100 |
100 + (160.2 – 156.5)/156.5 x 100 = 102.36 |
|
Feb 1, 2024 |
100 + (160.2 – 156.5)/156.5 x 100 = 102.36 |
102.36 |
|
Mar 1, 2024 |
102.36 |
102.36 |
|
… |
… |
… |
|
Dec 1, 2024 |
102.36 |
102.36 |
|
Jan 1, 2025, Case C |
102.36 |
102.36 |
|
Jan 1, 2025 Case D |
102.36 + (161.6 – 160.2)/160.2 x 102.36 = 103.25 |
102.36 + (161.6 – 160.2)/160.2 x 102.36 = 103.25 |
Example 3
Contract A starts on Aug 1, 2023. It is invoiced at $100 a month. The Base Price Index Date is Mar 1, 2023. The price adjustment starts on Jan 1, 2024 and is escalated Monthly. The calculation method is Prior.
In December 2023, the January invoice is created.
The CPI table at that time has the following values:
|
Date |
CPI Value |
|
Mar 1, 2023 |
156.5 |
|
Apr 1, 2023 |
156.7 |
|
May 1, 2023 |
156.5 |
|
Jun 1, 2023 |
156.6 |
|
Jul 1, 2023 |
156.8 |
|
Aug 1, 2023 |
160.1 |
|
Sep 1, 2023 |
160.2 |
|
Oct 1, 2023 |
160.2 |
The January price will be calculated as 100 + (160.2 – 156.5)/156.5 x 100 = 102.36.
When the February invoice is created, if the last CPI value is still for October 2023, no escalation happens.
If the latest CPI value is for December (e.g., 161.6) , the February price will be calculated as 102.36 + (161.6 – 160.2)/160.2 x 102.36 = 103.25.
Example 4
Two contracts started on Aug 1, 2023. Contract A is invoiced Annually $1,000 a year. Contract B is invoiced Monthly $100 a month.
Both contracts have Price Increase set up using CPI, beginning Jan 1, 2024. Contract A has price increased annually and contract B has price increased monthly.
Both contracts are invoiced up to Jan 1, 2025, then the new and then the latest SBS release with the CPI Enhancement was installed.
The CPI table at that time has the following values:
|
Date |
CPI Value |
|
Aug 1, 2023 |
156.5 |
|
… |
… |
|
Jan 1, 2024 |
156.6 |
|
Feb 1, 2024 |
157.5 |
|
Mar 1, 2024 |
157.9 |
|
… |
… |
|
Oct 1,2024 |
158.0 |
|
Nov 1, 2024 |
158.8 |
|
Dec 1, 2024 |
159.8 |
|
Jan 1, 2025 |
160.1 |
|
Feb 1, 2025 |
160.0 |
|
… |
… |
|
Nov 1, 2025 |
162.4 |
Below are the contract invoice amounts before the enhancement using the Base Price Index calculation method:
|
Month |
Case A Price |
Case B Price |
|---|---|---|
|
Dec 1, 2023 |
1,000 |
100 |
|
Jan 1, 2024 |
1,000 + (156.6 – 156.5)/156.5 x 1,000 = 1,000.64 |
100 + (156.6 – 156.5)/156.5 x 100 = 100.06 |
|
Feb 1, 2024 |
N/A |
100.06 + (157.5 – 156.5)/156.5 x 100.06 = 100.7 |
|
… |
… |
… |
|
Dec 1, 2024 |
N/A |
105.26 + (158.8 – 156.5)/156.5 x 105.26 = 106.81 |
Below are the contract invoice amounts before the enhancement using the Prior Price Index calculation method:
|
Month |
Case A Price |
Case B Price |
|---|---|---|
|
Dec 1, 2023 |
1,000 |
100 |
|
Jan 1, 2024 |
1,000 + (156.6 – 156.5)/156.5 x 1,000 = 1,000.64 |
100 + (156.6 – 156.5)/156.5 x 100 = 100.06 |
|
Feb 1, 2024 |
N/A |
100.06 + (157.5 – 156.6)/156.6 x 100.06 = 100.64 |
|
… |
… |
… |
|
Dec 1, 2024 |
N/A |
104.12 + (158.8 – 158.0)/158.0 x 104.12 = 104.65 |
After Dec 2024 was invoiced, the new SBS release is installed and both schedules received new fields with the following values:
| Base Price Index Date | Price Index Month | |
|---|---|---|
| Contract A | Aug 1, 2023 | Prior |
| Contract B | Aug 1, 2023 | Prior |
If the values of the new fields are not changed, both contracts will continue to escalate as before (assuming 2025 is invoiced with the new enchantment).
Below are the contract invoice amounts before the enhancement using the Base Price Index calculation method:
|
Contract |
A |
B |
|---|---|---|
|
Dec 1, 2024 |
1,000.64 |
106.81 |
|
Jan 1, 2025 |
1,000.64 + (159.8 – 156.5)/156.5 x 1,000.64 = 1,021.74 |
106.81 + (159.8 – 156.5)/156.5 x 106.81 = 109.06
|
|
… |
… |
… |
Below are the contracts invoice amounts before the enhancement using the Prior Price Index calculation method:
|
Contract |
A |
B |
|---|---|---|
|
Dec 1, 2024 |
1,000.64 |
104.65 |
|
Jan 1, 2025 |
1,000.64 + (159.8 – 156.6)/156.6 x 1,000.64 = 1,021.09 |
104.65 + (159.8 – 158.8)/158.8 x 104.65 = 105.30
|
|
… |
… |
… |
Assume that before the Jan 2026 invoice the CPI parameters of Contract A are changed as follows:
| Base Price Index Date | Price Index Month | |
|---|---|---|
| Contract A | Jan 1, 2024 | November |
The Jan 2026 invoice for Contract A will have the following prices:
Base Price Index calculation method:
1,021.74 + (162.4 – 156.6)/156.6 x 1,021.74 = 1,059.58
Prior Price Index calculation method:
1,021.09 + (162.4 – 158.8)/158.8 x 1,021.09 = 1,044.23
