Class 12 Commerce . Accountancy . Computerised Accounting System . Use of Spreadsheet in Business Applications

Chapter 3: Use Of Spreadsheet In Business Applications

Ch 3

Accountancy

Class 12 Commerce

Lecture 1

Introduction &Payroll Accounting Part- I

Introduction

  • Applications: Spreadsheets can support business calculations, including payroll, fixed-asset accounting and loan repayment.

Payroll Accounting

Payroll processing sequence

  1. Record employee, pay-period and attendance details
  2. Calculate earnings using pay values and applicable rates
  3. Calculate deductions using applicable rules and employee instructions
  4. Find net salary and prepare salary statements or slips
  5. Generate bank advice for salary transfers and statutory payments

Payroll Components

Earnings
ComponentMeaning
Basic Pay (BP)Pay in the pay scale plus Grade Pay; it excludes Special Pay.
Grade Pay (GP)Pay added according to the employee’s designation and applicable pay band or pay scale.
Dearness Pay (DP)The portion of Dearness Allowance declared and treated as merged with Basic Pay.
Dearness Allowance (DA)Compensation for loss of purchasing power due to rising prices; granted periodically as a percentage of Basic Pay plus Dearness Pay, where applicable.
House Rent Allowance (HRA)An amount paid to help the employee obtain residential accommodation on lease.
Transport Allowance (TRA)An amount paid to help meet the cost of commuting to the place of work.
Other earningsOther allowances declared from time to time, such as education, medical or washing allowance.
Dearness Allowance base
DA=rDA(BP+DP)\mathrm{DA}=r_{\mathrm{DA}}(\mathrm{BP}+\mathrm{DP})

DA is calculated as a percentage of Basic Pay and Dearness Pay, if applicable.

Deductions
ComponentMeaning
Professional Tax (PT)A statutory deduction imposed under the relevant State Government legislation; it applies in some states.
Provident Fund (PF)A statutory social-security deduction under the Provident Fund Act, calculated as a percentage of Basic Pay plus Dearness Pay, if applicable.
Tax Deduction at Source (TDS)Monthly deduction towards the employee’s income-tax liability; it apportions the yearly liability across 12 months.
Loan instalment recovery (LOAN)An amount deducted as authorised by the employee towards repayment of a loan.
Other deductionsOther recoveries, such as salary advance, food grain advance or festival advance.
Provident Fund base
PF=rPF(BP+DP)\mathrm{PF}=r_{\mathrm{PF}}(\mathrm{BP}+\mathrm{DP})

PF is computed using the applicable rate on Basic Pay and Dearness Pay, if applicable.

Gross and net salary
Gross Salary=BP+DP+DA+HRA+TRA+Other EarningsTotal Deductions=PT+PF+TDS+LOAN+Other DeductionsNet Salary=Gross Salary−Total Deductions\begin{aligned}\mathrm{Gross\ Salary}&=\mathrm{BP}+\mathrm{DP}+\mathrm{DA}+\mathrm{HRA}+\mathrm{TRA}+\mathrm{Other\ Earnings}\\\mathrm{Total\ Deductions}&=\mathrm{PT}+\mathrm{PF}+\mathrm{TDS}+\mathrm{LOAN}+\mathrm{Other\ Deductions}\\\mathrm{Net\ Salary}&=\mathrm{Gross\ Salary}-\mathrm{Total\ Deductions}\end{aligned}

Gross salary is total earnings before deductions; net salary is the amount payable after deductions.

Worked example

Using the given illustrative figures, find the employee’s gross and net salary.

  1. Suppose an employee’s monthly earnings are BP Rs. 30,000, DP Rs. 2,000, DA Rs. 6,400, HRA Rs. 5,000, TRA Rs. 1,000 and other earnings Rs. 600. Total deductions are PT Rs. 200, PF Rs. 3,200, TDS Rs. 1,500 and loan recovery Rs. 1,000.
  2. Add all earnings to find gross salary.
    Gross Salary=30000+2000+6400+5000+1000+600=45000\mathrm{Gross\ Salary}=30000+2000+6400+5000+1000+600=45000
  3. Add the deductions.
    Total Deductions=200+3200+1500+1000=5900\mathrm{Total\ Deductions}=200+3200+1500+1000=5900
  4. Subtract total deductions from gross salary.
    Net Salary=45000−5900=39100\mathrm{Net\ Salary}=45000-5900=39100
Answer: Gross salary = Rs. 45,000; net salary = Rs. 39,100.
⚠ Common mistake

Do not subtract deductions when finding gross salary. Also, follow the organisation’s salary format to avoid counting Grade Pay twice when Basic Pay is defined as including it.

Practice

An employee has Basic Pay Rs. 24,000, Dearness Pay Rs. 1,000 and DA at 10% of BP plus DP. Find DA.

Hint: Apply the DA percentage to BP plus DP.
Key step: The DA base is Rs. 24,000 + Rs. 1,000 = Rs. 25,000.
Solution: DA = 10% of Rs. 25,000 = Rs. 2,500.
Practice

Gross salary is Rs. 48,000 and total deductions are Rs. 7,250. Find net salary.

Hint: Subtract total deductions from gross salary.
Key step: Net salary = gross salary − total deductions.
Solution: Net salary = Rs. 48,000 − Rs. 7,250 = Rs. 40,750.
Practice

Name the payroll document or advice used to show the net salary to be transferred to employees’ bank accounts.

Hint: It is generated after payroll calculations.
Key step: It communicates bank transfers of net salary and related statutory payments.
Solution: Bank advice.

Quick recap

  • Payroll calculations use employee pay, attendance, applicable rates and deductions for a specified month and year.
  • Payroll work includes maintaining employee data, computing payroll, preparing salary statements and slips, and generating bank advice.
  • Earnings include BP, DP, DA, HRA, TRA and other allowances; DA is based on BP plus DP, if applicable.
  • Deductions include PT, PF, TDS, loan recovery and other authorised recoveries.
  • Gross salary is total earnings before deductions; net salary equals gross salary less total deductions.
Lecture 2

Introduction & Payroll Accounting Part-II

Payroll Calculation Elements

  • Payroll elements: Payroll calculations use attendance, pay rates and applicable allowances to determine employee earnings.
  • Effective days: Basic Pay Earned is based on the employee’s effective days present during the month.
  • Total earnings: Basic Pay Earned, DA, HRA and transport allowance are combined to calculate total earnings.

Deductions and Net Salary

  • Deductions: Common payroll deductions include Provident Fund, TDS and loan instalments.
  • Total deductions: Add the applicable deductions to calculate Total Deductions.
  • Net salary: Net Salary = Total Earnings − Total Deductions.

Spreadsheet Template and Payroll Layout

Example Payroll Parameters and Type-Based Allowances
Parameter or employee typeRate or amount
DA35% of BPE
Supervisory HRA (Sup)40% of BPE
Non-supervisory HRA (Nsup)30% of BPE
Consultant or contract employeeNil HRA and nil TRA in the example
Supervisory TRARs. 1,000 fixed
Non-supervisory TRARs. 500 fixed
PF12% of BPE in the spreadsheet calculations
Worked example

Calculate payroll net salary for the given employee.

  1. For a supervisory employee with BP Rs. 16,500, 1.5 deduction days in a 28-day month, find effective days.
    NOEDP=28−1.5=26.5\mathrm{NOEDP}=28-1.5=26.5
  2. Calculate basic pay earned.
    BPE=16,500×26.528=15,616.07\mathrm{BPE}=16,500\times\frac{26.5}{28}=15,616.07
  3. Calculate DA and HRA using the applicable rates.
    DA=15,616.07×0.35=5,465.63,HRA=15,616.07×0.40=6,246.43\mathrm{DA}=15,616.07\times0.35=5,465.63,\quad \mathrm{HRA}=15,616.07\times0.40=6,246.43
  4. Add TRA to find total earnings.
    TE=15,616.07+5,465.63+6,246.43+1,000=28,328.13\mathrm{TE}=15,616.07+5,465.63+6,246.43+1,000=28,328.13
  5. Calculate PF and total deductions.
    PF=15,616.07×0.12=1,873.93,TD=1,873.93+3,300+0=5,173.93\mathrm{PF}=15,616.07\times0.12=1,873.93,\quad \mathrm{TD}=1,873.93+3,300+0=5,173.93
  6. Subtract deductions from earnings to find net salary.
    NS=28,328.13−5,173.93=23,154.20\mathrm{NS}=28,328.13-5,173.93=23,154.20
Answer: Net salary = Rs. 23,154.20.
⚠ Common mistake

Do not calculate full-month basic pay when the employee has unpaid or unauthorised absence: prorate it by effective days. The source’s parameter-cell references differ (the prose names one set of cells while its sample formulas use another), so use the actual parameter locations consistently. Also, the source’s PF rule table says 12% of BP + DA, but the spreadsheet formulas and example values calculate 12% of BPE; follow the stated formula for the spreadsheet example.

Quick recap

  • Effective days = days in the month − deduction days; BPE is prorated using effective days.
  • DA, HRA and TRA are added to BPE to obtain total earnings.
  • PF, TDS and loan instalments make up total deductions.
  • Net salary = total earnings − total deductions.
  • A payroll template separates direct inputs, fixed parameters and formula-driven results.
  • Absolute cell references keep common parameter cells fixed when formulas are copied.
Lecture 3

Asset Accounting Part-I

Assets and Depreciation

Definition

Fixed assets — Long-term resources that provide productive capability to an organisation; they include both tangible and intangible assets.

Asset types and examples

  • Current assets: Assets classified separately from long-term fixed assets.
  • Tangible fixed assets: Physical assets with form, shape and size, such as buildings and machinery.
  • Intangible fixed assets: Non-physical resources that add value, such as goodwill, patents, copyrights and trademarks.
  • Depreciation: Recognises the cost of a fixed asset consumed during an accounting period because its useful life extends beyond one year.
  • Freehold land: Depreciation is usually not charged on freehold land.
Common asset categories
CategoryTypes or examples
GoodwillGoodwill
LandFreehold land; leasehold land
BuildingFactory; office; residential
Plant and machineryPlant and machinery
Furniture and fixturesFurniture and fixtures
VehiclesVehicles
Capital work in progressCapital work in progress
OthersOther asset classes
Definition

Year-to-date depreciation — Accumulated depreciation from the date an asset is put to use up to the current accounting year.

Total depreciable amount over an asset’s life
A−SA-S

Total depreciation over the asset’s useful life is acquisition cost less salvage value. Let AA denote acquisition cost and SS salvage value.

Asset-accounting records and methods

  • Records maintained: An asset register, a depreciation calculation sheet and a fixed-asset schedule.
  • Reporting: The fixed-asset schedule supports balance-sheet reporting in the annual accounts.
  • Methods: Depreciation is calculated according to organisational policy, commonly using the Straight Line Method (SLM) or Written Down Value Method (WDV).

Computerised Asset Accounting

Computerised asset accounting

  • Prescribed rates: The Companies Act, 1956, Schedule XIV lists depreciation rates for asset classes under SLM and WDV.
  • Method choice: Prescribed rates differ between methods; corporate enterprises may use either method and its applicable rates for reporting.
  • Spreadsheet use: Asset details are entered in columns, while calculated values are produced with formulas or built-in functions.

Straight Line Method (SLM)

Definition

Straight Line Method — A depreciation method that charges an equal amount of depreciation in each year of an asset’s useful life.

SLM inputs

  • Acquisition cost: Purchase value plus related costs, such as transportation, installation and pre-operating expenses.
  • Salvage value: The value expected to be realised at the end of the asset’s useful life.
  • Depreciable amount: Acquisition cost less salvage value.
SLM depreciation and depreciation rate
A=P+ED=A−Sd=DLr=dD×100\begin{aligned} A &= P+E \\ D &= A-S \\ d &= \frac{D}{L} \\ r &= \frac{d}{D}\times 100 \end{aligned}

Let PP be purchase value, EE other acquisition expenses, SS salvage value, and LL useful life in years.

Calculate SLM depreciation in a spreadsheet

  1. Add purchase cost, installation expenses and pre-operating expenses to find acquisition cost.
    A=P+I+OA=P+I+O
  2. Subtract salvage value from acquisition cost to find the total depreciable amount.
    D=A−SD=A-S
  3. Divide the depreciable amount by useful life in years to obtain annual SLM depreciation.
    d=DLd=\frac{D}{L}
  4. Use the spreadsheet SLN function with cost, salvage value and useful life as its inputs.
    SLN⁡(A,S,L)\operatorname{SLN}(A,S,L)
