Built-in formula functions

Here you can find the details regarding the Macrobond formula language.

In the function descriptions below, the following convention has been used when naming parameters:

 value The parameter can be either a number or a series. The type of the result is typically the same as this parameter. For instance, Log(20) returns a number, but Log(sek) returns a series. series The parameter can only be a series. (See discussion earlier in this document for how the automatic conversion of numbers to series works) other Other parameter names, such as number, window, observation, length, must be numbers unless other information is provided. For instance, you can write Lag(sek, 10), but not Lag(sek, nok).

Mathematical functions

Abs(value)

Returns the absolute value.

Example: Abs(usflof_002090)

Acos(value)

Returns the angle whose cosine is the specified value. The angle is expressed in radians.

Example: Acos(0.5)

AggregateProduct(series)

Returns the aggregated product of all previous numbers. Null numbers are treated as one.

Example: Russia's GDP is presented as 'CPPY=100' (Change Y/Y + 100). To turn it into index series you need to modify formula: AggregateProduct(1+(runaac0033-100)/100)

Example: AggregateProduct(1+0.05/100)*100 continous growth rate of 5%

AggregateSum(series)

Returns the aggregated sum of all previous numbers. Null numbers are treated as zero.

${𝑦}_{𝑡}=\sum _{i=0}^{t}{𝑥}_{𝑖}$

Example: AggregateSum(secons0162)

AggregateSumAnnual(series)

Returns the aggregated sum of previous numbers from the start of each year. Null numbers are treated as zero.

Example: AggregateSumAnnual(secons0162)

This is implemented in the Macrobond formula language as:

AggregateSumAnnual(series) =

let

.a = AggregateSum(series)

in

end

Asin(value)

Returns the angle whose sine is the specified value. The angle is expressed in radians.

Example: Asin(0.866)

Atan(value)

Returns the angle whose tangent is the specified value. The angle is expressed in radians.

Example: Atan(1.7321)

Atan2(x, y)

Returns the angle whose tangent is the quotient of two specified values. The angle is expressed in radians.

Example: Atan2(1.7321, 1)

Returns the Baxter-King bandpass filtered series with the specified properties.

Example: BKFilter(usgdp, 6, 32, 32)

CFRandomWalkFilter(series, shortPeriod, longPeriod)

Returns the series filtered with asymmetric Christiano-Fitzgerald bandpass filter for non-stationary series with the specified properties.

Example: CFRandomWalkFilter(usgdp, 6, 32)

CFStationaryFilter(series, shortPeriod, longPeriod)

Returns the series filtered with asymmetric Christiano-Fitzgerald bandpass filter for stationary series with the specified properties.

Example: CFStationaryFilter(usgdp, 6, 32)

Ceiling(value)

Returns the smallest integer greater than or equal to the specified value.

Example: Ceiling(sek)

Cos(value)

Returns the cosine of the specified angle. The angle should be expressed in radians and must be in the range -9223372036854775295 to 9223372036854775295.

Example: Cos(1.0472)

Cot(value)

Returns the cotangent of the specified angle. The angle should be expressed in radians.

Example: Cot(1.0472)

CountValid(series)

Returns the number of values that are not null.

Example: CountValid(fi10ygov)

CountValid(series, window)

Returns the number of values that are not null in a window of the specified length.

Example: CountValid(fi10ygov, WeeksLength(1))

CoVariance(series1, series2)

Returns the covariance of series1 and series2.

Example: Covariance(sek, segdp)

CoVariance(series1, series2, window)

Returns the covariance of series1 and series2 within a window of the specified length.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Example: Covariance(sek, segdp, YearsLength(1))

E()

The number e.

EMean(series, factor)

Returns the exponential moving average with the specified factor. The factor, α, must be a number between 0 and 1.

Example: EMean(sek, 0)

For

Exp(value)

Returns e raised to the power of the specified value, where e is the base of the natural logarithm.

Example: Exp(sek)

FillNullWithLinear(series)

Fills null values with a linear combination of the previous and next values.

Example: Set Conversion settings > Missing value method to 'Do not fill in': FillNullWithLinear(sp600_20201080tr)

FillNullWithNext(series)

Fills null values with the next value.

Example: Set Conversion settings > Missing value method to 'Do not fill in': FillNullWithNext(sp600_20201080tr)

FillNullWithPrevious(series)

Fills null values with the previous value.

Example: Set Conversion settings > Missing value method to 'Do not fill in': FillNullWithPrevious(sp600_20201080tr)

FillNullWithPreviousAsForecast(series)

Fills null values with the previous value and flag as forecast.

Example: Set Conversion settings > Missing value method to 'Do not fill in': FillNullWithPreviousAsForecast(sp600_20201080tr)

First(series)

Returns the first number of a series.

Example: First(sek)

FirstGreaterOrEqual(series, value)

Returns the observation number of the first observation greater or equal than the specified value.

Example: if(Counter()=FirstGreaterOrEqual(sp500_500, 4200), sp500_500, Null())

FirstLessOrEqual(series, value)

Returns the observation number of the first observation less or equal than the specified value.

Example: if(Counter()=FirstLessOrEqual(sp500_500, 4200), sp500_500, Null())

FirstValid(series)

Returns the first number of a series that is not null.

Example: First(sek)

Floor(value)

Returns the largest integer less than or equal to the specified value.

Example: Floor(sek)

GeometricMean(series)

Returns the geometric mean.

Example: GeometricMean(plgdp)

This is implemented in the Macrobond formula language as:

GeometricMean(series) = Exp(Mean(Ln(series)))

GeometricMean(series, window)

Returns the geometric moving average of the specified length.

Example: GeometricMean(plgdp, YearsLength(2))

This is implemented in the Macrobond formula language as:

GeometricMean(series, window) = Exp(Mean(Ln(series), window))

High(series)

Returns the highest number.

Example: High(usgdp)

High(series, window)

Returns the highest number in a window of the specified length. You can specify a vector as the length parameter in the High and Low formula functions.

Example: you can get the highest value in a series using an expanding window: High(usgdp, Counter(eur)-Start(eur)+1)

