Spreadsheets

Depreciation for a part-year purchase: pro-rata by months in Excel

How to prorate first-year depreciation for an asset bought mid-year: count months in use up to 31 March with DATEDIF, then charge that share in Excel.

Editorial illustration for Depreciation for a part-year purchase: pro-rata by months in Excel

If you buy an asset part-way through the April-March year, the first year's depreciation is the full-year charge multiplied by the months you had it, divided by 12. In Excel you get the months with DATEDIF (or plain month arithmetic) and multiply. This page builds that pro-rata calculation step by step, with a worked example you can copy.

What does pro-rata depreciation mean for a mid-year purchase?

A full-year charge assumes you owned the asset for all twelve months. A laptop bought in August was only in use for part of the year, so charging it a full year overstates that year's cost. Pro-rata means you charge only the share of the year it was in use, and the remaining value carries into later years.

One warning before the formulas: the method that applies to your books, and the method used for income tax, can differ. Some rules count by days, and some treat purchases late in the year differently. Rates and rules change, so confirm the current position with your CA. This sheet is a clean way to keep your own record.

Which columns do you need?

  • A: Asset name (say, Laptop)
  • B: Purchase date (a real date, not text)
  • C: Cost in rupees
  • D: Annual depreciation rate you or your CA decided on, as a percentage
  • E: Financial year end for the purchase
  • F: Months in use in year one
  • G: First-year depreciation

How do you find the financial year end from the purchase date?

The year ends on 31 March. If the purchase month is April or later, the year ends next calendar year; otherwise it ends in the same calendar year. In E2 put:

=IF(MONTH(B2)>=4, DATE(YEAR(B2)+1,3,31), DATE(YEAR(B2),3,31))

Format E2 as a date. A purchase on 15 August 2026 gives 31 March 2027, and one on 10 February 2027 gives 31 March 2027 as well.

How do you count the months in use?

There are two honest ways. Pick one and stay with it.

  1. Completed months with DATEDIF: =DATEDIF(B2, E2+1, "m"). It counts full months from the purchase date up to 1 April. DATEDIF works in Excel and Google Sheets even though Excel does not list it in the function picker, so type it in.
  2. Calendar months, counting the purchase month as a whole month: =(YEAR(E2)-YEAR(B2))*12 + MONTH(E2) - MONTH(B2) + 1. This suits businesses that charge from the month of purchase.

For a laptop bought on 15 August 2026, method one gives 7 (15 August to 15 March) and method two gives 8 (August to March). Bought on 1 August, both give 8, because the first month is complete.

What is the worked example?

Say a laptop costs ₹60,000 and you decided on 20% straight-line, a hypothetical rate. The full-year charge is ₹12,000. With the DATEDIF count of 7 months, the first-year charge in G2 is:

=C2*D2*F2/12

That is 60,000 x 0.20 x 7 / 12 = ₹7,000.

Financial yearMonthsChargeCumulative
2026-277₹7,000₹7,000
2027-2812₹12,000₹19,000
2028-2912₹12,000₹31,000
2029-3012₹12,000₹43,000
2030-3112₹12,000₹55,000
2031-32remainder₹5,000₹60,000

The last row is why a cap matters. For every later year use =MIN(C2*D2, C2 - cumulative so far), so the charge stops once the whole cost has been written off.

What if you sell or scrap the asset mid-year?

Use the same idea in reverse: count the months from the start of the year to the date it left the business, and charge only those. For one-off disposals, typing the month count into a separate cell and adding a note beside it is clearer than a clever formula nobody can read later.

A short checklist before you rely on it

  • Test with a purchase on 1 April and one on 31 March. The first should give 12 months. The second gives 1 by the calendar method and 0 with DATEDIF, since only a day remains.
  • Keep the purchase invoice number in an extra column so each row can be traced.
  • Ask your CA which counting method and rate to use, then write those choices in the header row.

If you also want the purchase itself in your spending records, SpendixAI can take the card or bank statement line that paid for it, and you keep the depreciation schedule in the sheet.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports