Spreadsheet used for Payroll Accounting, Asset Management, Loan Repayment Schedule.
1. PAYROLL ACCOUNTING
Meaning: Statement showing detailed salary calculation including earnings & deductions.
Components – Earnings: - Basic Pay (BP): Fixed component =BP - Dearness Allowance (DA): % of BP =BP*DA% - House Rent Allowance (HRA): Fixed or % of BP =IF(BP>55500,2500,1500) - Transport Allowance (TA): Fixed amount
Components – Deductions: - Provident Fund (PF): % of BP =(BP+DP)*PF% - Professional Tax (PT): Fixed slab - TDS: Monthly income tax - Loan Recovery: Fixed EMI
Formulas: Gross Pay = BP + All Allowances, Net Pay = Gross Pay – All Deductions
PAYROLL EXAMPLE WITH ABSOLUTE REFERENCE
Data: Arjun(Manager,40000), Bina(Technician,25000), Ramesh(Driver,18000)
Constants: DA 20% ($E$3), HRA Manager 3% ($E$4), Technician 2% ($E$5), Driver 0% ($E$6), PF 10% ($E$7)
Formulas (row 10): - DA: =D10*$E$3 - HRA: =IF(C10="Manager",D10*$E$4,IF(C10="Technician",D10*$E$5,D10*$E$6)) - Gross: =SUM(D10:F10) - PF: =D10*$E$7 - Net: =G10-H10
Result: Arjun Net ₹45200, Bina ₹28000, Ramesh ₹19800
2. ASSET ACCOUNTING
Depreciation: Gradual decrease in fixed asset value.
Straight Line Method (SLN): Fixed amount yearly.
=SLN(Cost, Salvage, Life)Example: Cost ₹11000, Salvage ₹2000, Life 10 years →
=SLN(11000,2000,10) = ₹900 per year
Written Down Value Method (DB): Decreasing amount yearly.
=DB(Cost, Salvage, Life, Period, [Month])Example: Cost ₹100000, Salvage ₹5000, Life 10 years, Year 1 →
=DB(100000,5000,10,1,12) = ₹20600
3. LOAN REPAYMENT SCHEDULE
PMT Function (EMI):
=PMT(rate, nper, pv, [fv], [type])Example: Loan ₹200000, 10% p.a., 5 years →
=PMT(10%/12, 5*12, 200000) = ₹-4249 (monthly EMI)
Principal & Interest Split: - PPMT: Principal part
=PPMT(rate, period, nper, pv)
- IPMT: Interest part =IPMT(rate, period, nper, pv)
IMPORTANT EXAM QUESTIONS (CHAPTER 3)
Q1 (2018 Mar): Odd one (not Gross Pay element)? Ans: PF (Provident Fund is deduction).
Q2 (2018 Say): Function for fixed installment depreciation? Ans: SLN.
Q3 (2020 Mar): Not a Payroll component? Ans: D.B.
Q4 (2020 Say): Deduction in Net Pay? Ans: PF.
Q5 (2018 Mar): SLN syntax? Ans: SLN(cost, salvage, life). DB syntax? DB(cost, salvage, life, period, [month]). PMT syntax? PMT(rate, nper, pv, [fv], [type]).
Q6 (2018 Say): DA 14% formula? Ans: =C4*14%. HRA if BP>55500 then 2500 else 1500? =IF(C4>55500,2500,1500). Gross Pay? =C4+D4+E4.
Q7 (2019 Mar): SLN vs DB difference? Ans: SLN = equal depreciation yearly; DB = decreasing depreciation yearly. Methods different.