or without expanding window: High(usgdp, Yearslength(2))

HighObs(series)

Returns the observation number of the last highest value.

Example: HighObs(usgdp)

HighObs(series, window)

Returns the observation number of the last highest value within a window of the specified length.

In this formula a “window” can be a number or a series. If the values in a series are not natural numbers, application will round them down to the nearest natural number.

Example: HighObs(usgdp, Yearslength(2))

HPFilter(series, lambda)

Returns a series smoothed by using the Hodrick-Prescott method with the specified lambda factor, which must be a number greater than zero. We calculate the HP filter according to Hodrick–Prescott filter - Wikipedia, which is the same method as can be found in MATLAB and EViews.

A typical value for lambda when used for quarterly data is 1600.

Example: HPFilter(brnaac0016, 1600)

HPFilterOneSided (series, lambda)

Returns a series smoothed by using a one-sided Hodrick-Prescott method with the specified lambda factor, which must be a number greater than zero.

The one-sided filter uses only past information when calculated each smoothed value. The calculation is the same as using the ordinary HP filter on all values up to the current value at each point in time.

A typical value for lambda when used for quarterly data is 1600.

Example: HPFilterOneSided(brnaac0016, 1600)

Intercept(series1, series2)

Returns the intercept after regression correlation of series1 and series2 with series1 as the dependent.

Example: Intercept(usgdp, uspric2373)

Intercept(series1, series2, window)

Returns the intercept after regression correlation of series1 and series2 with series1 within a window of the specified length.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Example: Intercept(usgdp, uspric2373, MonthsLength(20))

Last(series)

Returns the last value of a series.

Example: Last(eur)

LastGreaterOrEqual(series, value)

Returns the observation number of the last observation greater or equal than the specified value.

Example: if(Counter()=LastGreaterOrEqual(sp500_500, 4200), sp500_500, Null())

LastLessOrEqual(series, value)

Returns the observation number of the last observation less or equal than the specified value.

Example: if(Counter()=LastLessOrEqual(sp500_500, 4200), sp500_500, Null())

LastValid(series)

Returns the last valid value of a series.

Example: LastValid(eur)

LastNonForecast(series)

Returns the last value of the series that is not a forecast.

Example: LastNonForecast(bp_oecdoilprodt)

Linear(series)

Returns a line fitted using the least square method.

Example: Linear(sek)

This is implemented in the Macrobond formula language as:

Linear(series) = LinSlope(series)*(Counter(series) - Start(series)) + LinIntercept(series)

Linear(series, observationStart, observationEnd)

Returns a line fitted using the least square method by using data from observationStart to, but not including, observationEnd.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Example: Linear(sek, Date(2017, 01, 01), Now())

This is implemented in the Macrobond formula language as:

Linear(series, observationStart, observationEnd) =
let
.slice = Cut(series, observationStart, observationEnd)
in
LinSlope(.slice)*(Counter(series) - observationStart) + LinIntercept(.slice)
end

LinearExtended(series, observationExtended)

Returns a line fitted using the least square method. The line is extended until observationExtended.

Example: LinearExtended(plgdp, Endvalid(plgdp)+YearsLength(4))

This is implemented in the Macrobond formula language as:

LinearExtended(series, observationExtended) =
let
.slope = LinSlope(series)
.intercept = LinIntercept(series)
.observationStart = StartValid(series)
.line = .slope*(Counter(series) - .observationStart) + .intercept
.extendedValue = .slope*(observationExtended - .observationStart) + .intercept
in
ExtendLinear(.line, observationExtended, .extendedValue)
end

LinearExtended(series, observationStart, observationEnd, observationExtended)

Returns a line fitted using the least square method by using data from observationStart (start of trend calculation) to, but not including, observationEnd (end of trend calculation). The line is extended until observationExtended.

Example: LinearExtended(plgdp, Date(2010, 1, 1), Date(2015, 1, 1), Endvalid(plgdp)+YearsLength(4))

This is implemented in the Macrobond formula language as:

LinearExtended(series, observationStart, observationEnd, observationExtended) =
let
.slice = Cut(series, observationStart, observationEnd)
.slope = LinSlope(.slice)
.intercept = LinIntercept(.slice)
.line = .slope*(Counter(series) - observationStart) + .intercept
.extendedValue = .slope*(observationExtended - observationStart) + .intercept
in
ExtendLinear(.line, observationExtended, .extendedValue)
end

LinIntercept(series)

Returns the intercept of a line fitted with the least square method .

Example: LinIntercept(dxy)

LinIntercept(series, window)

Returns the intercept of a line fitted with the least square method within a window of the specified length.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Example: LinIntercept(dxy, YearsLength(1))

LinSlope(series)

Returns the slope of a line fitted with the least square method.

Example: LinSlope(dxy)

LinSlope(series, window)

Returns the slope of a line fitted with the least square method within a window of the specified length.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Example: LinSlope(dxy, YearsLength(1))

Ln(value)

Returns the natural logarithm.

Example: Ln(100)

Example: Ln(sek)

Log(value)

Returns the logarithm with base 10.

Example: Log(100)

Example: Log(sek)

Low(series)

Returns the lowest number of the series.

Example: Low(sek)

Low(series, window)

Returns the lowest number in a window of the specified length. You can specify a vector as the length parameter in the High and Low formula functions.

Example: Low(sek, Yearslength(1))

Example: You can get the lowest value so far in a series using an expanding window like this: Low(eur, Counter(eur)-Start(eur)+1).

LowObs(series)

Returns the observation value of the last lowest number.

Example: LowObs(sek)

LowObs(series, window)

Returns the observation value of the last lowest number within a window of the specified length.

In this formula a “window” can be a number or a series. If the values in a series are not natural numbers, application will round them down to the nearest natural number.

Example: LowObs(sek, Yearslength(1))

Max(value1, value2)

Returns the largest value of a pair of values.

Example: Max(sek)

Mean(series)

Returns the mean value as a number.

Example: Mean(segdp)

Mean(series, window)

Returns the moving average of the specified length.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

