PERIOD_LOOKUP
A PERIOD_LOOKUP function returns any periods values.
Syntax
PERIOD_LOOKUP(offset, initialValue) - > [number]
Arguments
| Arguments | Datatype | Description |
|---|---|---|
offset | Number | The number of periods it has to go back or front. |
initial Value | Number | The value of first period. |
Remarks
This function can only be used after referring a node for which this calculation needs to be done as shown below in the example.
If we give negative number for the offset it will go back to the past periods and vice versa.
Period lookup will only work if there is 1 series (Year). To use it for multiple series PRIOR_SERIES_PERIOD_LOOKUP function should be used along with PERIOD_LOOKUP.
Example 1
Consider a scenario where you want to calculate the closing balance of Non-Current assets by including the monthly movements. Below is the information.
Non-Current Asset Balance | $30 million |
Depreciation (Straight line) | 12% pa |
To achieve this, the following formula should be written in the [Opening Balance] node:
[Closing Balance].PERIOD_LOOKUP(-1,30000000)
Closing Balance node name is referred in the formula before using PERIOD_LOOKUP so that the calculation is performed for this specific node values. Since we have mentioned the first period value as $30 million it will take this as January month value. After that it will take the Closing Balance node as the previous period value as we have mentioned -1.
Result
| Period | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sept | Oct | Nov | Dec |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
Opening Balance | 30000000 | 29700000 | 29400000 | 29100000 | 28800000 | 28500000 | 28200000 | 27900000 | 27600000 | 27300000 | 27000000 | 26700000 |
Monthly movements | 300000 | 300000 | 300000 | 300000 | 300000 | 300000 | 300000 | 300000 | 300000 | 300000 | 300000 | 300000 |
Closing Balance | 29700000 | 29400000 | 29100000 | 28800000 | 28500000 | 28200000 | 27900000 | 27600000 | 27300000 | 27000000 | 26700000 | 26400000 |
Example 2
Consider a scenario where you have Year 1's Unit price value and Growth rate of Unit price for Years 2 to 5. We need to calculate the current year's price by taking the previous year's price and multiplying it with the growth rate.
| Year | 1 |
|---|---|
Unit price | 400 |
Growth rate (Years 2-5) | 5% |
To achieve this, the following formula should be written in the [Unit Price] node:
(THIS.PERIOD_LOOKUP(-1,400)*(1+[Growth rate (Years 2-5)])
Result
| Year | 1 | 2 | 3 | 4 | 5 |
|---|---|---|---|---|---|
Unit price | 400 | 420 | 441 | 463.05 | 486.20 |
Last updated