Spreadsheet layout and entries
ColumnHeadingEntry or formula
AAsset NameEntered directly
BDate of PurchaseEntered directly
CDate of InstallationEntered directly
DCost of PurchaseEntered directly
EInstallation ExpensesEntered directly
FPre-operating ExpensesEntered directly
GCost to Use=D5+E5+F5=D5+E5+F5
HSalvage ValueEntered directly
ILife in YearsEntered directly
KDepreciation AmountSLN function; example reference: =SLN⁡(G5,H11,I11)=\operatorname{SLN}(G5,H11,I11)

Date cells in the example sheet

  • Year beginning date: Entered in cell C3C3.
  • Year end date: Entered in cell F3F3.
  • First-half year end date: Entered in cell E3E3.
M/s GUNGUN Ltd.: SLM example data for 2008–09
AssetPurchase dateInstallation datePurchase costInstallation expensesPre-operating expensesCost to useSalvage valueLifeAllowed depreciationDepreciation
CNC Machine11-Jul-0817-Jul-08Rs. 877,000Rs. 11,000Rs. 3,000Rs. 891,000Rs. 45,0007 years100%Rs. 120,857.14
Packing Machine03-May-0607-May-06Rs. 123,000Rs. 8,000Rs. 2,500Rs. 133,500Rs. 17,0007 years100%Rs. 16,642.86
Worked example

Using SLM, calculate annual depreciation for M/s GUNGUN Ltd.’s CNC Machine and Packing Machine.

  1. Calculate the CNC Machine's cost to use by adding purchase cost and related expenses.
    877000+11000+3000=891000877000+11000+3000=891000
  2. Subtract salvage value and divide by its useful life to find annual SLM depreciation.
    891000−450007=120857.14\frac{891000-45000}{7}=120857.14
  3. Calculate the Packing Machine's cost to use, then its annual depreciation.
    123000+8000+2500=133500,133500−170007=16642.86123000+8000+2500=133500,\qquad \frac{133500-17000}{7}=16642.86
Answer: CNC Machine: Rs. 120,857.14; Packing Machine: Rs. 16,642.86.
Practice

An asset costs Rs. 250,000, has a salvage value of Rs. 25,000 and a useful life of 5 years. Find annual SLM depreciation and the depreciation rate.

Hint: Find the depreciable amount first, then divide it by useful life.
Key step: 250000−25000=225000250000-25000=225000; 225000÷5=45000225000\div5=45000; 45000÷225000×100=20%45000\div225000\times100=20\%.
Solution: Annual depreciation = Rs. 45,000; depreciation rate = 20%.
Practice

Purchase cost is Rs. 180,000, installation expenses are Rs. 12,000, pre-operating expenses are Rs. 8,000, salvage value is Rs. 20,000 and useful life is 6 years. Find annual SLM depreciation.

Hint: Include all listed expenses in acquisition cost before deducting salvage value.
Key step: 180000+12000+8000=200000180000+12000+8000=200000; (200000−20000)÷6=30000(200000-20000)\div6=30000.
Solution: Annual SLM depreciation = Rs. 30,000.
⚠ Common mistake

Do not divide purchase cost alone by useful life: first add relevant acquisition expenses, then subtract salvage value before dividing by useful life.

Quick recap

  • Fixed assets are long-term resources and include both tangible and intangible assets.
  • Depreciation recognises the cost of a fixed asset consumed over its useful life; freehold land is usually not depreciated.
  • Depreciable amount equals acquisition cost less salvage value.
  • SLM charges equal annual depreciation: depreciable amount divided by useful life.
  • Spreadsheet SLN uses cost, salvage value and useful life to calculate SLM depreciation.
  • Asset registers, depreciation sheets and fixed-asset schedules support annual balance-sheet reporting.
Lecture 4

Asset Accounting Part-Il

Written Down Value (WDV) Method

  • Method: The Written Down Value method calculates depreciation using the asset’s current book value as the base for the next period.
  • Declining balance: WDV is also called the Declining Balance method.
  • Excel function: The DB function calculates depreciation under this method.