In this formula a “window” can be a number or a series. If the values in a series are not natural numbers, application will round them down to the nearest natural number.

Example: Mean(segdp, Yearslength(1))

Median(series)

Returns the median as a number.

Example: Mean(segdp)

Median(series, window)

Returns the median value in a window of the specified length.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

In this formula a “window” can be a number or a series. If the values in a series are not natural numbers, application will round them down to the nearest natural number.

Example: Mean(segdp, Yearslength(1))

Min(value1, value2)

Returns the smallest value of a pair of values.

Example: Min(swedsegdpfcst, sefcst0076)

Example: Min(swedsegdpfcst, 2)

Mod(value1, value2)

Returns the reminder of an integer division.

Example: Mod(swedsegdpfcst, 2)

The calculation uses a floored division and is equal to: value1 – value2*Floor(value1/value2)

Momentum(series, length)

Returns the difference between the series and a lagged series.

Example: Momentum(plgdp, yearslength(1))

Example: When you want to disaggregate series you can combine Momentum() with if(): if(Month()=1, dedemo0013, momentum(dedemo0013, 1))

NextGreaterOrEqual(series, value, observation)

Returns the observation number of the next observation greater or equal than the specified value.

Example: Counter()-NextGreaterOrEqual(sp500_500, sp500_500, Counter()-1) It calculates the number of days before the value was as high

NextLessOrEqual(series, value, observation)

Returns the observation number of the next observation less or equal than the specified value.

Example: Counter()-NextLessOrEqual(sp500_500, sp500_500, Counter()-1) It calculates the number of days before the value was as low

Null()

Returns the null number.

Example: You can use it inside if() condition formula: if(swedsegdpfcst>3.5, 1, Null())

Null0(value)

Returns 0 if the value is null and otherwise the value.

Example: Set Conversion settings > Missing value method to 'Do not fill in': Null0(sp600_20201080tr)

Null1(value)

Returns 1 if the value is null and otherwise the value.

Example: Set Conversion settings > Missing value method to 'Do not fill in': Null1(sp600_20201080tr)

PI()

The number π.

Example: 2*pi()

Pow(value, power)

Returns a value raised to a power.

Example: Pow(sek, 2)

PreviousGreaterOrEqual(series, value, observation)

Returns the observation number of the previous observation greater or equal than the specified value.

Example: Counter()-PreviousGreaterOrEqual(sp500_500, sp500_500, Counter()-1) It calculates the number of days since the value was as high

PreviousLessOrEqual(series, value, observation)

Returns the observation number of the previous observation less or equal than the specified value.

Example: Counter()-PreviousLessOrEqual(sp500_500, sp500_500, Counter()-1) It calculates the number of days before the value was as low

Product(series)

Returns the product of all numbers in the series.

Example: product(Cut(uscpi, Date(2019, 1, 1), Date(2020, 1, 1)))

Product(series, window)

Returns the product of all values in a window of the specified length.

Example: product(Cut(uscpi, Date(2019, 1, 1), Date(2020, 1, 1)), Quarterslength(1))

Round(value, decimals)

Returns the value rounded to the specified number of decimals.

The number of decimals can be from 0 to 15

The value is rounded to the closest value with the specified number of decimals. If the value is exactly in between two values, it is rounded to the value that ends with an even digit. This follows the IEEE Standard 754 as specified in section 4 and is sometimes called “banker’s rounding”. Other midpoint strategies can be obtained by using the Floor and Ceiling functions.

Example: Round(sek, 2)

Sign(value)

Returns -1 for negative values, 0 for 0 and 1 for positive values.

Example: You can wrap it around change over period (cop()) formula and check the growth: Sign(Cop(usgdp, Yearslength(1)))

Sin(value)

Returns the sine of the specified angle.

The angle should be expressed in radians and must be in the range -9223372036854775295 to 9223372036854775295.

Example: Sin(1.0472)

Slope(series1, series2)

Returns the coefficient of the regression, a.k.a. the beta, between series1 and series2 with series1 as the dependent.

Example: Slope(usgdp, uspric2373)

Slope(series1, series2, window)

Returns the coefficient of the regression, a.k.a. the beta, between series1 and series2 with series1 as the dependent within a window of the specified length.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Example: Slope(usgdp, uspric2373, Yearslength(1))

Sqrt(value)

Returns the square root.

Example: Sqrt(usgdp)

Sum(series)

Returns the sum of all numbers in the series.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Example: Sum(opecsecrudeoilimp)

Sum(series, window)

Returns the sum of all numbers in a window of the specified length.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

In this formula a “window” can be a number or a series. If the values in a series are not natural numbers, application will round them down to the nearest natural number.

Example: Sum(opecsecrudeoilimp, Yearslength(1))

Tan(value)

Returns the tangent of the specified angle. The angle should be expressed in radians.

Example: Tan(1.0472)

Statistical functions

ChiDist(value, degFree)

Returns the probability for a value based on the chi distribution with the specified degrees of freedom. The parameters can be either a number or series.

Example: ChiDist(sek, 2)

ChiDistInv(probability, degFree)

Returns the value for a probability based on the chi distribution with the specified degrees of freedom. The parameters can be either a number or series.

Example: ChiDistInv(0.5, 20)

Correlation(series1, series2)

Returns the correlation coefficient of series1 and series2.

Example: Correlation(uscpi, btc)

Correlation(series1, series2, window)

Returns the correlation coefficient of series1 and series2 in a window of the specified length.

Example: Correlation(uscpi, btc, YearsLength(1))

CorrelationBestLag(series1, series2, startLag, endLag)

Returns lag with the highest correlation coefficient of series1 and series2.

Example: CorrelationBestLag(uscpi, btc, -Yearslength(1), Yearslength(1))

FDist(value, degFreeNum, degFreeDenom)

Returns the cumulative probability for a value based on the F distribution with the specified degrees of freedom for the numerator and denominator. The parameters can be either a number or series.

Example: FDist(sek, 2, 1)

FDistInv(probability, degFreeNum, degFreeDenom)

Returns the value for a probability based on the F distribution with the specified degrees of freedom for the numerator and denominator. The parameters can be either a number or series.

Example: FDist(0.5, 2, 1)

Kurtosis(series)

Returns the excess kurtosis of a series.

Example: Kurtosis(sek)

Kurtosis(series, length)

Returns the excess kurtosis of a series in a window of the specified length.

Example: Kurtosis(sek, Yearslength(1))

LogNormDist(value, mean, stdDev)

Returns the probability for a value based on the log-normal distribution with the specified mean and standard deviation. The parameters can be either a number or series.

Example: LogNormDist(sek, 2, 1)

LogNormDistInv(probability, mean, stdDev)

Returns the value for a probability based on the log-normal distribution with the specified mean and standard deviation. The parameters can be either a number or series.

Example: LogNormDistInv(0.5, 2, 1)

LowerTailMean(series, p)

Returns the mean of the number of lowest values as specified by the percentage, p. The parameter p should be in the range 0 < p < 100. This is sometimes known as the tail expectation.

Example: LowerTailMean(sek, 20)

LowerTailMean(series, p, window)

Returns the mean of the number of lowest values as specified by the percentage, p, in a window of the specified length. The parameter p should be in the range 0 < p < 100. This is sometimes known as the tail expectation.

Example: LowerTailMean(sek, 20, Yearslength(1))

NormDist(value)

Returns the probability for a value based on the standard normal distribution. The parameters can be either a number or series.

Example: NormDist(sek)

NormDist(value, mean, stdDev)

Returns the probability for a value based on the normal distribution with the specified mean and standard deviation.

Example: NormDist(sek, 2, 1)

This is implemented in the Macrobond formula language as:

NormDist(value, mean, stdDev) = NormDist((value - mean) / stdDev)

NormDistInv(probability)

Returns the value for a probability based on the standard normal distribution. The parameters can be either a number or series.

Example: NormDistInv(0.5)

NormDistInv(probability, mean, stdDev)

Returns the value for a probability based on the normal distribution with the specified mean and standard deviation. The parameters can be either a number or series.

Example: NormDistInv(0.5, 2, 1)

This is implemented in the Macrobond formula language as:

NormDistInv(probability, mean, stdDev) = NormDistInv(probability)*stdDev+mean

Percentile(series, p)

Returns the number of the p:th percentile from series, where p is in the range 0-100.

Example: Percentile(sek, 1) will give you lower tail 1%

Example: Percentile(sek, 99) will give you upper tail 1%

Percentile(series, p, window)

Returns the value of the p:th percentile within a window of the specified length, where p is in the
range 0-100.

At the start of the series, before there are enough values to cover a full window, the calculation
will be made on fewer observations than specified by the window parameter. This is also the
case if there are missing values.

Example: Percentile(sek, 1, YearsLength(1)) will give you lower tail 1% in a rolling yearly time frame

Example: Percentile(sek, 99, YearsLength(1)) will give you upper tail 1% in a rolling yearly time frame

ExpandingPercentile(series, p)

Returns the value of the p:th percentile within an expanding window (0 < p < 100).

Example:  Percentile(sek, 1) will give you lower tail 1% in an expanding time frame

PercentRank(series, value)

Returns the rank of a value in a series as a percentage (0..100) of the values in the series.

A missing value will be returned if the value is outside the range of values in the series.

If the value is in between values in the series, a linear interpolation will be made using the ranks of the surrounding values. The last parameter can be either a number or series.

Example:  PercentRank(sek, sek) this calculates the percentile value of the series “sek” for each observation.

PercentRank(series, value, length)

Returns the rank of a value in a series as a percentage (0..100) of the values within a window of the specified length.

A missing value will be returned if the value is outside the range of values in the series.

If the value is in between values in the series, a linear interpolation will be made using the ranks of the surrounding values. The second parameter can be either a number or series.

Example:  PercentRank(sek, sek, YearsLength(1))

ExpandingPercentRank(series, value)

Returns the rank of a value in a series as a percentage (0..100) of the values in an expanding window.
It calculates in the order from smallest to largest. This corresponds to passing 1 as the third parameter to RANK.EQ in Excel. It also use 0 as the index of the first value.

Example:  ExpandingPercentRank(sek, Lastvalid(sek))

Returns a seasonally adjusted series calculated by using an additive method with weights in a window of the specified number of years.

Skewness(series)

Returns the skewness of a series.

Example: Skewness(sek)

Skewness(series, length)

Returns the skewness of a series in a window of the specified length.

Example: Skewness(sek, Yearslength(1))

Standardize(series)

Z-score. Returns a normalised series where the mean is 0 and the standard deviation is 1.

Example:  Standardize(ussurv1055)

Standardize(series, window)

Z-score. Returns a normalised series where the mean is 0 and the standard deviation is 1 percentile within a window of the specified length.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Example:  Standardize(ussurv1055, MonthsLength(6))

This is implemented in the Macrobond formula language as:

Standardize(series, window) = FlagForecast((series - Mean(series, window)) / StdDev(series, window), IsForecast(series))

StdDev(series)

Returns the standard deviation of the numbers in a series relative its mean value.

Example:  StdDev(ussurv1055)

StdDev(series, window)

Returns the standard deviation compared to the mean value within a window of the specified length.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

In this formula a “window” can be a number or a series. If the values in a series are not natural numbers, application will round them down to the nearest natural number.

Example:  StdDev(ussurv1055, MonthsLength(6))

TDist(value, degFree)

Returns the probability for a value based on the Student-T distribution with the specified degrees of freedom.

The parameters can be either a number or series.

Example:  TDist(sek, 2)

This is implemented in the Macrobond formula language as:

TDist(value, degFree) =
let
.dist = (1-FDist(value*value, 1.0, degFree))/2
in
if (value > 0.0, 1-.dist, .dist)
end

TDistInv(probability, degFree)

Returns the value for a probability based on the Student-T distribution with the specified degrees of freedom. The parameters can be either a number or series.

Example:  TDist(0.5, 2)

This is implemented in the Macrobond formula language as:

