Formulas for financial ratios
This page lists the formulas for common financial ratios you can add to your financial statement calculations. You might use these ratios to assess aspects of financial performance such as liquidity, leverage, growth, margins, profitability, rates of return, valuation, and more. Note that the formulas are indicative, as every business will have its own naming conventions and groupings.
See also Functions for Profit & Loss calculations.
Liquidity ratios
Liquidity ratios measure a company’s ability to repay both short-term and long-term debt obligations. Many of these rations use the [BAL] function in their formulas.
Current
Current Assets / Current Liabilities
The Current ratio, also known as the Working Capital ratio, measures a company's capability to meet its short-term obligations (liabilities) that are due within a year. In other words, it measures a company’s liquidity. The ratio considers the weight of total Current Assets versus total Current Liabilities. It indicates a company's financial health and shows how it can maximize the liquidity of its current assets to settle debt and obligations to suppliers.
The formula you use in the calculation:
On a Balance Sheet: [a]/[b] where [a] is Current Assets and [b] is Current Liabilities
On a Profit & Loss statement: [bal(Balance Sheet, Current Assets, closing)]/[bal(Balance Sheet, Current Liabilities, closing)]
Acid-test
(Current Assets - Inventories) / Current Liabilities
The Acid-Test ratio, also known as the Quick ratio, is a liquidity ratio that measures how sufficient a company’s short-term assets are to cover its current liabilities. It measures how well a company can satisfy its short-term (current) financial obligations.
The formula you use in the calculation:
On a Balance Sheet: ([a]-[d])/[b] where [a] is Current Assets, [d] is Inventories, and [b] is Current Liabilities
On a Profit & Loss statement: ([bal(Balance Sheet, Current Assets, closing)]-[bal(Balance Sheet, Inventories, closing)])/[bal(Balance Sheet, Current Liabilities, closing)]
Cash
Cash & Cash Equivalents / Current Liabilities
The Cash ratio, sometimes referred to as the Cash Asset ratio, is a liquidity metric that indicates a company’s capacity to pay off short-term debt obligations with its cash and cash equivalents. Compared to other liquidity ratios, the Cash ratio is a stricter, more conservative measure because only cash and cash equivalents (a company’s most liquid assets) are used in the calculation.
The formula you use in the calculation:
On a Balance Sheet: [e]/[b] where [e] is Cash and [b] is Current Liabilities
On a Profit & Loss statement: [bal(Balance Sheet, Cash, closing)]/[bal(Balance Sheet, Current Liabilities, closing)]
Operating Cash Flow
Operating Cash Flow / Current Liabilities
The Operating Cash Flow ratio is a measure of the number of times a company can pay off current liabilities with the cash generated in a given period. Companies often use EBITDA (Earnings Before Interest, Tax, Depreciation and Amortisation) as a proxy for the operating cash flow.
The formula you use in the calculation on a Cash Flow statement: [c]/[bal(Balance Sheet, Current Liabilities, closing)] where [c] is the operating cash flow
Working Capital
Current Assets - Current Liabilities
Like the Current ratio, the Working Capital ratio measures the ability of a company to repay its short-term obligations (liabilities).
The formula you use in the calculation:
On a Balance Sheet: [a]-[b] where [a] is Current Assets and [b] is Current Liabilities
On a Profit and Loss statement: [bal(Balance Sheet, Current Assets, closing)]-[bal(Balance Sheet, Current Liabilities, closing)]
Profitability ratios
Profitability ratios are financial metrics used by analysts and investors to measure and evaluate a company's ability to generate profit relative to revenue, Balance Sheet assets, operating costs, and shareholders' equity during a specific period. They show how well a company uses its assets to produce profit and value for shareholders. In other words, they paint a picture of a company’s performance.
Gross Profit
Revenue - Cost of Goods Sold
The Gross Profit is the difference between Revenue and Cost of Goods Sold.
The formula you use in the calculation: [a]-[b] where [a] is Revenue and [b] is Cost of Goods Sold
Gross Margin
(Revenue – Cost of Goods Sold) / Revenue * 100
The Gross Margin ratio, also known as the Gross Profit margin, compares a company's gross profit to its revenue. It shows a company's profit after paying off its Cost of Goods Sold (COGS). The ratio indicates the percentage of each dollar of revenue that the company retains as gross profit (profit before operating expenses). For example, if the Gross Margin is calculated to be 20%, that means for every dollar of revenue generated, $0.20 is retained while $0.80 is attributed to the cost of goods sold. The remaining amount can be used to pay off general operating expenses, interest expenses, debts, rent, overhead, etc.
The formula you use in the calculation: (([a]-[b])/[a])*100 where [a] is Revenue and [b] is Cost of Goods Sold
Operating Profit Margin
Operating Profit (before interest & tax) / Revenue * 100
The Operating Profit Margin ratio reflects the percentage of profit a company produces from its operations before subtracting taxes and interest charges. It is calculated by dividing the operating profit by total revenue and expressing it as a percentage. The margin is also known as EBIT (Earnings Before Interest and Tax) Margin.
The formula you use in the calculation: (([c]-[d])/[a])*100 where [c] is Gross Profit and [d] is Operating Expenses and [a] is Revenue
Return on Assets
Operating Profit (Annualised)/ Asset Value * 100
The Return on Assets (ROA) ratio is a type of return on investment (ROI) metric that measures a company's profitability in relation to its total assets. This ratio indicates how well a company is performing by comparing the profit it is generating to the capital it has invested in assets. The higher the return, the more efficient management is in using the company’s economic resources.
The formula you use in the calculation: ([h]/[DaysInPeriod]*365)/[bal(Balance Sheet, Total Assets, average)]*100 where [h] is Profit
The [DaysInPeriod] function multiplied by 365 part of the formula produces annualized profit.
Return on Equity
Operating Profit (Annualised)/ Shareholders’ Equity * 100
The Return on Equity (ROE) ratio measures a company’s annual operating profit divided by the value of its total shareholders' equity, expressed as a percentage. The number represents the total return on equity capital and shows the company’s ability to turn equity investments into profits. In other words, it measures the profits made for each dollar of shareholders’ equity.
The formula you use in the calculation: ([h]/[DaysInPeriod]*365)/-[bal(Balance Sheet, Equity, average)]*100 where [h] is Profit
The [DaysInPeriod] function multiplied by 365 part of the formula produces annualized profit.
You need to reverse the sign on Equity.
Efficiency ratios
Efficiency ratios, also known as Activity Financial ratios, measure how well a company is using its assets and resources.
Asset Turnover
Net Sales Revenue / Total Assets The Asset Turnover ratio measures the value of revenue generated by a business relative to its average total assets for a given fiscal or calendar year. It is an indicator of how efficiently the company uses both current and fixed assets to produce revenue.
The formula you use in the calculation: [a]/[DaysInPeriod]*365)/[bal(Balance Sheet, Total Assets, average)] where [a] is Revenue.
The [DaysInPeriod] function multiplied by 365 part of the formula produces annualized revenue.
Inventory Turnover
Cost of Goods Sold / Average Inventory
The Inventory Turnover ratio is the number of times a company sells and replaces its stock of goods during a given period. It considers the cost of goods sold, relative to its average inventory for a year or in any set period. A high inventory turnover generally means that goods are sold faster and a low turnover rate indicates weak sales and excess inventories, which may be challenging for a company.
The formula you use in the calculation: [b]/[DaysInPeriod]*365)/[bal(Balance Sheet, Inventory, average)] where [b] is Cost of Goods Sold
The [DaysInPeriod] function multiplied by 365 part of the formula produces annualized cost of goods sold.
Days Sales in Inventory (DSI)
365 days / Inventory Turnover The Days Sales in Inventory (DSI) ratio, sometimes known as Inventory Days or Days in Inventory, measures the average number of days or time required for a company to convert its inventory into sales. The DSI value is calculated by dividing the inventory balance by the amount of Cost of Goods Sold. This number is then multiplied by the number of days in a year, quarter, or month.
You can use either of these formulas in the calculation, where [b] is Cost of Goods Sold:
365/([b]/[DaysInPeriod]*365/[bal(Balance Sheet, Inventory, average)])
[bal(Balance Sheet, Inventory, average)]/([b]/[DaysInPeriod])
Days Sales Outstanding (DSO)
Accounts Receivable/Sales x 365
The Days Sales Outstanding (DSO) ratio, also called the Average Collection Period or Days’ Sales in Receivables, measures the number of days it takes a company to collect cash from its sales. It shows the liquidity and efficiency of a company’s collections department.
The formula you use in the calculation: [bal(Balance Sheet, Accounts Receivable, average)]/([a]/[DaysInPeriod]) where [a] is Revenue
Days Payable Outstanding (DPO)
Accounts Payable/COGS x 365
The Days Payable Outstanding (DPO) ratio measures the average time it takes a company to pay its invoices from suppliers and vendors. For instance, a company that takes longer to pay its bills has access to its cash for a longer period and can do more things with it during that period.
The formula you use in the calculation: [bal(Balance Sheet, Accounts Payable, average)]/([b]/[DaysInPeriod]) where [b] is Cost of Goods Sold.
Cash Conversion Cycle (CCC)
DSI + DSO - DPO
The Cash Conversion Cycle (CCC) ratio attempts to measure the time it takes a company to convert its investment in inventory and other resource inputs into cash. In other words, it measures how long cash is tied up in inventory before the inventory is sold and cash is collected from customers.
Leverage ratios
Leverage Ratios measure the amount of capital in a company that comes from debt. In other words, they evaluate the risks associated with a company’s debt levels.
Debt
(Short-term Debt + Long-term Debt) / Total Assets
The Debt Ratio indicates the percentage of assets that are being financed with debt. The higher the ratio, the greater the degree of leverage and financial risk. It’s commonly used by creditors to determine the amount of debt in a company, the ability to repay its debt, and whether additional loans will be extended to the company. On the other hand, investors use the ratio to make sure the company is solvent, can meet current and future obligations, and can generate a return on their investment.
The formula you use in the calculation:
On a Balance Sheet: ([e]+[f])/[a] where [e] is Short Term Loans, [f] is Long Term Loans, and [a] is Total Assets
On a Profit and Loss statement: ([bal(Balance Sheet, Loans due this year, closing)]+[bal(Balance Sheet, Loans Long Term, closing)])/[bal(Balance Sheet, Total Assets, closing)]
Debt to Equity
(Short-term Debt + Long-term Debt) / Shareholders’ Equity
The Debt to Equity ratio, also called the Debt-Equity, Risk, or Gearing ratio, calculates the weight of total debt and financial liabilities against total shareholders' equity. This ratio highlights how a company’s capital structure is tilted either toward debt or equity financing.
The formula you use in the calculation:
On a Balance Sheet: ([e]+[f])/[g] where [e] is Short Term Loans, [f] is Long Term Loans, and [g] is Total Shareholders' Equity
On a Profit and Loss statement: ([bal(Balance Sheet, Loans due this year, closing)]+[bal(Balance Sheet, Loans Long Term, closing)])/[bal(Balance Sheet, Total Equity, closing)]
Interest Cover
Earnings Before Interest & Tax / Interest Expense
The Interest Coverage ratio determines how well a company can pay the interest on its outstanding debts. It’s commonly used by lenders, creditors, and investors to determine the riskiness of lending capital to a company.
The formula you use in the calculation: [p]/[h] where [p] is Earnings Before Interest & Tax and [h] is Interest Expense
Last updated