WDV Spreadsheet Inputs and Timing

WDV Spreadsheet Formulas
G5=D5+E5+F5J5=IF⁡(MONTH⁡(C5)>3,YEAR⁡(F3)−YEAR⁡(C5),YEAR⁡(F3)−YEAR⁡(C5)+1)K5=ROUND⁡((L5−C5)/30,0)L5=IF⁡(AND⁡(MONTH⁡(C5)>0,MONTH⁡(C5)<4),DATE⁡(YEAR⁡(C5),3,31),DATE⁡(YEAR⁡(C5)+1,3,31))M5=DB⁡(G5,H5,I5,J5,K5)\begin{aligned}G5&=D5+E5+F5\\J5&=\operatorname{IF}(\operatorname{MONTH}(C5)>3,\operatorname{YEAR}(F3)-\operatorname{YEAR}(C5),\operatorname{YEAR}(F3)-\operatorname{YEAR}(C5)+1)\\K5&=\operatorname{ROUND}((L5-C5)/30,0)\\L5&=\operatorname{IF}(\operatorname{AND}(\operatorname{MONTH}(C5)>0,\operatorname{MONTH}(C5)<4),\operatorname{DATE}(\operatorname{YEAR}(C5),3,31),\operatorname{DATE}(\operatorname{YEAR}(C5)+1,3,31))\\M5&=\operatorname{DB}(G5,H5,I5,J5,K5)\end{aligned}

Use these formulas to prepare the asset inputs and calculate depreciation.

Worked example

Find the WDV depreciation for the CNC Machine and Packing Machine for FY 2008–09 using the DB spreadsheet.

  1. For the CNC Machine, calculate the cost to use from its purchase cost, installation expenses and pre-operating expenses.
    877000+11000+3000=891000877000+11000+3000=891000
  2. Use the given salvage value, life, period and first-year months as the remaining DB inputs.
    DB⁡(891000,45000,7,1,9)\operatorname{DB}(891000,45000,7,1,9)
  3. For the Packing Machine, use its cost to use and the stated DB inputs.
    DB⁡(133500,17000,7,3,11)\operatorname{DB}(133500,17000,7,3,11)
Answer: CNC Machine: Rs. 231,882.75. Packing Machine: Rs. 19,433.37.

Schedule Forming Part of the Balance Sheet

Balance-Sheet Schedule Formulas
Closing gross block=Opening gross block+Additions−DeductionsClosing depreciation=Opening depreciation+Depreciation additions−Depreciation deductionsOpening net block=Opening gross block−Opening depreciationClosing net block=Closing gross block−Closing depreciation\begin{aligned}\text{Closing gross block}&=\text{Opening gross block}+\text{Additions}-\text{Deductions}\\\text{Closing depreciation}&=\text{Opening depreciation}+\text{Depreciation additions}-\text{Depreciation deductions}\\\text{Opening net block}&=\text{Opening gross block}-\text{Opening depreciation}\\\text{Closing net block}&=\text{Closing gross block}-\text{Closing depreciation}\end{aligned}

Apply the same calculations to each fixed-asset category.

Illustrative Balance-Sheet Schedule (Rs.)
AssetOpening grossGross additionsGross deductionsClosing grossOpening depreciationDepreciation additionsDepreciation deductionsClosing depreciationOpening net blockClosing net block
Building12,081.000.000.0012,081.001,812.15604.050.002,416.2010,268.859,664.80
Plant & Machinery13,942.002,765.0014.0016,693.004,182.60166.930.004,349.539,759.4012,343.47
Office & Other Equipment2,894.00616.003.003,507.00868.20350.700.001,218.902,025.802,288.10
Furniture & Fixtures1,681.00777.000.002,458.00504.30245.800.00750.101,176.701,707.90
Vehicles927.00483.0024.001,386.00370.80277.200.00648.00556.20738.00
Practice

An asset has an opening gross block of Rs. 50,000, additions of Rs. 8,000 and deductions of Rs. 1,500. Find its closing gross block.

Hint: Add additions to the opening gross block, then subtract deductions.
Key step: 50,000+8,000−1,50050,000+8,000-1,500
Solution: Closing gross block = Rs. 56,500.
Practice