TDistInv(probability, degFree) =
let
.f = Sqrt(FDistInv(1-if (probability < 0.5, probability, 1-probability)*2, 1, degFree))
in
if (probability < 0.5, -.f, .f)
end

Trimean(series)

It returns the Tukey’s trimean.

Example:  Trimean(sek)

TwoPieceNormDist(probability, mode, stdDev1, stdDev2)

Returns the probability for a value based on the 2-piece normal distribution with the specified mode, mean and standard deviations. The parameters can be either a number or series.

Example:  TwoPieceNormDist(0.5, 1, 4, 8)

This is implemented in the Macrobond formula language as:

TwoPieceNormDist(value, mode, stdDev1, stdDev2) =
let
.norm = NormDistCdf((value-mode) / if (value > mode, stdDev2, stdDev1))
in
if(value > mode,
(stdDev1-stdDev2)/(stdDev1+stdDev2)+2*stdDev2/(stddev1+stdDev2)*.norm,
2*stdDev1/(stdDev1+stdDev2)*.norm)
end

TwoPieceNormDistInv(probability, mode, stdDev1, stdDev2)

Returns the value for a probability based on the 2-piece normal distribution with the specified mode, mean and standard deviation.

Example:  TwoPieceNormDistInv(0.5, 1, 4, 8)

This is implemented in the Macrobond formula language as:

TwoPieceNormDistInv(probability, mode, stdDev1, stdDev2) =
let
.m = stdDev1/(stdDev1+stdDev2)
.stdsum = stdDev1+stdDev2
.norm = NormDistInv(if (probability > .m, (probability-(stdDev1-stdDev2)/.stdsum)*.stdsum/(2*stddev2), probability*.stdsum/(2*stdDev1)))
in
if (probability > .m, .norm*stdDev2, .norm*stdDev1)+mode
end

UpperTailMean(series, p)

Returns the mean of the number of highest values as specified by the percentage, p. The parameter p should be in the range 0 < p < 100. This is sometimes known as the tail expectation.

Example:  UpperTailMean(sek, 99)

UpperTailMean(series, p, window)

Returns the mean of the number of highest values as specified by the percentage, p, in a window of the specified length. The parameter p should be in the range 0 < p < 100. This is sometimes known as the tail expectation.

Example:  UpperTailMean(sek, 99, MonthsLength(6))

Variance(series)

Returns the variance of the series.

Example:  Variance(sek)

Variance(series, window)

Returns the variance for a series within a window of the specified length.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Example:  Variance(sek, Yearslength(1))

Date and observation number functions

Returns a series of all observation numbers offset by a number of months

Returns the observation number for the specified observation number plus a number of months.

Returns a series of all observation numbers offset by a number of years.

Returns the observation number for the specified observation number plus a number of years.

At(series, observation)

Returns the value in the series at the specified observation number.

Example:  At(sek, Date(2020, 12, 11))

Counter()

Returns a series of all observation numbers.

Example: It can be used as a 'pointer' in other formulas: if(Counter()=Date(2021, 6, 1), flagforecast(sek/2), sek)

Counter(series)

Returns a series of all observation number for the specified series. It starts from '0' so to get exact number of observations you have to add +1

Example: Counter(sek)+1

Date(year, month, day)

Returns the observation number of the specified date, where month is 1-12 and day is 1-31.

If there is no observation at the specified date, the observation number of the closest previous observation is returned.

You should generally not use this function to find the start of a year or month. Use the StartOfYear(year) and StartOfMonth(year, month) for this purpose.

Example: It can be used in other formulas At(sek, Date(2021, 6, 1))

DateAtOrAfter(year, month, day)

Returns the observation number of the specified date, where month is 1-12 and day is 1-31.

If there is no observation at the specified date, the observation number of the next observation is returned.

Example: DateAtOrAfter(2021, 4, 1)

Day()

Returns a series of the day (1-31) for each observation.

Example: Day()

Day(observation)

Returns the day (1-31) of the specified observation number.

Example: Day(Counter(sek))

DayOfWeek()

Returns a series of day of the week for each observation.

0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday

Example: DayofWeek()=3

DayOfWeek(observation)

Returns the day of the week of the specified observation number

0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday

Example: DayofWeek(Counter(sek))

Days(observation1, observation2)

Returns the number of calendar days passed from the first observation number to the second one.

Example: Days(StartOfMonth(), EndOfMonth())+1

Days30E(observation1, observation2)

Returns the number of days passed from the first observation number to the second using the 30E convention.

Example: Days30E(StartOfMonth(), EndOfMonth())+1

End()

Returns the last observation number for calculations.

Example: if(Counter()=End(), 1, 0)

End(series)

Returns the last observation number of the series.

Example: if(Counter()=End(sek), 1, 0)

EndOfMonth()

Returns a series with the last observation number during each month.

Example: if(Counter()=EndOfMonth(), 1, 0)

Returns a series with the last observation number during each month.

EndOfQuarter()

Returns a series with the last observation number during each quarter.

Example: if(Counter()=EndOfQuarter(), 1, 0)

Returns a series with the last observation number during each quarter.

EndOfWeek(firstDayOfWeek)

Returns a series with the last observation number during each week.

0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday

Example: Extend(sek, Last(EndOfWeek(3)), LastValid(sek))

Returns a series with the last observation number during each week.

0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday

EndOfYear()

Returns a series with the last observation number during each year.

Example: if(Counter()=EndOfYear(), 1, 0)

Returns a series with the last observation number during each year.

EndValid(series)

Returns the observation number of the last valid value of the series.

Example: if(Counter()=EndValid(sek), 1, 0)

FlagForecast(value)

Returns a value as a forecast value.

Example: FlagForecast(sek)

FlagForecast(value, condition)

Returns a value as a forecast value if the condition is True and as a non-forecast value if it is False.

Example: FlagForecast(sek, sek>9) all values higher than 9

Example: FlagForecast(sek, Counter()>Date(2020, 3, 1)) all values after 1st Mar 2020

Example: You can also turn off any forecast highlight: FlagForecast(unpoppr276constant, 0)

Returns a series with the observation numbers of IMM dates where an offset is added to each input date.

