Schedules with spreadsheets

The essentials

Spreadsheets make repeated calculations quick and accurate. Three common accounting schedules are prepared with them: the depreciation schedule, which shows yearly depreciation and book value of an asset; the loan repayment schedule, which splits each instalment into interest and principal; and the payroll, which calculates each employee's gross pay, deductions and net pay.

Key points

• Straight-line depreciation = (Cost − Salvage value) ÷ Useful life; spreadsheet function SLN(cost, salvage, life).
• Written down value method: depreciation = Opening book value × Rate; functions such as DB and DDB give declining-balance depreciation.
• Equated instalment for a loan: function PMT(rate, nper, pv), where rate is the interest rate per period and nper the number of periods. IPMT and PPMT give the interest and principal parts of a payment.
• In each instalment, interest = Outstanding balance × Rate per period; principal repaid = Instalment − Interest.
• Payroll: Gross pay = Basic pay + Dearness Allowance (DA) + House Rent Allowance (HRA) + other allowances; Net pay = Gross pay − Deductions (such as provident fund and tax deducted at source).
• Example: an asset costing ₹1,00,000 depreciated at 20% on WDV gives ₹20,000 in year 1 and ₹16,000 in year 2.

Exam tips

For monthly instalments, divide the annual rate by 12 and multiply years by 12. Use absolute references for fixed rates when building the schedule.

Common mistakes

• Using the annual rate in PMT for a monthly loan.
• Calculating provident fund on gross pay when the question says basic pay plus DA.