Opening gross block is Rs. 80,000 and opening depreciation is Rs. 12,000. Closing gross block is Rs. 90,000 and closing depreciation is Rs. 18,500. Find the opening and closing net blocks.

Hint: For each date, subtract depreciation from the matching gross block.
Key step: 80,000−12,00080,000-12,000 and 90,000−18,50090,000-18,500
Solution: Opening net block = Rs. 68,000; closing net block = Rs. 71,500.

Quick recap

  • WDV/DB: Each period’s depreciation uses the current book value; Excel’s DB function calculates the amount.
  • Cost to use: Add purchase cost, installation expenses and pre-operating expenses.
  • Timing inputs: Installation date determines the period, first-year end date and first-year months.
  • DB inputs: Cost, salvage value, useful life, period and first-year months.
  • Gross block: Closing amount = opening amount + additions − deductions.
  • Depreciation and net block: Closing depreciation adjusts for additions and deductions; net block = gross block − depreciation.
Lecture 5

Loan Repayment Schedule

Loan and Repayment

  • Loan: A loan is borrowed principal for a specified period at a pre-agreed interest rate.
  • Repayment: The borrower repays the loan through periodic instalments, usually monthly, over the repayment period.

Using PMT to Calculate Instalments

PMT Function Parameters
ParameterMeaning
RateInterest rate for each payment period.
NperTotal number of payments; its time unit must match the rate.
PvPresent value of the loan, i.e. the amount borrowed.
FvFuture value or remaining balance at the end; usually 00 for a fully repaid loan.
Type11 means payment at the beginning of a period; 00 means payment at the end.
PMT Function
PMT⁡(rate,nper,pv,fv,type)\operatorname{PMT}(\text{rate},\text{nper},\text{pv},\text{fv},\text{type})

Use the parameters in this order. In Excel, a negative present value can be used so the payment is displayed as a positive amount.

Spreadsheet Layout and Formulas

Loan Repayment Spreadsheet Columns
ColumnHeadingEntry or formula
ALoan amountEnter the loan amount.
BLoan disbursement dateEnter the date.
CPeriod of loan (years)Enter the period.
DRate of interestEnter the annual rate.
EFuture valueEnter 00 when the loan is fully repaid.
FYearly instalment amount=PMT⁡(D6,C6,−A6,0,1)=\operatorname{PMT}(D6,C6,-A6,0,1)
GMonthly instalment=F6/12=F6/12
Monthly Instalment in the Spreadsheet
Monthly instalment=Yearly instalment12\text{Monthly instalment}=\frac{\text{Yearly instalment}}{12}

The given layout first calculates the yearly instalment, then divides it by 1212.

Worked example

What are the yearly and monthly instalments?

  1. A loan of Rs. 1,00,000 is given on 1 April 2007 for 2 years at 10% interest. Find the instalments using the spreadsheet layout.
    A6=100000,C6=2,D6=10%,E6=0A6=100000,\quad C6=2,\quad D6=10\%,\quad E6=0
  2. Calculate the yearly instalment using the PMT formula with type 1, meaning payment at the beginning of each period.
    F6=PMT⁡(10%,2,−100000,0,1)=52380.95F6=\operatorname{PMT}(10\%,2,-100000,0,1)=52380.95
  3. Divide the yearly instalment by 12 to obtain the monthly instalment.
    G6=52380.9512=4365.08G6=\frac{52380.95}{12}=4365.08
Answer: Yearly instalment = Rs. 52380.95\text{Rs. }52380.95; monthly instalment = Rs. 4365.08\text{Rs. }4365.08.
Examples in the Loan Schedule
LoanPeriod and rateFuture valueYearly instalmentMonthly instalment
Rs. 1,00,000; disbursed 1 April 20072 years; 10%0.000.00Rs. 52,380.95Rs. 4,365.08
Rs. 2,50,000; disbursed 15 May 20083 years; 11%0.000.00Rs. 92,165.11Rs. 7,680.43
✎ Exam tip

Check the payment timing and time units before using PMT. The given spreadsheet formula uses annual rate and years, and sets type to 11.

Practice

