# Lumonic Excel Add-in Documentation

## **Getting Started**

[Use this link](https://marketplace.microsoft.com/en-us/product/office/wa200006487?tab=overview) to find Lumonic add-in

#### Building Formulas

Use the sidebar included in the add-in custom tab under Lumonic tab. The sidebar provides tools to quickly look up entities and build Lx expressions.

![](https://downloads.intercomcdn.com/i/o/y9cw1kvx/1592408205/985e0a4cf6ce81aab5491c4f4df9/Building+Formulas+1.jpg?expires=1791840600&signature=ef7221807f14dcf2a2b318772f211c516ada225f0b7ffa0cfe83c0d9a37c510a&req=dSUuFM1%2BlYNfXPMW1HO4zTkUxvduYxExrtr6Hoxfl1iDxziX7nC1TlltIZ4E%0AYrGlAgev7WRWzuL%2BbzQ%3D%0A)

![](https://downloads.intercomcdn.com/i/o/y9cw1kvx/1592408206/28cc1075c50e42d79463a477529e/Building+Formulas+2.jpg?expires=1791936000&signature=8015cd3aa9b823060c694191b6629e35b8137bfa24ffa214b609d83616c09325&req=dSUuFM1%2BlYNfX%2FMW3Hu4gRrTIdwE4TqVWpuhVHs%2BQ6qdU0j03kpXKl1QV%2Fm0%0AOw%3D%3D%0A)

#### Refreshing Data On Demand

Use the Refresh command menu to select the appropriate action for your use case:

- **Worksheet** will refresh each LX.* formula on the current worksheet

- **Selection** will refresh LX.* formulas only in currently selected cells

We recommend refreshing smaller selections for data intensive worksheets.

![](https://downloads.intercomcdn.com/i/o/y9cw1kvx/1592408579/ae808303ebd8101db9585e0325f0/Refreshing+Data+On+Demand+.jpg?expires=1791840600&signature=3d1e3cb6288ee58ae9acef99d6ed15e24d1a906149a54555912db1a01478f65b&req=dSUuFM1%2BlYRYUPMW1HO4zZmsjQOGsvwTHmQoMtdotAfsDHwW9ps6fuOw47o%2B%0Ak3wxsIJ%2BMNUClVphNQA%3D%0A)

---

## **Entity Formulas (LX.ENTITY.*)**

Entity formulas query data for specific entities using their unique IDs.

## **LX.ENTITY.GET**

Query an entity for a specific date and Lx expression.

#### Return:

A single data point (string, number, or date)

#### Arguments:

- `entityId` *required[string]* – Entity ID to query

- `lx` *required[string]* – Lx expression to evaluate

- `effectiveDate` *required[date]* – Date for evaluation

#### Examples:

```
=LX.ENTITY.GET("company_abc123", "@company.revenue", "2024-01-01")
=LX.ENTITY.GET("security_xyz789", "@security.amount", TODAY())
=LX.ENTITY.GET("fund_def456", "@fund.nav", "2024-12-31")
```

## **LX.ENTITY.DATE**

Retrieve the latest or earliest date for an entity based on an Lx expression.

#### Return:

Excel date number

#### Arguments:

- `entityId` *required[string]* – Entity ID to query

- `lx` *required[string]* – Lx expression to evaluate

- `dateType` *optional[string]* – "latest" (default) or "earliest"

#### Examples:

```
=LX.ENTITY.DATE("company_abc123", "@company.revenue", "latest")
=LX.ENTITY.DATE("security_xyz789", "@security.valuation", "earliest")
```

## **LX.ENTITY.AGG**

Aggregate values from related entities using an Lx expression.

#### Return:

Aggregated scalar value (number or string)

#### Arguments:

- `entityId` *required[string]* – Parent entity ID

- `lx` *required[string]* – Lx expression to evaluate on related entities

- `effectiveDate` *required[date]* – Date for evaluation

- `aggMethod` *required[string]* – Aggregation method: SUM, AVERAGE, MIN, MAX, COUNT, FIRST, ALL, ANY

- `filters` *optional[string]* – Filter JSON from LX.FILTER() or LX.FILTER.CHAIN()

- `options` *optional[string]* – Reserved for future use

#### Examples:

```
// Sum revenue from all subsidiaries
=LX.ENTITY.AGG("company123", "@company.revenue", "2024-01-01", "SUM")

// Average revenue from active subsidiaries only
=LX.ENTITY.AGG("company123", "@company.revenue", "2024-01-01", "AVERAGE",
  LX.FILTER("@company.status = Active", "EXACT", "2024-01-01"))

// Count securities with amount > 50000
=LX.ENTITY.AGG("company123", "@security.amount", "2024-01-01", "COUNT",
  LX.FILTER("@security.amount > 50000", "EXACT", "2024-01-01"))
```

---

## **Namespace Formulas (LX.NAMESPACE.*)**

Namespace formulas work with collections of entities within a specific namespace (entity type).

## **LX.NAMESPACE.LIST**

Return a list of entities within a namespace with optional filtering and formatting.

#### Return:

Dynamic 2D array of entity data

#### Arguments:

- `namespace` *required[string]* – Namespace name (e.g., "Company", "Fund", "Security")

- `filters` *optional[string]* – Filter JSON from LX.FILTER() or LX.FILTER.CHAIN()

- `fields` *optional[string]* – Comma-separated field list (default: "name,id")

- `sortBy` *optional[string]* – Sort field, prefix with "-" for descending

- `limit` *optional[number]* – Maximum results to return

- `transpose` *optional[boolean]* – Transpose results (default: false)

#### Examples:

```
// Simple list with default fields
=LX.NAMESPACE.LIST("Company")

// List with single filter
=LX.NAMESPACE.LIST("Company", LX.FILTER("@company.revenue > 1000000", "ANY", "2023-01-01:2024-12-31"))

// Complex list with custom fields and sorting
=LX.NAMESPACE.LIST("Company",
  LX.FILTER.CHAIN("AND",
    LX.FILTER("@company.revenue > 50000", "ANY", "2023-01-01:2024-12-31"),
    LX.FILTER("@company.status = Active", "EXACT", "2024-01-01")
  ),
  "name,id,revenue,status",
  "-revenue",
  50)
```

## **LX.NAMESPACE.AGG**

Aggregate values from child entities across multiple parent entities in a namespace.

#### Return:

Aggregated scalar value (number or string)

#### Arguments:

- `namespace` *required[string]* – Parent entities' namespace

- `parentFilter` *optional[string]* – Filter for parent entities

- `lx` *required[string]* – Lx expression to evaluate on child entities

- `effectiveDate` *required[date]* – Date for evaluation

- `aggMethod` *required[string]* – Aggregation method: SUM, AVERAGE, MIN, MAX, COUNT, FIRST, ALL, ANY

- `childFilters` *optional[string]* – Filter for child entities

- `options` *optional[string]* – Reserved for future use

#### Examples:

```
// Sum revenue from all securities across all active companies
=LX.NAMESPACE.AGG("Company",
  LX.FILTER("@company.status = Active", "EXACT", "2024-01-01"),
  "@security.revenue", "2024-01-01", "SUM")

// Average valuation of securities > 50000 across all companies
=LX.NAMESPACE.AGG("Company", , "@security.valuation", "2024-01-01", "AVERAGE",
  LX.FILTER("@security.amount > 50000", "EXACT", "2024-01-01"))

// Complex example with chained filters for both parents and children
=LX.NAMESPACE.AGG("Company",
  LX.FILTER.CHAIN("AND",
    LX.FILTER("@company.revenue > 100000", "ANY", "2023-01-01:2024-12-31"),
    LX.FILTER("@company.status = Active", "EXACT", "2024-01-01")
  ),
  "@security.amount", "2024-01-01", "SUM",
  LX.FILTER.CHAIN("OR",
    LX.FILTER("@security.type = Equity", "EXACT", "2024-01-01"),
    LX.FILTER("@security.type = Debt", "EXACT", "2024-01-01")
  ))
```

---

## **Relationship Formulas (LX.RELATIONSHIP.*)**

Relationship formulas work with entity relationships and connections.

## **LX.RELATIONSHIP.LIST**

Return a list of child or related entities for a given parent entity.

#### Return:

Dynamic 2D array of related entity data

#### Arguments:

- `entityId` *required[string]* – Parent entity ID

- `relatedNamespace` *required[string]* – Child entity type (e.g., "Security", "Company")

- `filters` *optional[string]* – Filter JSON for child entities

- `fields` *optional[string]* – Comma-separated field list (default: "name,id")

- `sortBy` *optional[string]* – Sort field, prefix with "-" for descending

- `limit` *optional[number]* – Maximum results to return

- `transpose` *optional[boolean]* – Transpose results (default: false)

#### Examples:

```
// Simple list of securities for a company
=LX.RELATIONSHIP.LIST("company123", "Security")

// Filtered list with custom fields
=LX.RELATIONSHIP.LIST("company123", "Security",
  LX.FILTER("@security.status != Paid Off", "ANY", "2023-01-01:2024-12-31"),
  "name,id,amount,status",
  "-amount",
  50)
```

---

## **Covenant Formulas (LX.COVENANT.*)**

Covenant formulas work with covenant tracking and compliance data.

## **LX.COVENANT.LIST**

Return a list of covenant fields tracked by an entity.

#### Return:

Dynamic 2D array of covenant field data

#### Arguments:

- `entityId` *required[string]* – Entity ID to query

- `fields` *optional[string]* – Field options: "name", "pointer", or "name,pointer" (default: both)

- `sortBy` *optional[string]* – Sort by "name" or "status", prefix with "-" for descending

- `limit` *optional[number]* – Maximum covenant to return

- `transpose` *optional[boolean]* – Transpose results (default: false)

#### Examples:

```
// Simple list with default fields
=LX.COVENANT.LIST("entity123")

// List with only names, sorted by covenant name
=LX.COVENANT.LIST("entity123", "name", "name")

// List sorted by compliance status (out of compliance first)
=LX.COVENANT.LIST("entity123", , "-status")
```

---

# Performance Formulas (LX.PERFORMANCE.*)

Performance formulas calculate cashflow-based performance metrics (IRR, DPI, TVPI, etc.) using the CashflowPerformanceEngine for mathematically correct aggregation.

## LX.PERFORMANCE.GET

Get a cashflow performance metric for a specific entity.

**Return**:

Single performance metric value (number or string)

**Arguments**:

- `entityId` *required[string]* – Lumonic ID of the entity

- `lx` *required[string]* – Performance metric Lx expression (e.g., "@Slack Security.poc-irr", "@company.dpi")

- `effectiveDate` *required[date]* – Date for cumulative calculation

- `ledger `*required[string]* – Ledger name (user-defined, e.g., "GAAP", "Tax")

- `leverageScenario` *required[string]* – "levered" or "unlevered"

- `financialScenario` *required[string]* – Financial scenario name (user-defined, e.g., "Base")

- `filters` *optional[string]* – Filter JSON from LX.FILTER() to filter underlying entities

**Examples**:

```
// Get IRR for a specific security
=LX.PERFORMANCE.GET("security_abc123", "@Slack Security.poc-irr", "2024-12-01", "GAAP", "levered", "Base")

// Get DPI for a company with filters
=LX.PERFORMANCE.GET("company_xyz789", "@company.dpi", "2024-12-01",
"GAAP", "unlevered", "Base", LX.FILTER("@security.status = Active", "EXACT", "2024-12-01"))
```

```
// Get DPI for a company with filters
=LX.PERFORMANCE.GET("company_xyz789", "@company.dpi", "2024-12-01", "GAAP", "unlevered", "Base",  LX.FILTER("@security.status = Active", "EXACT", "2024-12-01"))
```

```
// Get TVPI for a fund
=LX.PERFORMANCE.GET("fund_def456", "@fund.tvpi", TODAY(), "Tax","levered", "Base")
```

---

# LX.PERFORMANCE.LIST

Get cashflow performance metrics, optionally grouped by a property expression.

**Return**:

Dynamic 2D array of [group_value, metric_value] pairs (or [["Total", value]] if no groupBy)

​**Arguments**:

- `lx` *required[string]* – Performance metric Lx expression (namespace parsed from expression)

- `effectiveDate` *required[date]* – Date for cumulative calculation

- `ledger` *required[string]* – Ledger name (user-defined)

- `leverageScenario` *required[string]* – "levered" or "unlevered"

- `financialScenario` *required[string]* – Financial scenario name (user-defined)

- `groupBy` *optional[string]* – Grouping expression via LX.GROUP() (e.g., "@fund.name")

- `filters` *optional[string]* – Filter JSON from LX.FILTER()

**Examples:**

```
// Get total IRR across all securities (no grouping)
=LX.PERFORMANCE.LIST("@security.poc-irr", "2024-12-01", "GAAP", "levered", "Base")

// Group IRR by fund name
=LX.PERFORMANCE.LIST("@security.poc-irr", "2024-12-01", "GAAP", "levered", "Base", LX.GROUP("@fund.name"))

// Group DPI by industry with filters
=LX.PERFORMANCE.LIST("@company.dpi", "2024-12-01", "GAAP", "unlevered", "Base", LX.GROUP("@company.industry"),LX.FILTER("@company.status = Active", "EXACT", "2024-12-01"))

```

---

## LX.GROUP

Create a grouping specification for use with performance functions.

**Return**: JSON grouping specification string

**Arguments**:

- `expression` *required[string]* – Lx expression to group by (must start with @)

**Examples:**

```
// Group by fund name
=LX.GROUP("@fund.name")

// Group by company industry
=LX.GROUP("@company.industry")

// Use in PERFORMANCE.LIST
=LX.PERFORMANCE.LIST("@security.poc-irr", "2024-12-01", "GAAP", "levered", "Base",LX.GROUP("@fund.name"))
```

---

# Performance Analysis

```
// Calculate fund-level IRR grouped by fund
=LX.PERFORMANCE.LIST("@security.poc-irr", "2024-12-31", "GAAP", "levered", "Base",LX.GROUP("@fund.name"))

// Get portfolio DPI with active securities only                                                                                                                                                             =LX.PERFORMANCE.GET("portfolio123", "@portfolio.dpi", "2024-12-31", "GAAP", "levered", "Base", LX.FILTER("@security.status = Active", "EXACT", "2024-12-31"))

// Compare performance across industries                                                                                                                                                                     =LX.PERFORMANCE.LIST("@company.tvpi", "2024-12-31", "Tax", "unlevered", "Base", LX.GROUP("@company.industry"), LX.FILTER("@company.revenue > 1000000"))

// Get IRR for specific fund excluding certain deal types                                                                                                                                                    =LX.PERFORMANCE.GET("fund_abc", "@fund.irr", "2024-12-31", "GAAP", "levered", "Base", LX.FILTER.CHAIN("AND", LX.FILTER("@security.status != Written Off", "EXACT", "2024-12-31"), LX.FILTER("@company.deal_type != Bridge", "EXACT", "2024-12-31")))
```

---

## **Filter Functions (LX.FILTER.*)**

Filter functions create filter conditions for use with other formulas.

## **LX.FILTER**

Create a single filter condition for entity queries.

#### Return:

JSON filter string

#### Arguments:

- `condition` *required[string]* – Lx expression with @ prefixes

- `strategy` *optional[string]* – Filter strategy: "EXACT", "ANY", or "ALL" (default: ANY with 12-month lookback)

- `dates` *optional[string]* – Date specification (default: 12-month lookback from today)

#### Examples:

```
// Simple condition check (uses ANY strategy with 12-month lookback)
=LX.FILTER("@company.revenue > 1000000")

// Exact date filter
=LX.FILTER("@company.revenue > 1000000", "EXACT", "2024-01-01")

// Any date in range
=LX.FILTER("@company.status = Active", "ANY", "2023-01-01:2024-12-31")

// All dates in range must match
=LX.FILTER("@company.deal_type = Venture", "ALL", "2023-01-01:2024-12-31")
```

## **LX.FILTER.CHAIN**

Chain multiple filter conditions with AND/OR logic.

#### Return:

JSON filter chain string

#### Arguments:

- `operation` *required[string]* – Logical operation: "AND" or "OR"

- `filter1` *required[string]* – First filter JSON from LX.FILTER()

- `filter2` *required[string]* – Second filter JSON from LX.FILTER()

- `filter3-10` *optional[string]* – Additional filters (up to 10 total)

#### Examples:

```
// AND multiple conditions
=LX.FILTER.CHAIN("AND",
  LX.FILTER("@company.revenue > 1000000", "ANY", "2023-01-01:2024-12-31"),
  LX.FILTER("@company.deal_type = Venture", "EXACT", "2024-01-01"))

// OR multiple conditions
=LX.FILTER.CHAIN("OR",
  LX.FILTER("@company.status = Active", "EXACT", "2024-01-01"),
  LX.FILTER("@company.status = Pending", "EXACT", "2024-01-01"))

// Using default 12-month lookback filters
=LX.FILTER.CHAIN("AND",
  LX.FILTER("@company.revenue > 1000000"),
  LX.FILTER("@company.employees > 50"))
```

---

## **Filter Strategies**

#### Default (12-Month Lookback)

- When no strategy or dates are provided

- Uses ANY strategy with a 12-month date range from today

- Checks if the condition was met on any date in the past 12 months

- Best for current state queries: LX.FILTER("@company.revenue > 1000000")

#### EXACT Strategy

- Use with single dates: "2024-01-01"

- Entity must have data on the exact specified date

- Best for point-in-time queries

#### ANY Strategy

- Use with date ranges: "2023-01-01:2024-12-31"

- Entity must match condition on at least one date in the range

- Best for "has ever been" type queries

#### ALL Strategy

- Use with date ranges: "2023-01-01:2024-12-31"

- Entity must match condition on every date in the range where data exists

- Best for "always has been" type queries

---

## **Advanced Examples**

#### Portfolio Analysis

```
// Get all active companies with revenue > $1M in 2024
=LX.NAMESPACE.LIST("Company",
  LX.FILTER.CHAIN("AND",
    LX.FILTER("@company.status = Active", "EXACT", "2024-01-01"),
    LX.FILTER("@company.revenue > 1000000", "EXACT", "2024-01-01")
  ),
  "name,revenue,employees",
  "-revenue")

// Sum total portfolio value across all funds
=LX.NAMESPACE.AGG("Fund", , "@company.valuation", "2024-12-31", "SUM")

// Get companies with current revenue > $1M (using latest value)
=LX.NAMESPACE.LIST("Company",
  LX.FILTER("@company.revenue > 1000000"),
  "name,revenue,status")
```

#### Compliance Monitoring

```
// List all covenant violations for an entity
=LX.COVENANT.LIST("entity123", , "-status", , TRUE)

// Count companies with covenant violations
=LX.NAMESPACE.AGG("Company", , "@covenant.status", "2024-01-01", "COUNT",
  LX.FILTER("@covenant.status = Out of Compliance", "EXACT", "2024-01-01"))

// Get companies currently out of compliance (using latest value)
=LX.NAMESPACE.LIST("Company",
  LX.FILTER("@covenant.status = Out of Compliance"),
  "name,covenant_status")
```

#### Performance Tracking

```
// Get latest revenue date for each company
=LX.ENTITY.DATE("company123", "@company.revenue", "latest")

// Track revenue growth across all subsidiaries
=LX.ENTITY.AGG("parent_company", "@company.revenue", "2024-01-01", "SUM",
  LX.FILTER("@company.growth_rate > 0.1", "EXACT", "2024-01-01"))

// Find companies with current positive growth
=LX.NAMESPACE.LIST("Company",
  LX.FILTER("@company.growth_rate > 0"),
  "name,revenue,growth_rate",
  "-growth_rate")
```

---

## **Developer Functions (LX.X.DEVELOPER.*)**

Developer functions assist with troubleshooting and configuration verification.

## **LX.X.DEVELOPER.ME**

Get current authenticated user information.

#### Return:

User ID and email string

#### Example:

```
=LX.X.DEVELOPER.ME()
```

## **LX.X.DEVELOPER.CONFIG**

Display current add-in configuration including API URL and authentication status.

#### Return:

Configuration details string

#### Example:

```
=LX.X.DEVELOPER.CONFIG()
```

---

## **Tips and Best Practices**

#### Performance Optimization

- Use filters to limit result sets when possible

- Refresh smaller selections for faster updates

- Consider using limits on large datasets

- Cache frequently used filter expressions in helper cells

#### Error Handling

- Check entity IDs are valid before building complex formulas

- Use developer functions to verify authentication

- Test filter expressions with simple cases first

#### Formula Organization

- Build filter expressions in separate cells for reusability

- Use named ranges for commonly referenced entity IDs

- Document complex formulas with comments

---

## **Troubleshooting**

#### Common Issues

- **Authentication errors**: Use LX.X.DEVELOPER.CONFIG() to check status

- **Invalid entity IDs**: Verify IDs using the sidebar lookup tool

- **Date format errors**: Use YYYY-MM-DD format or Excel date functions

- **Filter syntax errors**: Test individual filters before chaining

#### Getting Help

- Use the sidebar for entity and expression lookup

- Check the formula examples in this documentation

- Contact support for complex Lx expression assistance

#### Finding Excel Error Messages for Custom Functions

When a Lumonic Excel function encounters an error, you'll see #VALUE! displayed in the cell instead of the expected result. While this indicates something went wrong, Excel provides a way to view the detailed error.

Click on the warning icon or look for error checking options in the formula bar. Excel will show you additional information about the error:

![](https://downloads.intercomcdn.com/i/o/y9cw1kvx/1602442801/c07376c7408ed14ff6fe118cd264/AD_4nXf2IXmwFekkazKNBr8qMQ5AGhl7CTzagZ4jTH_xJBswqBzqn_p81IEgztNsDGhDEUQsQGRhE5W6rrxpvDY9Nze02Y1ky8JrK3C2AUnObayj2ooVrSs3UiXeswd0zEVVonOFjI4dJA?expires=1791840600&signature=7401e48425a4fdfc83486dd04f24796e3c42d60dd11de393d72941cf314bb521&req=dSYnFM16n4lfWPMW1HO4zbDaWDCufwKqR86cUjZAlZkrrFv%2BEC0fTvwCmZoY%0A%2Bwg2C6Cj4PX1pDembWM%3D%0A)

By clicking ‘Error Checking’ a dialog will appear that contains details on errors that have occurred in your spreadsheet.

![](https://downloads.intercomcdn.com/i/o/y9cw1kvx/1602442798/031b27dcbd79d52e499b714607d0/AD_4nXdpwL7R3JkwQH8GQmFD8gyBoxv1x4IRjJb7CHUxxMQK3kO-zmGdr9uzHa1shGVE-HA1Ny0o2YLvb3JAIMWBS0G1OyEUJc4KTELIAw_5DLlL0Mpt8fxYQzQbSwo8PUx6RLSJY2qETw?expires=1791840600&signature=a89fad6338df5489a4f73e57743cced572a1dab20e410e6cd6359469caad3344&req=dSYnFM16n4ZWUfMW1HO4zUe9qf59b1ztipBfOXkoIPpmVJyKjf%2FRei%2BEQGng%0As5SImIEJ0hTjBqTNAp4%3D%0A)

Once you do this, cells that have the errors will persist an error indicator at the cell for quick reference:

![](https://downloads.intercomcdn.com/i/o/y9cw1kvx/1602442799/4b4fee5433e6a590b279e2e4a74c/AD_4nXdDnXb3YjjiL5x9sAOaE7l1Aoyy0cAgSMvIiNLq1tWYXZK-NXTHO9bJUUafbjxQFduu821JtcojfMiAbniWmF1DHnuLSnn7ezdTmRnOjLpUZ5qHPAWBinXNLQ9AQZAtMhbwyDIelg?expires=1791840600&signature=dc44411d01fe417073c533663ebc824f41f5be07db215a43e117499290073d7c&req=dSYnFM16n4ZWUPMW1HO4zduVgNgIjcZ%2Bg3spR02dW2Ul2zyiijbtHDncSVQQ%0ANr1XtsyIRlEy0x1kXTU%3D%0A)