Loans Receivable (Historical) Formula in Excel
Loans Receivable (Historical) is a powerful Excel formula in MarketXLS that retrieves the outstanding amount of funds a company has lent but has not yet been repaid for a specified historical period. By using this function, you can quickly access crucial insight into a company’s balance sheet directly in Excel.
Understanding Loans Receivable (Historical)
- Purpose: Helps investors and analysts evaluate how much a company has lent out and not yet collected.
- Key Benefits:
- Provides direct access to fundamental metrics in Excel.
- Streamlines data retrieval for financial modeling.
- When to Use:
- Ideal for balance sheet analysis.
- Useful for comparing historical trends across multiple periods.
Syntax and Parameters
=hf_Loans_Receivable(Symbol, Year, [Quarter], [TTM])| Parameter | Description | Required | Example |
|---|---|---|---|
| Symbol | The ticker symbol for the company or security (stocks, indices, options, crypto). | Yes | "MSFT", "^SPX", "@MSFT…" |
| Year | The year you want data for (e.g., 2022) or special codes like "ly" (last year), "lq" etc. | Yes | 2022, "ly-1" |
| Quarter | The quarter number (1 to 4). Leave blank or default to "1" if unsure. | No | 2 |
| TTM | Accepts "TTM" for trailing twelve months. Leave blank otherwise. | No | "TTM" |
?? Note: The function returns a numeric value or "NA" if data is unavailable.
Return Value
- Returns the loans receivable figure for the specified company and period, as a numeric value.
- Displays "NA" for invalid symbols or if the data is not found.
Error Handling
- If the symbol is invalid or subscription is not active, the function will return "NA".
- Check your MarketXLS license validity to ensure uninterrupted data access.
? Pro Tip: Use references like =hf_Loans_Receivable(A1, A2) to dynamically pull different symbols and years from cells.
Examples and Usage
Below are some practical scenarios:
- Regular symbol by year:
=hf_Loans_Receivable("MSFT", 2022) - Specifying a quarter:
=hf_Loans_Receivable("^SPX", "2023", 2) - Including TTM (trailing twelve months):
=hf_Loans_Receivable("BTCUSD:DEFAULT", "2023", 3, "TTM") - Using special codes for year shortcuts:
=hf_Loans_Receivable("MSFT", "lq") // Last Quarter =hf_Loans_Receivable("MSFT", "ly-1") // Last Year minus 1 - Options symbol example:
=hf_Loans_Receivable("@MSFT 110122C00020000", 2022)
Date Input Formats
While this function specifically uses a string-based year parameter, you can still incorporate cell-referenced dates if needed. For example:
- Cell references:
=hf_Loans_Receivable("MSFT", TEXT(A1,"yyyy")) - Direct date:
=hf_Loans_Receivable("MSFT", TEXT("2024-03-15","yyyy")) - Using Excel date functions:
=hf_Loans_Receivable("MSFT", TEXT(A1,"yyyy-mm-dd"))
?? Note: Converting a date into a year or a quarter is essential since the function requires these parameters in string format.
Common Questions
1. Why am I getting "NA"?
This usually occurs if the symbol is invalid or if you do not have the required MarketXLS subscription plan. Verify the ticker and your license status.
2. How can I speed up calculations?
- Limit the number of calls by referencing cells instead of typing the same parameters repeatedly.
- Use batch refresh options in MarketXLS if available.
3. Can I reference other cells for Symbol or Year?
Absolutely. In many financial models, referencing cells maintains flexibility and scalability.
4. Does this formula work with non-U.S. stocks or crypto?
Yes. You can use international symbols, indices, options, and crypto by specifying the appropriate format, for example "BTCUSD:DEFAULT".
? Pro Tip: Mix and match "Year", "Quarter", and "TTM" to refine time periods for deeper insights.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today