A loan of Rs. 2,50,000 is taken for 3 years at 11%. Using the given schedule approach, what are the yearly and monthly instalments?

Hint: Use the PMT inputs: rate 11%11\%, nper 33, pv −250000-250000, fv 00, type 11; then divide by 1212.
Key step: PMT⁡(11%,3,−250000,0,1)=92165.11\operatorname{PMT}(11\%,3,-250000,0,1)=92165.11; divide by 1212.
Solution: Yearly instalment = Rs. 92,165.11; monthly instalment = 92165.11/12=Rs. 7680.4392165.11/12=\text{Rs. }7680.43.
Practice

In the PMT function, what does type 00 indicate, and why is future value commonly entered as 00 for a fully repaid loan?

Hint: Consider payment timing and the balance remaining after the final payment.
Key step: Type 00 means payment at the end of each period; a fully repaid loan has no closing balance.
Solution: Type 00 indicates payment at the end of the period. Future value is 00 because regular repayments leave no amount payable at the end of the loan term.

Quick recap

  • A loan is borrowed principal repaid through instalments over an agreed period at a stated rate.
  • PMT uses rate, number of payments, present value, future value and payment timing.
  • The rate and number of payments must use matching time units.
  • Future value is zero when the loan is fully repaid.
  • In the given spreadsheet, yearly instalment is calculated with PMT and divided by 12 for the monthly amount.
  • The spreadsheet formula uses type 1, so payment is assumed at the beginning of the period.
Lecture 6

Summary

Spreadsheet Applications and Planning

Definition

Spreadsheet application — A spreadsheet application uses entered values and formulas to carry out business calculations.

Planning a spreadsheet

  • Applications: The chapter illustrates payroll calculation, fixed asset accounting, and loan repayment scheduling.
  • First step: List the elements needed for the application.
  • Classify each element: Decide whether its value is entered directly or calculated with a formula.
  • Built-in functions: Excel provides functions, including financial functions, for calculations; a formula may use one or more functions.
Worked example

How should the elements of a spreadsheet application be planned?

  1. When planning a spreadsheet application, first list its required elements.
  2. For each element, mark whether it is entered directly or computed using a formula.
Answer: List the elements and identify each as a direct input or a formula-based calculation.

Absolute Addresses and the IF Function

Definition

Absolute address — An absolute address keeps a cell reference fixed when a formula containing it is copied to other cells.

Key uses

  • Absolute reference: Use it when the referenced cell’s content must not change as the formula is copied; for example, `F1`.
  • IF function: Use it to produce different actions or results according to different conditions.

Fixed Asset Depreciation

Excel depreciation functions
FunctionDepreciation method
SLNStraight Line Method (SLM)
DBWritten Down Value Method (WDVM), also called the declining balance (DB) method

Loan Repayment Schedule

Definition

PMT function — Excel’s PMT function is used to compute a loan repayment schedule.

Practice

Which spreadsheet applications are illustrated in the chapter?

Hint: Recall the three business examples.
Solution: Payroll calculation, fixed asset accounting, and loan repayment scheduling.
Practice

Which functions are used for SLM and WDVM depreciation?

Hint: Match each method to its Excel function.
Solution: SLN is used for SLM; DB is used for WDVM, also called the declining balance method.
Practice

What is the purpose of the PMT function?

Hint: Consider the loan repayment application.
Solution: It is used to compute a loan repayment schedule.

Quick recap

  • List the required spreadsheet elements and distinguish direct inputs from formula-based calculations.
  • Excel has built-in functions, including financial functions; formulas can use one or more functions.
  • An absolute address keeps a cell reference fixed when its formula is copied.
  • The IF function supports different actions for different conditions.
  • SLN is for Straight Line Method depreciation; DB is for Written Down Value (declining balance) depreciation.
  • PMT is used to compute a loan repayment schedule.

Formula sheet

