Financial ratio calculator for panel data

How to use the calculator

1. Choose the template: non-financial firm or bank. The two have different input items; open "Recognised columns" to see them. Set the unit of your figures (VND, thousand, million or billion VND). The unit matters for firm size, ln(total assets), and is stated in the methods paragraph.
2. Click Download template (CSV), open it in Excel and fill one row per firm-year: firm is the ticker, year the fiscal year. You do not need every column; any ratio whose inputs are missing is listed under "Not calculated" with what it needs.
3. Copy the table including the header row, paste it and click Calculate ratios. Headers can be short keys (ta, ni), English names (Total assets, Net income) or the Vietnamese item names and codes used in Circular 200/2014 statements. One firm's statement pasted vertically, with year columns, is also accepted.
4. Read the Data check: it flags rows where total assets differ from liabilities plus equity by more than 1%, where profit before tax minus tax differs from net income, or where total assets jump more than 100-fold between two years (usually a unit slip).
5. Click Download panel data (CSV) for a long file (one row per firm-year) with the inputs, the ratios and, if chosen, winsorized versions (suffix _w) and one-year lags (suffix _l1). The commands box gives ready-made Stata (import delimited, xtset), R (plm) and EViews code.

All calculations run in your browser; your figures are not uploaded or stored.

Worked example

Click Load example: two firms, AAA (2021–2023) and BBB (2022–2023), in billion VND. For BBB in 2023, net income is 25.6 and total assets 540, so ROA = 25.6 / 540 = .047; equity is 320, so ROE = 25.6 / 320 = .080. Total liabilities of 220 give leverage of 220 / 540 = .407 and a debt-to-equity ratio of .688. With market capitalisation of 320, the Chung and Pruitt (1994) approximation of Tobin's Q is (320 + 220) / 540 = 1.00, and the original Altman (1968) Z-score is 2.39. Sales growth for BBB is (480 − 450) / 450 = .067, which needs the 2022 row: ratios that use the previous year are blank for each firm's first year.

Conventions you should state in your methods chapter

• End-of-year or average balances. By default denominators are year-end balances. Choosing the average of opening and closing balances affects ROA, ROE, asset turnover, the cash conversion cycle, NIM and loan loss provisions, and leaves each firm's first year blank. Leverage, liquidity and size always use year-end figures.
• Gaps. Growth rates, accruals, capital expenditure, average balances and lags are computed only from the immediately preceding year; a gap (2019 and 2021, no 2020) is never bridged.
• Undefined observations. ROE, D/E and market-to-book are blank when equity is negative; the effective tax rate and payout ratio are blank in loss years; interest coverage is blank without interest expense. Z'' (Altman et al., 1998) is still computed with negative equity, because that is itself a sign of distress.
• Bank Z-score. The standard deviation of ROA is taken over the latest three consecutive years (Laeven & Levine, 2009), so at least three years are needed. It is a different measure from the Altman Z-score.
• NIM. If interest-earning assets are not supplied, the tool divides by total assets and marks the value with an asterisk so you can report it as an approximation.

Winsorizing

Financial ratios often have a few extreme values (very small firms, revenue close to zero) that can pull regression coefficients. Winsorizing at 1% and 99% replaces every value below the 1st percentile with that percentile and every value above the 99th with the 99th; the number of observations is unchanged, unlike trimming. Percentiles are computed on the pooled sample with Stata's _pctile rule, the same as winsor2 roa, cuts(1 99). Lags are taken from the winsorized values. With fewer than 100 observations, 1% only touches the minimum and maximum, so report results before and after winsorizing if asked.

Reporting in APA 7

The methods paragraph lists every computed variable with its formula, unit, outlier treatment and the original source, and the reference list carries checked DOIs. Delete the variables that are not in your model and name your exact data source. In the results chapter, report the descriptive statistics table (N, mean, SD, min, median, max, as Stata's summarize), for example: "ROA had a mean of .xx (SD = .xx) across N firm-year observations."

FAQ

Why do my ratios differ slightly from a financial data website?

Each provider chooses its own denominator (year-end or average) and numerator (net income or net income attributable to parent shareholders). The formulas here are shown in full so you can match and cite them.

Can I paste several firms vertically?

A vertical statement is read for one firm at a time. For many firms use the wide template (one row per firm-year) or long data with the four columns firm, year, variable, value.

Does it work with statements outside Vietnam?

Yes, if you use the short keys or English headers. The item codes follow Vietnamese Circulars 200/2014 (firms) and 49/2014 (banks), but the ratio formulas are the standard ones from the cited papers.

How are numbers with commas read?

The tool looks at the whole table to decide whether the decimal mark is a point or a comma. In a table containing 0.5, 1,250 is one thousand two hundred and fifty; 0.125 is always a decimal. Negative numbers in brackets, such as (1,250), are read as −1,250.