Returns the observation number of the IMM date where an offset is added to the input date that corresponds to the input observation number.

Length(series)

Returns the number of observations in a time series.

Example: Length(sek)

Month()

Returns a series of the month (1-12) for each observation.

Example: Month()

Example: if(Month()=2|Month()=6, 1, 0)

Month(observation)

Returns the month (1-12) of the specified observation number.

Example: Month(Counter(sek))

MonthLength()
Returns the number of observations per month based on the frequency.

Example: cop(sek, MonthLength()*3)

MonthsLength(months)
Returns the number of observations for a number of months based on the frequency.
MonthsLength(1) is the same as MonthLength()

Example: cop(sek, MonthsLength(3))

MonthOffset()

Returns a series with the offset of each observation within the month.

Example: At(Monthoffset(), EndofMonth())+1

MonthOffset(observation)

Returns the offset within the month of the specified observation number.

Example: MonthOffset(Counter(sek)+1)

NextValidObservationNumber(series)

Returns a series that contains the observation number of the current observation if it is valid and otherwise the number of the next observation that is valid

Example: High(sek, NextValidObservationNumber(sek))

NextValidObservationNumber(series, observation)

Returns the observation number of the specified observation if it is valid and otherwise the number of the next observation that is valid.

Now()

Returns the observation number of today's date.

Example: if(Counter()=Now()-1, flagforecast(sek), 0) will highlight yesterday's observation

ObservationCountPerYear()

Returns the number of observations per year based on the frequency.
This is a synonym for YearLength().

Example: cop(sek, ObservationCountPerYear())

PreviousValidObservationNumber(series)

Returns a series that contains the observation number of the current observation if it is valid (not null) and otherwise the number of the previous observation that is valid.

Example: High(sek, PreviousValidObservationNumber(sek)+1)

PreviousValidObservationNumber(series, observation)

Returns the observation number of the specified observation if it is valid and otherwise the number of the previous observation that is valid.

Quarter()

Returns a series of the quarter (1-4) for each observation.

Example: Quarter()

Example: if(Quarter()=2|Quarter()=3, 1, 0)

Quarter(observation)

Returns the quarter (1-4) of the specified observation number.

Example: Quarter(Counter(sek))

QuarterLength()
Returns the number of observations per quarter based on the frequency.

Example: cop(sek, QuarterLength()*2)

QuartersLength(quarters)
Returns the number of observations for a number of quarters based on the frequency.
QuartersLength(1) is the same as QuarterLength()

Example: cop(sek, QuartersLength(2))

QuarterOffset()

Returns a series with the offset of each observation within the quarter.

Example: At(Quarteroffset(), EndofQuarter())+1

QuarterOffset(observation)

Returns the offset within the quarter of the specified observation number.

Example: QuarterOffset(Counter(sek)+1)

Start()

Returns the first observation number for calculations.

Example: if(Counter()=Start(), 1, 0)

Start(series)

Returns the first observation number of the series.

Example: if(Counter()=Start(sek), 1, 0)

StartValid(series)

Returns the observation number of the first valid value of the series.

Example: if(Counter()=StartValid(sek), 1, 0)

StartOfYear()

Returns a series with the first observation number during each year.

Example: if(Counter()=StartofYear(), 1, 0)

Example: StartofYear()

Example: At(sek, StartofYear()) to get a value

StartOfYear(year)

Returns the observation number of the first observation of the specified year.

Example: StartofYear(2020)

Example: At(sek, StartofYear(2020)) to get a value

StartOfQuarter()

Returns a series with the first observation number during each quarter.

Example: if(Counter()=StartofQuarter(), 1, 0)

StartOfMonth()

Returns a series with the first observation number during each month.

Example: if(Counter()=StartofMonth(), 1, 0)

StartOfMonth(year, month)

Returns the observation number of the first observation of the specified month, where the month is in the range 1-12.

Example: if(Counter()=StartofMonth(2021, 1), 1, 0)

StartOfWeek(firstDayOfWeek)

Returns a series with the first observation number during each week.

0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday

Example: At(sek, StartOfWeek(3))

WeekLength()
Returns the number of observations per week based on the frequency.

Example: cop(sek, WeekLength()*2)

WeeksLength(weeks)
Returns the number of observations for a number of weeks based on the frequency.
WeeksLength(1) is the same as WeekLength()

Example: cop(sek, WeekLength(2))

Year()

Returns a series of the year for each observation.

Example: if(Year()=2020, series/2, series)

Year(observation)

Returns the year of the specified observation number.

Example: Year()

Example: Year(Counter(sek))

YearLength()
Returns the number of observations per year based on the frequency.
This is a synonym for ObservationCountPerYear().

Example: cop(sek, YearLength()*5)

YearsLength(years)
Returns the number of observations for a number of years based on the frequency.
YearsLength(1) is the same as YearLength().

Example: cop(sek, YearLength(5))

YearOffset()

Returns a series with the offset of each observation within the year.

Example: ceiling((YearOffset()+1)/WeekLength())-1 it counts weeks since the start of year

Example: At(Yearoffset(), EndofYear())+1

YearOffset(observation)

Returns the offset within the year of the specified observation number.

Example: YearOffset(Counter(sek)+1)

Years(observation1, observation2)

Returns the number of years between the first observation number to the second. The calculation is made using the Actual/Actual method defined by ISDA.

Example: Years(Start(), End())

Example: Years(Start(sek), End(sek))

Series operations

Cut(series, observationStart, observationEnd)

Returns a series excluding observations before the specified start observation number and all observations starting with the specified end observation number.

Example: Cut(sek, Date(2010,01,01), Date(2021,01,01))

CutEnd(series, observation)

Returns a series of all observations before the specified observation number.

Example: CutEnd(sek, Date(2021,03,12))

CutStart(series, observation)

Returns a series without any observations before the specified observation number.

Example: CutStart(sek, Date(2020,01,01))

Extend(series, observation, number)

Returns a series with the specified number at the specified observation number if it is past the end.
Any values between the last value of the series and the specified future point of extension is filled in with the last value.