Dearness Allowance base
DA=rDA(BP+DP)\mathrm{DA}=r_{\mathrm{DA}}(\mathrm{BP}+\mathrm{DP})
Provident Fund base
PF=rPF(BP+DP)\mathrm{PF}=r_{\mathrm{PF}}(\mathrm{BP}+\mathrm{DP})
Gross and net salary
Gross Salary=BP+DP+DA+HRA+TRA+Other EarningsTotal Deductions=PT+PF+TDS+LOAN+Other DeductionsNet Salary=Gross Salary−Total Deductions\begin{aligned}\mathrm{Gross\ Salary}&=\mathrm{BP}+\mathrm{DP}+\mathrm{DA}+\mathrm{HRA}+\mathrm{TRA}+\mathrm{Other\ Earnings}\\\mathrm{Total\ Deductions}&=\mathrm{PT}+\mathrm{PF}+\mathrm{TDS}+\mathrm{LOAN}+\mathrm{Other\ Deductions}\\\mathrm{Net\ Salary}&=\mathrm{Gross\ Salary}-\mathrm{Total\ Deductions}\end{aligned}
Total depreciable amount over an asset’s life
A−SA-S
SLM depreciation and depreciation rate
A=P+ED=A−Sd=DLr=dD×100\begin{aligned} A &= P+E \\ D &= A-S \\ d &= \frac{D}{L} \\ r &= \frac{d}{D}\times 100 \end{aligned}
WDV Spreadsheet Formulas
G5=D5+E5+F5J5=IF⁡(MONTH⁡(C5)>3,YEAR⁡(F3)−YEAR⁡(C5),YEAR⁡(F3)−YEAR⁡(C5)+1)K5=ROUND⁡((L5−C5)/30,0)L5=IF⁡(AND⁡(MONTH⁡(C5)>0,MONTH⁡(C5)<4),DATE⁡(YEAR⁡(C5),3,31),DATE⁡(YEAR⁡(C5)+1,3,31))M5=DB⁡(G5,H5,I5,J5,K5)\begin{aligned}G5&=D5+E5+F5\\J5&=\operatorname{IF}(\operatorname{MONTH}(C5)>3,\operatorname{YEAR}(F3)-\operatorname{YEAR}(C5),\operatorname{YEAR}(F3)-\operatorname{YEAR}(C5)+1)\\K5&=\operatorname{ROUND}((L5-C5)/30,0)\\L5&=\operatorname{IF}(\operatorname{AND}(\operatorname{MONTH}(C5)>0,\operatorname{MONTH}(C5)<4),\operatorname{DATE}(\operatorname{YEAR}(C5),3,31),\operatorname{DATE}(\operatorname{YEAR}(C5)+1,3,31))\\M5&=\operatorname{DB}(G5,H5,I5,J5,K5)\end{aligned}
Balance-Sheet Schedule Formulas
Closing gross block=Opening gross block+Additions−DeductionsClosing depreciation=Opening depreciation+Depreciation additions−Depreciation deductionsOpening net block=Opening gross block−Opening depreciationClosing net block=Closing gross block−Closing depreciation\begin{aligned}\text{Closing gross block}&=\text{Opening gross block}+\text{Additions}-\text{Deductions}\\\text{Closing depreciation}&=\text{Opening depreciation}+\text{Depreciation additions}-\text{Depreciation deductions}\\\text{Opening net block}&=\text{Opening gross block}-\text{Opening depreciation}\\\text{Closing net block}&=\text{Closing gross block}-\text{Closing depreciation}\end{aligned}
PMT Function
PMT⁡(rate,nper,pv,fv,type)\operatorname{PMT}(\text{rate},\text{nper},\text{pv},\text{fv},\text{type})
Monthly Instalment in the Spreadsheet
Monthly instalment=Yearly instalment12\text{Monthly instalment}=\frac{\text{Yearly instalment}}{12}

Accountancy — ALL CHAPTERS

1

Accounting for Share Capital

View

2

Issue and Redemption of Debentures

View

3

Financial Statements of a Company

View

4

Analysis of Financial Statements

View

5

Accounting Ratios

View

6

Cash Flow Statement

View

1

Overview Of Computerised Accounting System

View

2

Spreadsheet

View

3

Use Of Spreadsheet In Business Applications

View

4

Graphs And Charts For Business Data

View

1

Accounting for Partnership: Basic Concepts

View

2

Reconstitution of a Partnership Firm –Admission of a Partner

View

3

Reconstitution of a Partnership Firm –Retirement/Death of a Partner

View

4

Dissolution of Partnership Firm

View