Excel Equivalent Formulas - Anaplan Technical Documentation

Excel Function

Anaplan Formula

YEARFRAC

tested to 3 decimal places

Create three line items

Two date-formatted line items for the two dates End Date & Start Date
One number-formatted line item with the formula

To replicate US (NASD) 30/360 (Basis 0)

(YEAR(End Date) - YEAR(Start Date) + 30 * (MONTH(End Date) - MONTH(Start Date)) + IF DAY(Start Date) > 29 AND DAY(End Date) > 30 THEN 30 ELSE IF MONTH(Start Date) = 2 AND DAY(Start Date) = 28 AND DAY(End Date) = 28 THEN 30 ELSE DAY(End Date) - IF DAY(Start Date) = 31 THEN 30 ELSE IF MONTH(Start Date) = 2 AND DAY(Start Date) = 28 THEN 30 ELSE DAY(Start Date)) / 360

To replicate Actual Days/Actual of days in year (Basis 1)

No of Days * 360 / 365 / 360 (Accurate to 2 decimal places)

To replicate Actual Days/360 days in year (Basis 2)

(End Date - Start Date) / 360

To replicate Actual Days/365 in year (Basis 3)

(End Date - Start Date) / 365

To replicate European 30/360 (Basis 4)

((MONTH(End Date) - MONTH(Start Date) - 1) * 30 + 30 - (IF DAY(Start Date) < 30 THEN DAY(Start Date) ELSE 30) + IF DAY(End Date) < 30 THEN DAY(End Date) ELSE 30 + (YEAR(End Date) - YEAR(Start Date)) * 12 * 30) / 360

NETWORKDAYS

Create four line items

Two date-formatted line items for the two dates -End Date & Start Date;
One number-formatted line item for an integer representing the total days holiday - Holidays

Create fourth number-formatted line item with the formula.

End Date - Start Date + 1 - (IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0) - (IF WEEKDAY(End Date) > 5 THEN WEEKDAY(End Date) - 5 ELSE 0) - (End Date - Start Date + 1 - (IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0) - (IF WEEKDAY(End Date) > 5 THEN WEEKDAY(End Date) - 5 ELSE 0) - (IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0) - MOD(End Date - Start Date + 1 - (IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0) - (IF WEEKDAY(End Date) > 5 THEN WEEKDAY(End Date) - 5 ELSE 0) - IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0, 7)) / 7 * 2 - Holidays

MMULT

Create three lists for the matrix dimensions: I, J, K.

Add a parent item in list K to sum intermediate products.

Create two modules Matrix A and Matrix B to use as the input matrices.

Configure Matrix A with one line item (Value) set as Pages and the lists I set as Rows and & K as Columns.
Configure Matrix B with one line item (Value) set as Pages and the lists K set as Rows and & J as Columns—the column count of Matrix B must match the row count of Matrix A.

Create a module Matrix C as the output matrix.

Configure Matrix C with one line item (Output) as Pages and the list I as Row, and lists J & K as Columns. In the module, hide every occurrence of the K dimension, except the parent (Sum) column.

The formula for the line item (Output) in Matrix C is

Matrix A.Value * Matrix B.Value

Excel Function	Anaplan Formula
YEARFRAC tested to 3 decimal places	Create three line items Two date-formatted line items for the two dates End Date & Start Date One number-formatted line item with the formula To replicate US (NASD) 30/360 (Basis 0) (YEAR(End Date) - YEAR(Start Date) + 30 * (MONTH(End Date) - MONTH(Start Date)) + IF DAY(Start Date) > 29 AND DAY(End Date) > 30 THEN 30 ELSE IF MONTH(Start Date) = 2 AND DAY(Start Date) = 28 AND DAY(End Date) = 28 THEN 30 ELSE DAY(End Date) - IF DAY(Start Date) = 31 THEN 30 ELSE IF MONTH(Start Date) = 2 AND DAY(Start Date) = 28 THEN 30 ELSE DAY(Start Date)) / 360 To replicate Actual Days/Actual of days in year (Basis 1) No of Days * 360 / 365 / 360 (Accurate to 2 decimal places) To replicate Actual Days/360 days in year (Basis 2) (End Date - Start Date) / 360 To replicate Actual Days/365 in year (Basis 3) (End Date - Start Date) / 365 To replicate European 30/360 (Basis 4) ((MONTH(End Date) - MONTH(Start Date) - 1) * 30 + 30 - (IF DAY(Start Date) < 30 THEN DAY(Start Date) ELSE 30) + IF DAY(End Date) < 30 THEN DAY(End Date) ELSE 30 + (YEAR(End Date) - YEAR(Start Date)) * 12 * 30) / 360
NETWORKDAYS	Create four line items Two date-formatted line items for the two dates -End Date & Start Date; One number-formatted line item for an integer representing the total days holiday - Holidays Create fourth number-formatted line item with the formula. End Date - Start Date + 1 - (IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0) - (IF WEEKDAY(End Date) > 5 THEN WEEKDAY(End Date) - 5 ELSE 0) - (End Date - Start Date + 1 - (IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0) - (IF WEEKDAY(End Date) > 5 THEN WEEKDAY(End Date) - 5 ELSE 0) - (IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0) - MOD(End Date - Start Date + 1 - (IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0) - (IF WEEKDAY(End Date) > 5 THEN WEEKDAY(End Date) - 5 ELSE 0) - IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0, 7)) / 7 * 2 - Holidays
MMULT	Create three lists for the matrix dimensions: I, J, K. Add a parent item in list K to sum intermediate products. Create two modules Matrix A and Matrix B to use as the input matrices. Configure Matrix A with one line item (Value) set as Pages and the lists I set as Rows and & K as Columns. Configure Matrix B with one line item (Value) set as Pages and the lists K set as Rows and & J as Columns—the column count of Matrix B must match the row count of Matrix A. Create a module Matrix C as the output matrix. Configure Matrix C with one line item (Output) as Pages and the list I as Row, and lists J & K as Columns. In the module, hide every occurrence of the K dimension, except the parent (Sum) column. The formula for the line item (Output) in Matrix C is Matrix A.Value * Matrix B.Value