Example: To extend series two months after its original end (second parameter), with a 0.75 value at the end (third parameter): Extend(usrate0001, Endvalid(usrate0001)+Monthslength(2), 0.75)

ExtendLastAsForecast(series)

Extends the series with the last valid value until the end of the calendar.

Example: add some other series, i.e., usrate0001 and below ExtendLastAsForecast(nolama0138)

This is implemented in the Macrobond formula language as:

ExtendLastAsForecast(series) = Join(series, FlagForecast(LastValid(series)), EndValid(series) + 1)

ExtendLastYoYForecast(series) / ExtendLastYoYForecast(series, observation)

Extends the series using the % YoY change to calculate a forecast for the whole calendar range.

Example: ExtendLastYoYForecast(CutEnd(usgdp, Date(2020, 1, 1)))

Example: ExtendLastYoYForecast(usgdp, Endvalid(usgdp)+Quarterslength(3))

ExtendLinear(series, observation, number)

Returns a series extended with the specified number using a linear interpolation from the end until the specified observation number.

Example: When you want to put at the end of the current year (second parameter) last value higher by 5% (third parameter): ExtendLinear(sp500_500, LastValid(EndOfYearAhead(0)), LastValid(sp500_500)*1.05)

ExtendToEndOfMonth(series)

Extends the series with the last value until the end of the month.

Example: ExtendToEndOfMonth(sek)

This is implemented in the Macrobond formula language as:

