Trading and Profit and Loss Account Format in Excel 1.0 – Free Final Accounts with Balance Sheet

Final accounts Excel template dashboard

Advertisement

Quick answer: The trading and profit and loss account format records a business’s results for the year, prepared from the trial balance at the year-end. The Trading Account finds the gross profit: net sales plus closing stock, minus opening stock, net purchases and direct expenses. The Profit & Loss Account then subtracts indirect expenses, depreciation and provisions, and adds other income, to find the net profit. The Balance Sheet lists what the business owns and owes on the last day of the year. The free Xplorexcel template prepares all three in the traditional T-format from your trial balance, adjustments included.

Key takeaways

  • Enter the trial balance once and pick a head for each account. The final accounts are then prepared automatically.
  • Covers the common adjustments: closing stock, outstanding and prepaid expenses, accrued income, income received in advance, depreciation, bad debts, provision for doubtful debts, interest on capital and interest on drawings.
  • Traditional T-format with Dr and Cr sides, “To” and “By” wording, and gross profit carried down (c/d) and brought down (b/d).
  • Checks that the trial balance tallies, that every account has a head, and that the balance sheet tallies.
  • Calculates key ratios: gross profit, net profit, current, quick, stock turnover and return on capital.

↓ Jump to the free download

FOR SHOPS & TRADERSBills, stock and GST returns — all in one Excel file.XploreBooks makes GST bills with barcode, updates stock and party khata, and gives GSTR-1, GSTR-3B, P&L and Balance Sheet.See XploreBooks — from ₹499 →⚡ Instant download · ♾️ Lifetime access · 💳 Pay onceXploreBooks
Final accounts Excel template dashboard showing net sales, gross profit, net profit and balance sheet total
The dashboard: net sales, gross profit, net profit, closing capital, balance sheet total and checks.

Trading and profit and loss account format

Trading Account (finds gross profit)

Dr. (debit side)Cr. (credit side)
To Opening StockBy Sales (less Sales Returns)
To Purchases (less Purchase Returns)By Closing Stock
To Direct Expenses (wages, carriage inward, freight, power …)By Gross Loss c/d (if any)
To Gross Profit c/d (balancing figure)

Profit & Loss Account (finds net profit)

Dr. (debit side)Cr. (credit side)
To Gross Loss b/d (if any)By Gross Profit b/d
To Indirect Expenses (salaries, rent, electricity, insurance …)By Indirect Income (commission, discount, interest received …)
To Bad Debts and Provision for Doubtful DebtsBy Interest on Drawings
To DepreciationBy Net Loss (if any)
To Interest on Capital
To Net Profit (transferred to Capital)

As a rule of thumb, direct expenses (those involved in buying or making the goods) go in the Trading Account, and indirect expenses (running the office and selling) go in the Profit & Loss Account. The template puts each account on the right side according to the head you choose.

Trading and profit and loss account format in Excel in T-format with gross profit and net profit
Trading Account and Profit & Loss Account, prepared automatically in T-format.

Balance sheet format (sole proprietor)

LiabilitiesAssets
Capital
+ Net Profit (or − Net Loss)
+ Interest on Capital
− Drawings
− Interest on Drawings
Fixed Assets (less depreciation)
Long-term LoansInvestments
Sundry Creditors, Bills PayableClosing Stock
Outstanding ExpensesSundry Debtors (less bad debts and provision)
Income Received in AdvanceCash, Bank, Prepaid Expenses, Accrued Income
Balance sheet format in Excel for sole proprietor with capital, fixed assets less depreciation, debtors less provision
Balance Sheet: capital account worked out, fixed assets net of depreciation, debtors net of provision, and a tally check.

How adjustments are treated (the two-way effect)

Every adjustment appears in two places. This is the part students most often get wrong, and the template handles it automatically.

AdjustmentEffect 1Effect 2
Closing stockTrading A/c, credit sideBalance Sheet, assets
Outstanding expenseAdded to that expenseBalance Sheet, liabilities
Prepaid expenseDeducted from that expenseBalance Sheet, assets
Accrued incomeAdded to that incomeBalance Sheet, assets
Income received in advanceDeducted from that incomeBalance Sheet, liabilities
DepreciationP&L A/c, debit sideDeducted from the asset
Further bad debts and new provisionP&L A/c, debit side (less old provision)Deducted from debtors
Interest on capitalP&L A/c, debit sideAdded to capital
Interest on drawingsP&L A/c, credit sideDeducted from capital
Year-end adjustments sheet in Excel for outstanding, prepaid, accrued income, closing stock, bad debts and provision
Adjustments sheet: pick the account and enter the amount. Both effects are posted for you.

Worked example: M/s Xplore Traders

