Financial Ratios in Excel: Liquidity, Margin and Debt

Financial ratios in Excel turn a company's raw figures into numbers you can compare: how easily it pays its bills, how much of each sale it keeps, and how much it relies on debt. Each ratio is a simple division between two cells, plus IFERROR for zeros and ROUND for tidy output. This guide is for students, analysts and job seekers preparing for a finance Excel test. It explains each ratio in plain English and builds it on one set of example figures.

What are financial ratios?#

A financial ratio divides one figure by another so you can compare businesses of different sizes, or one business over time. The common ones fall into three groups:

The figures come from two reports. The income statement, also called the profit and loss, shows revenue and costs over a period. The balance sheet shows assets, liabilities and equity on one date. The guide to building a profit and loss statement shows how the income statement side is laid out.

How to set up the figures#

Put each source figure in its own labelled cell, so every ratio formula is short and easy to check. These are example figures for a small company, in A1:B9:

Current means due or usable within about a year. Cost of goods sold is the direct cost of what was sold. Shareholders' equity is what belongs to the owners: total assets minus total liabilities.

Liquidity ratios: current ratio and quick ratio#

Liquidity ratios check whether short-term assets cover short-term debts. The current ratio divides current assets by current liabilities:

=B4/B6

The result is 1.5: the company has 1.50 of current assets for each 1.00 it owes in the next year.

The quick ratio is stricter. It leaves out inventory, because stock can take time to sell:

=(B4-B5)/B6

The result is 0.9 ((150,000 − 60,000) ÷ 100,000). The brackets matter: without them, Excel divides inventory by liabilities first and returns a wrong answer. What counts as a comfortable level depends on the industry, so compare with similar businesses or past years rather than a single rule.

Profitability ratios: margins and return on equity#

Margins show how much of each sale the business keeps, written as percentages. Gross margin uses revenue minus the direct cost of sales:

=(B2-B3)/B2

Format the cell as a percentage: the result is 40%. Net margin uses profit after all costs:

=B9/B2

That gives 8%. The article on the profit margin formula explains the difference between margin and markup, which is a common test question.

Return on equity (ROE) compares profit with the owners' money in the business: =B9/B8 gives 18.2% (40,000 ÷ 220,000).

Leverage ratio: debt-to-equity#

Debt-to-equity compares what the company owes with what the owners have put in:

=B7/B8

The result is 0.818…, usually shown as 0.82. That means 0.82 of liabilities for each 1.00 of equity.

Definitions vary. Some textbooks and analysts use only interest-bearing debt, such as loans, instead of total liabilities. Both are valid, but they give different numbers, so write the definition next to the ratio. If you need ratios in "x : y" form, such as 9 : 11, the guide on how to calculate a ratio in Excel covers that.

How to handle zeros and missing figures with IFERROR#

If a denominator is zero or blank, the ratio shows #DIV/0!. That happens with a new company with no revenue yet, or a template where a figure hasn't been entered. Wrap the formula in IFERROR to show something cleaner:

=IFERROR(B4/B6,"n/a")

If B6 is 0, the cell shows n/a. Otherwise it shows the ratio as normal. The IFERROR function guide explains the syntax, and the article on fixing the #DIV/0! error covers other ways to handle it.

One warning: IFERROR hides every error, including a typo in a cell reference. If you only want to catch the zero case, test for it directly: =IF(B6=0,"n/a",B4/B6).

How to round and format ratios#

Use ROUND when the rounded value should be the real value, and number formatting when you only want to change what's shown. To store the quick ratio at two decimals, with the zero check included:

=IFERROR(ROUND((B4-B5)/B6,2),"n/a")

This returns 0.9. The ROUND function guide explains the second argument.

For display only, use formats instead:

• Margins and ROE: Home > Number Format > Percentage.
• Ratios with an "x": a custom format of 0.00"x" shows 1.5 as 1.50x while the cell still holds 1.5. Open it with Ctrl+1 on Windows or ⌘1 on a Mac, then choose Custom.

How to compare ratios across years#

A single ratio means little on its own. Put each year in its own column, with the same rows, and write each ratio once in the first year's column. Then fill it right (Windows: Ctrl+R; Mac: ⌘R). Because the references are relative, =B4/B6 becomes =C4/C6 for the next year. Add a row for the change between years to show which way each ratio is moving.

This layout, with source figures above and ratios below, is common in finance work and job tests. The guide to Excel skills for finance jobs lists the other skills that come up alongside ratios, and the finance Excel test lets you practise them under a timer.

Practise it#

Try the exercise on this page: it builds the quick ratio with ROUND and IFERROR in one formula. Then practise the department budget lesson in a live spreadsheet to work with divisions and percentages on real-looking figures, and practise IFERROR so a missing number never fills your report with errors.