ExtendToEndOfMonth(series) = Extend(Trim(series), At(EndOfMonth(), EndValid(series)), LastValid(series)

ExtendToEndOfQuarter(series)

Extends the series with the last value until the end of the quarter.

Example: ExtendToEndOfQuarter(sek)

This is implemented in the Macrobond formula language as:

ExtendToEndOfQuarter(series) = Extend(Trim(series), At(EndOfQuarter(), EndValid(series)), LastValid(series))

ExtendToEndOfWeek(firstDayOfWeek, series)

Extends the series with the last value until the end of the week.

0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday

Example: ExtendToEndOfWeek(4, sek)

This is implemented in the Macrobond formula language as:

ExtendToEndOfWeek(firstDayOfWeek, series) = Extend(Trim(series), At(EndOfWeek(firstDayOfWeek), EndValid(series)), LastValid(series))

ExtendToEndOfYear(series)

Extends the series with the last value until the end of the year.

Example: ExtendToEndOfYear(sek)

This is implemented in the Macrobond formula language as:

ExtendToEndOfYear(series) = Extend(Trim(series), At(EndOfYear(), EndValid(series)), LastValid(series))

Join(series1, series2)

Returns a series that joins two series at the end of series1.

Example:  join(cncons5420_old2015, cncons5420)

This is implemented in the Macrobond formula language as:

Join(series1, series2) = Join(series1, series2, EndValid(series1)+1)

Join(series1, series2, observation)

Returns a series that joins two series at the specified observation number.

Example:  join(cncons5420_old2015, cncons5420, Date(2020, 1, 1))

JoinScaled(series1, series2)

Returns a series that joins two series at the end of the series1 and scales the first series. Series need to overlap.

Example:  JoinScaled(fisurv0005, fisurv0716)

This is implemented in the Macrobond formula language as:

JoinScaled(series1, series2) = JoinScaled(series1, series2, EndValid(series1))

JoinScaled(series1, series2, observation)

Returns a series that joins two series at the specified observation number and scales the first series. Series need to overlap.

Example:  JoinScaled(fisurv0005, fisurv0716, Date(2019, 1, 1))

JoieScaledAppend(series 1, series 2) / JoinScaledAppend(series1, series2, observation)

Returns a series that joins two series at the end of the series1 and scales the second series.

Example:  JoinScaledAppend(fisurv0005, fisurv0716)

Example:  JoinScaledAppend(fisurv0005, fisurv0716, Date(2019, 1, 1))

JoinMoreHistory(series1, series2)

Returns a series of all observations of series1 complemented with all values of series2 that are before the start of series1.

Example:  JoinMoreHistory(ngprod0001, imf_q69466___zf)

This is implemented in the Macrobond formula language as:

JoinMoreHistory(series1, series2) = Join(series2, series1, StartValid(series1))

JoinMoreHistoryScaled(series1, series2)

Returns a series of all observations of series1 complemented with all values of series2 that are before the start of series1. The values of series2 are scaled so that they are equal at the beginning of series1. Series need to overlap.

Example:  JoinMoreHistoryScaled(ngprod0001, imf_q69466___zf)

This is implemented in the Macrobond formula language as:

JoinMoreHistoryScaled(series1, series2) = JoinScaled(series2, series1, StartValid(series1))

Lag(series, length)

Returns the series lagged by the specified length. The length is rounded to an integer.

Example: Lag(plgdp, -5) data moved 5 observations into the past

Example: Lag(plgdp, Quarterslength(5)) data moved 5 quarters into the future

Trim(series)

Returns a series where any null values at the start or end have been excluded.

Example: Trim(sek)

Logical functions

Count(series)

Returns the number of values that are True.

Example: Count(sp500_500)

Count(series, window)

Returns the number of values that are True in a window of the specified length.

Example: Count(sp500_500, Monthslength(6))

If(condition, value1, value2)

Returns the first value if condition is True and the second value if it is False.

Example: If value is higher than 8, draw 1, otherwise don't draw anything: If(sek > 8,1, Null())

IsForecast(value)

Returns True if a value is a forecast and otherwise False.

Example: IsForecast(unpoppr276constant)

IsNull(value)

Returns True if a value is null and otherwise False.

Example: isNull(sp600_20201080tr)

Example: !isNull(sp600_20201080tr) ! sign is a logical “not”

RunLength(series)

Counts how many observations in a row that have been True.

Example: runlength(sp500_500)

Example: RunLength(Counter()>EndValid(sek)-34) it assigns the number 34 to the last observation, 33 to the second last, etc.

This is implemented in the Macrobond formula language as:

RunLength(series) =
let
.a = AggregateSum(if (series, 1, 0))
.s = Null0(series)
.c = FillNullWithPrevious(if (!.s & lag(.s, 1), .a, null()))
in
if (IsNull(series), Null(), .a - Null0(.c))
end

Financial

BollingerLower(series, nStdDev, window)

Returns the lower Bollinger band using a window of the specified length and number of standard deviations.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Example: BollingerLower(sek, 2, MonthsLength(12))

This is implemented in the Macrobond formula language as:

BollingerLower(series, nStdDev, window) = Mean(series, window)-nStdDev*Sqrt(Variance(series, window))

BollingerUpper(series, nStdDev, window)

Returns the upper Bollinger band using a window of the specified length and number of standard deviations.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Example: BollingerUpper(sek, 2, MonthsLength(12))

This is implemented in the Macrobond formula language as:

BollingerUpper(series, nStdDev, window) = Mean(series, window)+nStdDev*Sqrt(Variance(series, window))

COP(series, length)

Returns the percentage change over the number of specified observations.
This is the same calculation as is done in the method "Change over period %" in the Rate of change analysis.

Example:  Cop(usnaac0673, YearsLenght(1))

This is implemented in the Macrobond formula language as:

COP(series, length) =
let
.lagged = lag(series, length)
in
CutEnd((series-.lagged)/Abs(.lagged)*100, End(series)+1)
end

Drawdown(series)

This calculates the peak-to-trough decline [as 100*(peak - value)/peak] since the last peak or since the first value. The output is expressed as “positive” percentage: 50 should be interpreted as a 50% decline since the last peak.

Example:  Drawdown(sp500_500)

FMACD(series, short, long)

Returns the fast moving average convergence divergence.

Example:  FMACD(standardize(sek), 13, 21)

InvestmentStrategy(marketValue, conditionWhenInMarket)

Calculate the value of an investment by compounding the returns of the marketValue series during the periods when the conditionWhenInMarket is True. When out of the market, the value is not changed.

Example: Invest in S&P 500 only during the first six months each year: InvestmentStrategy(sp500_500tr, Month() <= 6)

This is implemented in the Macrobond formula language as:

InvestmentStrategy(marketValue, conditionWhenInMarket) =
let
.ReturnWhenInMarket = if (Lag(conditionWhenInMarket, 1), COP(marketValue, 1)/100+1, 1)
in
AggregateProduct(.ReturnWhenInMarket)
end

Calculate the value of an investment by compounding the returns of the marketValue series during the periods between when the buyCondition becomes True until the sellCondition becomes True. When out of the market, the value is not changed.

Example: Invest in S&P when it is 10% over the mean of the last 200 observations and sell when it is 10% below the mean: InvestmentStrategy(sp500_500tr, sp500_500tr > Mean(sp500_500tr, 200)*1.1, sp500_500tr < Mean(sp500_500tr, 200)*0.9)

This is implemented in the Macrobond formula language as:

let
.InMarketPeriods = Signal(buyCondition, sellCondition)              // 1 = invested, 0 = not invested
.ReturnWhenInMarket = if (Lag(.InMarketPeriods, 1), COP(marketValue, 1)/100+1, 1)
in
AggregateProduct(.ReturnWhenInMarket)
end

Calculate the value of an investment by compounding the returns of the marketValue series during the periods between when the buyCondition becomes True until the sellCondition becomes True

Example: Invest in S&P when it is 10% over the mean of the last 200 observations, sell when it is 10% below the mean and invest in a bond index when out of the market: InvestmentStrategy(sp500_500tr, djcfin30tr, sp500_500tr > Mean(sp500_500tr, 200)*1.1, sp500_500tr < Mean(sp500_500tr, 200)*0.9)

This is implemented in the Macrobond formula language as:

let
.InMarketPeriods = Signal(buyCondition, sellCondition)              // 1 = invested, 0 = not invested
.ReturnWhenInMarket = if (Lag(.InMarketPeriods, 1), COP(inMarketValue, 1), COP(outOfMarketValue, 1))/100+1
in
AggregateProduct(.ReturnWhenInMarket)
end

Rsi(series, window)

Returns the relative strength index of the series using a window of the specified length.
The relative strength is calculated by the method described by J. Welles Wilder 1978 and uses an exponential smoothing.

At the start of the series, before there are enough values to cover a full window, the calculation will be made on fewer observations than specified by the window parameter. This is also the case if there are missing values.

Example: rsi(sp500_500, 25)

Signal(onCondition, offCondition)

Calculate a signal based on conditions when the signal is turned on (1) and when it is turned off (0).

Example:  Signal(sek>8, sek<8)

This is implemented in the Macrobond formula language as:

Signal(onCondition, offCondition) =
let
.OnAndOff = if (offCondition, 0, if (onCondition, 1, null())) // 1 = signal, 0 = no signal
in
FillNullWithPrevious(.OnAndOff) // 1 = signal, 0 = no signal
end

SMACD(series, short, long, signal)

Returns the exponential average of the fast moving average convergence divergence.

Example: SMACD(standardize(sek), 13, 14, 2)

This is implemented in the Macrobond formula language as:

SMACD(series, short, long, signal) = EMean(FMACD(series, short, long),2/(signal+1))

TotalReturnFromShortYield(yieldSeries, maturityInYears)

Estimates the total return from yield when there are no coupons using Actual/360 day count convention.

Example:  TotalReturnFromShortYield(us3mgov, 0.25)

This is implemented in the Macrobond formula language as:

TotalReturnFromShortYield(yieldSeries, maturityInYears) = AggregateProduct((yieldSeries*Days(Counter()-1, Counter())/360 - Momentum(yieldSeries, 1)*maturityInYears)/100 + 1)

TotalReturnFromYield(yieldSeries, duration)

Estimates the total return from yield given the duration.

Example:  TotalReturnFromYield(us10ygov, 10)

This is implemented in the Macrobond formula language as:

TotalReturnFromYield(yieldSeries, duration) = AggregateProduct((yieldSeries/ObservationCountPerYear()-Momentum(yieldSeries, 1) * duration ) / 100 + 1)