The sample file uses a 28-account trial balance totalling ₹11,97,000. The adjustments are: closing stock ₹70,000, outstanding salaries ₹5,000, outstanding wages ₹4,000, prepaid insurance ₹1,500, accrued commission ₹1,000, depreciation on machinery and furniture at 10% and on the building at 5%, further bad debts ₹2,000, provision at 5% on debtors, interest on capital at 6% and interest on drawings ₹1,500.

Net sales₹6,85,000
Gross profit₹2,65,000 (38.7%)
Net profit₹1,17,600 (17.2%)
Closing capital₹4,04,100
Balance sheet total₹5,88,100 on both sides
Trial balance format in Excel with debit, credit, head, depreciation rate and adjusted amount
Trial Balance sheet: enter debit or credit, pick the head, and the adjusted amounts are calculated.

How to prepare final accounts in Excel (4 steps)

  1. Setup: Enter the firm name and the year-end date.
  2. Trial Balance: Enter each ledger balance as a debit or credit and choose its head, such as Purchases, Direct Expenses or Fixed Assets. For fixed assets, add the depreciation rate. Make sure the trial balance tallies.
  3. Adjustments: Enter the closing stock, outstanding and prepaid expenses, accrued and advance income, bad debts, provision, and interest on capital and drawings.
  4. Final Accounts: Check that the balance sheet tallies, then print. The Ratios sheet shows the key numbers.
Outgrown the free sheet? Here’s what XploreBooks adds
Free sheetXploreBooks
GST bills with barcode scan—✓
GSTR-1, GSTR-3B, P&L and Balance Sheet—✓
One-click new year—✓
Purchase & sales registers—✓
Low-stock alerts—✓
Fill-in forms & one-click PDF (macro edition)—✓
⚡ Instant download · ♾️ Lifetime access · 💳 Pay onceGet XploreBooks — from ₹499 →

Download the Final Accounts Template in Excel (Free)

File: Final Accounts – Trading, P&L and Balance Sheet 1.0 · Format: .xlsx · Macros: none · Price: free.

Free download · one quick step

Unlock this free template

This one is a full working system, not a one-page format. It stays free. I only ask for a way to reach you, because:

  • It has several linked sheets with a filled-in sample, so you see it working before you type your own entries.
  • The formulas are locked so one wrong click can’t break your totals. If something still goes wrong, you’ll know where to ask.
  • Tax and labour rules change. When GST, TDS or payroll rules change, I send the corrected file to the number or email you give here.
  • No spam. A few messages a month at most, unsubscribe any time.

Made by Amanpreet Kaur Bharaj, XplorExcel, Hoshiarpur. You only need to do this once on this device.

Was this template useful?

Students: try solving a textbook question by hand first, then check your answer with the template.

Advertisement

Frequently asked questions

What is the format of the trading and profit and loss account?

Both use a two-sided T-format. The Trading Account shows opening stock, net purchases and direct expenses on the debit side, and net sales and closing stock on the credit side, which gives the gross profit. The Profit & Loss Account starts with the gross profit, then shows indirect expenses on the debit side and indirect incomes on the credit side, which gives the net profit.

What is the difference between direct and indirect expenses?

Direct expenses are linked to buying or making goods, such as wages, carriage inward, freight and factory power, and go to the Trading Account. Indirect expenses relate to running and selling, such as salaries, rent, advertising and depreciation, and go to the Profit & Loss Account.

Why is closing stock shown twice?

Closing stock is an adjustment. It reduces the cost of goods sold, so it appears on the credit side of the Trading Account, and it is an asset at the year-end, so it also appears in the Balance Sheet.

How is provision for doubtful debts treated?

The new provision is worked out on debtors after deducting further bad debts. In the P&L Account, bad debts plus the further bad debts plus the new provision, minus the old provision, is debited. In the Balance Sheet, further bad debts and the new provision are deducted from debtors.

Is this format valid for companies?

No. This is the sole-proprietor format used in school and college accountancy and by small traders. Companies must prepare financial statements in the Schedule III format under the Companies Act, 2013.

Can I use this template in Google Sheets?

Yes. Upload the .xlsx file to Google Drive and open it with Google Sheets. It has no macros.

Advertisement

More free accounting templates from Xplorexcel

Last reviewed: October 2026.

Advertisement

Amanpreet Kaur Bharaj
I'm Amanpreet Kaur Bharaj from Hoshiarpur, Punjab. I've worked in Excel for about 12 years, and since 2022 I've run my own ethnic-wear boutique, Punjabi Libas, on Excel files I built myself. XplorExcel is where I share those files: free templates for everyday business work, and ready-made Excel software for shops, boutiques, clinics, dairies, mandis and offices. Every file comes with a filled-in sample so you can see how it works. Stuck with one? Message me and I'll help.