EXCEL

PAYROLL STATEMENT

Ques 1. Prepare a payroll statement for the month of Feb, 2008 in the following format
on the basis of following terms and conditions:

Name Category Basic DA HRA Gross PF IT Net

.
.

Terms & Conditions:

a) DA is to be calculated as 150% of basic for those having a basic salary up to
16,000 and 100% for those having more than 16,000 with a condition that he must
get a minimum DA of 24,000.
b) HRA for employee of category “A” is Rs. 1600, for “B” is Rs. 1200, and for “C”
is Rs. 1000.
c) Gross salary is the total of these three.
d) 25% of gross salary is deducted as income tax.
e) Employee needs to contribute 10% of basic and DA in PF.
f) Net salary will be calculated after deducting PF and ITAX from gross.
Also plot the Gross salary in the form of composite histogram.

Ques 2. Prepare a detailed generalized Payroll Report for a ltd company employing 10
workers for the month of March, 2005, given the following information:
 Employees are entitled to Basic, DA, HRA, CCA and Bonus.
 DA is payable @ 17% on Basic.
 HRA paid is Rs.3300 for Basic<15000 and Rs.5500 for others.
 CCA is Rs. 800 for employees living in category ‘A’ cities, Rs. 500 for
employees living in category ‘B’ cities, and Rs. 300 for employees living in
category ‘C’ cities.
 Bonus is payable @8.33% of (basic plus DA) and should not exceed Rs.2000.
 PF is deductible @10% of basic and DA.
 Income Tax is deductible @ 10% of gross salary.
Also plot the Gross salary in the form of composite histogram.

Ques 3. M/s Alpha Ltd pays to its employees the salary on the basis of Basic pay. The
Gross salary of an employee consists of Dearness Pay (D.P) and House Rent Allowance
(H.R.A). The components of salary are computed according to the following terms of
contract of service:
 Dearness Allowance is paid @ 10% of basic pay subject to a
maximum of Rs. 5400.
  HRA is computed as per the following scale:
Basic Pay HRA (Rs.)
Upto Rs. 10,000 5,000
From 10,001 to 25,000 7,000
More than 25,000 9,000

Required: Prepare payroll of 10 employees M/s Alpha Ltd for the month of March
2006 , in the format given below according to above terms of contract .Fill information
about Employee Identification (EmpId), Name of Employees and their Basic Pay on your
own.

Payroll for the Month of March 2012

EmpId Name of Employees Basic DA HRA Gross

Total

Ques 4. M/s Alpha Ltd pays to its employees the salary on the basis of Basic pay. The
Gross salary of an employee consists of Dearness Pay (D.P) and House Rent Allowance
(H.R.A). The components of salary are computed according to the following terms of
contract of service:
 Dearness Allowance is paid @ 10% of basic pay subject to a maximum
of Rs. 5400.
 HRA is computed as per the following scale:
Group HRA(Rs.)
A 5,000
B 7,000
C 9,000
However, it is subject to a maximum of 40% of the basic pay.
You are given employee identification (EmpId), Name of employees, Group and the
basic pay for the month of April 2005.

Payroll for the Month of March 2012

EmpId Name of Employees Basic DA HRA Gross

Total

Required: Prepare payroll of 10 employees M/s Alpha Ltd for the month of March
2006 , in the format given below according to above terms of contract . Fill information
about Employee Identification (EmpId), Name of Employees and their Basic Pay on your
own.

Ques 5. M/s Alpha Ltd pays to its employees the salary on the basis of Basic pay. The
Gross salary of an employee consists of Dearness Pay (D.P) and House Rent Allowance
(H.R.A). The components of salary are computed according to the following terms of
contract of service:
 Dearness Allowance is paid @ 10% of basic pay subject to a
minimum of Rs. 5400.
 HRA is computed as per the following scale:
Basic Pay HRA (Rs.)
Up to Rs. 10,000 10%
From Rs. 10,001 to Rs. 25,000 20%
More than 25,000 30%

Required: Prepare payroll of 10 employees M/s Alpha Ltd for the month of March
2006 , in the format given below according to above terms of contract .Fill information
about Employee Identification (EmpId), Name of Employees and their Basic Pay on your
own.

Payroll for the Month of March 2012

EmpId Name of Employees Basic DA HRA Gross

Total

Ques 6. A company XYZ Ltd. pays a monthly salary to its employees which consists of
basic salary, allowance & deductions. The details of allowances and deductions
are as follows:
Allowance
 HRA Dependent on Basic
30% of Basic if Basic <=1000
25% of Basic if Basic>1000 & Basic<=3000
20% of Basic if Basic >3000
 DA Fixed for all employees, 30% of Basic
 Conveyance Allowance Rs. 50/-if Basic is <= 1000
Rs. 75/- if Basic >1000 & Basic<=2000
Rs. 100 if Basic >2000
 Entertainment Allowance NIL if Basic is <= 1000
Rs.100/-if Basic > 1000
 Deductions
Provident Fund 6% of Basic
Group Insurance Premium Rs. 40/- if Basic <= 1500
Rs. 60/- if Basic > 1500 & Basic<=3000
Rs.80/- if Basic >3000
Calculate the following:
Gross Salary = Basic + HRA + DA + Conveyance + Entertainment
Total deduction = Provident Fund + Group Insurance Premium
Net Salary = Gross Salary - Total Deduction
Ques 7. Prepare a generalized worksheet of Prasad & Sons Steel Pvt. Ltd. to produce a
Payroll Statement for the month of Feb. 2007 for five employees in the given format:

S.No. Name Basic HRA TA DA Net

.
.
The following relevant information is given:
Employee is entitled for Basic pay, HRA, DA, TA. Net salary is total of these
three.
HRA, DA, TA will be calculated on the following criteria:
Basic DA TA HRA
< 16,000 15% of basic 30% of basic 45% of basic
≥ 16,000 & ≤20,000 30% of basic 45% of basic 60% of basic
>20,000 45% of basic 60% of basic 75% of basic

Ques 8. Create a payroll sheet in which Basic pay of 20 employees is given. You have to
calculate DA, HRA, CCA and gross pay of each employee using the formulas with
following conditions:
a) HRA is 50 % of Basic pay, and CCA is 15 % of Basic Pay.
b) The rates of DA for different levels of Basic Pay are given in cells G to I as
G H I
1 Lower limit Upper limit DA rate
2 0 6000 0.28
3 6001 12000 0.25
4 12001 & Above 0.20

You would also have to ensure that DA amount paid to an employee with lower Basic
Pay should not exceeded the DA paid to an employee with higher Basic Pay. Also
ensure that that the worksheet should calculate the correct figures in case the DA rate
or the Basic Pay levels for which it is applicable are altered.

Also Create a Stacked Bar chart on different sheet in the same workbook showing the
breakup of gross pay.

Ques 9. M/s GOVIND Ltd pays to its employees the salary on the basis of Basic pay. The
Gross salary of an employee consists of Dearness Pay (D.P) and House Rent
Allowance (H.R.A). The components of salary are computed according to the following terms
of contract of service:
 Dearness Allowance is paid @ 25% of basic pay subject to a
minimum of Rs.10,500.
 TA is paid Rs.4000 if the distance the employee covers is up to 10
KMs for others it is Rs.5500. (assume distance on your own)
 HRA is computed as per the following:
 Salary up to Rs.20000 Rs.7000
Salary up to Rs.40000 Rs.8000
Salary More than Rs.40000 Rs.10000
However in no case it can be more than 30% of basic pay
 PF deduction is 15% of (Basic+DA) rounded to nearest 100.
 IT is 12% Flat on (Gross- PF-20%of TA)
You are given Employee Identification (EmpId, Name of Employees and their
Basic Pay in the range of 15000-50000(use any value in multiple of Rs.1000).
Prepare Payroll statement as per following schedule.

Payroll for the Month of Apr 2012

EmpId Name of Basic DA HRATA Gross PF IT Net
Employees salary

Total

Ques9. Beta Ltd. pays to its employee’s basic salaries in the range of 15000-35000 per
month. (Assume randomly in multiples of 1000). It also pays
 Dearness allowance 22% of basic pay or 4500 whichever is maximum
 House Rent Allowance 20% if the basic pay is up to 25000(minimum
Rs.4000) afterwards 30% (minimum Rs.8000)
 Contribution towards Provident Fund is 20% of (basic+DA) rounded to
nearest 100.
 Income Tax is 15% of (gross- PF) rounded to nearest 10.
 Net salary is (GROSS-PF-IT)

Prepare the payroll statement (assuming 10 employees) as follows:

Payroll for the Month of March 2012

EmpId Name of Employees Basic DA HRA Gross PF IT N. Salary

Total

Ques 10. Prepare a detailed generalized Payroll Report for A Ltd company employing 10
workers for the month of March, 2011, given the following information in the following
format:
Emp Name Basic DA HRA Bonus Gross PF Income Net
Id Tax Salary

TOTAL

1. Emplyees are entitled to Basic, DA, HRA and Bonus.

 2. DA:
BASIC DA
UPTO 20,000 MINIMUM OF 10% OR 1,750
BETWEEN 20,000 AND 40,000 MINIMUM OF 15% OR 4,750
ABOVE 40,000 MINIMUM OF 20% OR 10,000

3. HRA is Rs 16,500 for Basic<20,000 and Rs 28,000 for others.

4. Bonus is payable @ 12.5% of (Basic + DA).
5. PF is deductible @ 10% of (Basic).
6. Income Tax is deductible @ 10% of Gross Salary.
Prepare a stacked bar chart to depict various elements of gross salary as computed.

Ques11.M/s Alpha Ltd pays to its employees the salary on the basis of Category. The
Gross salary of an employee consists of Basic Salary + Dearness Pay + DA + HRA +
TA + CCA. The GPF and IT are the two Deductions. The components of salary are
computed according to the following terms of contract of service:

Rules for payment of Basic Salary, TA, and CCA are as under:
Basic
Category Salary TA CCA
A 25000 5000 1000
B 20000 4000 800
C 15000 3000 500

Rules for payment of DP, DA and HRA are as under:

DP 50% of Basic
DA 41% of (Basic +DP)
HRA 30% of (Basic +DP)

Rules for Deduction of GPF and IT are as under:

GPF 10% of Gross
IT 8% of Gross

Ques12. You are required to prepare a payroll statement in the given format making
maximum use of cell referencing facility:
0.5 0.41 0.3 0.08 0.1
Is
HRA is
to be Total
Code Name Cat Paid Basic DP DA HRA TA CCA Gross IT GPF Ded Net
1 Y
2 N
3
4
Total
 DEPRECIATION

Ques1. Prepare a Spreadsheet in MS EXCEL which accepts Cost of Asset, Life of Asset,
Rate of Depreciation and the as input and produces a comparative schedule of
Depreciation as output in the following format:

Cost of Asset :
Life of asset :
Salvage Value :
Months :
Depreciation Method: [Select Method]
Fixed Asset Account
Year Opening Depreciatio Closing
n
Balance Balance

Choice of methods: Straight Line, Declining Balance

Ques2.Given below are the particulars of a Plant and M/c purchased this year:

COST of Plant & M/c 200,000.00

SALVAGE Value 5,000.00
LIFE of Plant & M/c (1-40
years) 5
MONTHs in the first (year 1-
12) 12

You are required to prepare a generalized depreciation schedule in the given format:

DEPRICIATION SCHEDULE
PERIO SLN
DB METHOD
D METHOD
1
2
 3
4
5
Total

Ques3. M/s Philips Associates is planning to buy fixed assets, the details of which are
given below:
Date Particulars Cost Life Salvage Method
1-Aug-11 Air Conditioners 4,50,000 10 50,000 Straight Line
1-Sep-11 Plant and Machinery 9,50,000 15 80,000 Diminishing Bal.
1-Dec-11 Boring Equipments 7,50,000 12 10,000 Diminishing Bal.
1-Feb-12 Road Rollers 27,25,000 20 1,00,000 Straight Line
1-Mar-12 Lifting Cranes 89,99.000 25 5,00,000 Diminishing Bal.

Required:
Using MS Excel, develop a statement of depreciation that calculates the amount of
depreciation chargeable to profit and loss account during the financial years 2011-12
onwards till 2016-17 in respect of above fixed assets. The generalization aspect of this
statement should be restricted to the changes in details of fixed assets.
 RATIOS

Ques1. Following is the balance sheet of XYZ Ltd. as on 31st December.

LIABILITIES Rs. ASSETS Rs.

Equity share capital 24,000 Machinery & Equipment 50,00
0
P&L Account 6,000 Stock 12,00
0
10% debentures 15,000 Sundry Debtors 4,00
0
Sundry creditors 23,400 Cash at bank 2,28
0
Provision for taxation 600 Prepaid expenses 72
0
Total 69,000 69,00
0

Calculate Liquidity ratio and Current ratio.

Ques2. The Balance Sheets of M/s ABC Ltd. for year ending 2003 and 2004.

BALANCE SHEET
Liabilities 2003 2004 Assets 2003 2004
Sundry Creditors 226000 33000 Cash 131000 27600
0 0
Bills Payable 239000 20000 Marketable 90000 66000
Securities
Outstanding 75000 12000 Accounts 424000 15200
Expenses 0 Receivable 0
Long Term Debt 400000 38000 Inventories 145000 19100
0 0
Equity Share Capital 500000 50000 Fixed Assets 800000 83000
 0 0
Share Premium 30000 30000 Less: Depreciation 20000 35000
Retained Earnings 100000 10000 Net Fixed Assets 780000 79500
0 0
157200 14820 239200 234700
3 04 3 4

Additional Information 2003 2004

Sales Revenue 1500000 1550000
Cost of Goods Sold 1000000 1030000
Net Profit Before Taxes 62000 66000
Taxes 10000 12000

Required :
Prepare a spread sheet in MS EXCEL to calculate various financial ratios viz., Current
Ratio, Quick Ratio, Inventory Turnover, Debtors Turnover Ratio, Fixed Asset Turnover,
Gross Profit Ratio, Net Profit Ratio for both the years besides giving a comment:
Improvement or Deterioration

Ques3. The following is the revenue statement of A Ltd. for year ending 31 st March,
2009.

Sales 6,00,000
Less: Cost of goods Sold 4,00,000
Gross Profit 2,00,000
Less: Operating Expenses 1,20,000
Operating Profit 80,000
Add: Non-operating Income 12,000
92,000
Less: Non-operating Expenses (Interest) 4,000
Net Profit 88,000
Less: Tax @ 50 per cent 44,000
Profit after Tax 44,000

Number of paid-up equity shares are 10,000

Calculate: Gross Profit Ratio

 FREQUENCY

Ques1. Prepare a spread sheet in MS EXCEL to classify 50 given numbers (varying

between 1 to 100, Generated at Random) according to the following class intervals of 10
starting with 1-10,and ending with 81 and Above. Compute the statistical parameters
such as mean and standard deviation both on the basis of discrete data and above
frequency distribution. Before Preparing frequency table fix the random numbers in
another area and use fixed numbers to prepare frequency table.

Ques2. Prepare a spread sheet in MS EXCEL to classify 50 given numbers (varying

between 1 to 100, Generated at Random) according to the following class intervals.
Before creating frequency distribution fix the random numbers.

Class Intervals Frequency

0 – 20
20 – 40
40 – 60
60 – 80
80 & above
Total

Required:
 Prepare a pie chart for the above frequency distribution
 Compute the statistical parameters such as mean and standard deviation both
on the basis of discrete data and above frequency distribution.
 Find the sum of numbers (out of above 50 fixed integer numbers) having
values more than 65.
 Find out skewness of above 50 fixed integer numbers
 Find out Kurtosis of above 50 fixed integer numbers
 Find out how many values are more than 560
 REGRESSION ANALYSIS

Ques1. Develop a spreadsheet in MS EXCEL to compute the standard regression

estimates for the following set of data:

X Y Estimated Y
90 38
76 29
97 46
109 49
93 39
85 36
90 42

You are required to find the estimated values of Y series, given that Y=a + bX. What
shall be the value of Y when the value of X=70. Give an appropriate graphical
representation of the actual and estimated series of Y.

Ques2. Develop a spread sheet to conduct the following trend series analysis by
utilizing the standard technique of least square regression.

Years Actual Output Estimated Output

1995 69
1996 72
1997 77
1998 82
1999 91
2000 85
2001 97
2002 104
2003 110
2004 117
 2005 127

What shall be trend value of output for the year 2007? Prepare a suitable graph to depict
the actual and estimated output year-wise.

Ques3. You are given the output of a product for the following four quarters.
I- Jan to Mar; II Apr to Jun; III Jul to Sep; IV Oct to Dec
Year I II III IV Total
1997 34 54 98 224
1998 37 58 93 228
1999 39 56 95 236
2000 36 59 99 250
2001 33 53 87 256
2002 37 58 96 254
2003 38 50 92 290
2004 37 55 94 280

Required:
Develop a spreadsheet to conduct the trend series analysis for each of the above year, by
utilizing the standard technique of least square regression.
Compute the trend value of output for the year 2005 on the following basis:
(i) as the sum of predicted values of each of the quarters.
(ii) on the basis of total production of the year.
(iii) Compute the estimated output and depict the actual and estimated output
using a suitable graph.
(iv) Find the value of coefficient of correlation between years and yearly output.
(v) Find the sum of all those quarterly values that exceed 75.

Ques4. It has been observed that the degree of rainfall determines the volume of
Sugarcane which in turn effects the production of Sugar. Develop a spread sheet in MS
EXCEL to compute the estimated output of Sugarcane and Sugar for a particular region
for the following set of data:

Rainfall Sugarcane Production of

(in mm) (in tons) Sugar (in tons)
176 1802 530
98 1526 365
110 1945 482
105 2102 624
 99 1844 525
72 1665 396
102 1804 515

You required to find the estimated production levels of Sugar when the predicted
values of rainfall is 120mm. Give an appropriate graphical representation of the
estimated values of output of Sugarcane and Sugar.

Ques5. Develop a spread sheet in MS EXCEL to compute the standard regression

estimates for the following set of data:

Y X Estimate
138 90
129 76
146 97
149 109
139 93
136 85
142 90

You required to find the estimated values of X series, given that X = a + b * Y, What
shall be the value of X when the value of Y=152. Give an appropriate Graphical
representation of the actual and estimated series of Y.

Ques6. Develop a spreadsheet to conduct the following regression analysis by utilizing

the standard of regression technique.

Stats 68 70 75 78 79 73 82 86 87 89
Math
s 69 78 79 79 83 86 88 89 90 98

1. Find out the projected marks in Maths from the data given above using Slope and
Intercept.
2. Find out the Coefficient of Correlation between Stats and Maths.
3. What shall be projected marks in Maths if marks in Stats are 94?
4. Prepare a suitable graph to depict actual and estimated output year-wise.
 LOAN REPAYMENT

Ques1. Develop an appropriate spread sheet in MS EXCEL to show the repayment with
respect to a loan when the following basic input is given:
Amount of Loan:
Rate of Interest:
Period of Repayment: (in years)
Periodicity of payment: (Monthly/Quarterly/Yearly)
Installment: (Computed)
You are required to prepare the Loan repayment Schedule in the following format
(The worksheet should be generalized for all the four basic inputs.)

Loan Repayment Schedule

Opening Interest Closing
Period Installment
Balance Due Balance

Ques2. On 1-Oct-1997, Mr. Zuber borrowed Rs.5,00,000 from a bank for a period of 10
years @ 12% p.a .The terms of agreement provide that the repayment can be rescheduled
at the option of the borrower after 6 years. Mr. Zuber exercised the option and from Ist
Oct 2003 onwards the banker agreed to reschedule the repayment of loan by reducing the
interest rate to 10% p.a. Develop an appropriate spread sheet in MS EXCEL to show the
repayment with respect to a loan when the following basic input is given:
Amount of Loan:
Rate of Interest-Original:
-Rescheduled:
Period of Repayment: (in years)
Periodicity of payment: Original: (Monthly/Quarterly/Yearly/Half-yearly)
-Rescheduled: (Monthly/Quarterly/Yearly/Half-yearly)
Original Installment: (Computed)
Rescheduled Installment: (Computed)
 You are required to prepare the Loan repayment Schedule in the following
format:
Loan Repayment Schedule
Opening Interest Closing
Period Installment
Balance Due Balance

Ques3. ICICI Bank give loans to applicants where:

The rate of interest is decided on the following basis:
 8% p.a. for Category ‘A’ (installment payable yearly)
 8.5% p.a. for Category ‘B’ (installment payable half yearly)
 8.75% p.a. for Category ‘C’ (installment payable quarterly)

For a loan amount of Rs. 60000 given for 5 years:

 Prepare a generalized worksheet to show the Effective Rate, Total No
of Installments and the Equated Periodic Installment in each of three
cases

 Also determine how much ultimately total interest will be paid in each
category.

Category A B C
 Installments In a year
 Rate of Interest
 Loan Amount
 Years
 Total Number of Installments
 Effective rate of interest
 Installments Amount
 Total Payment
 Interest Payment

Category A
Installment No. Interest Installment
Payment Amt
1
2
3
4
5
TOTAL
 Category B
Installment No. Interest Installment
Payment Amt
1
2
3
4
5
6
7
8
9
10
TOTAL

Category C
Installment No. Interest Installment
Payment Amt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
TOTAL

Ques4. Ravneet borrowed an amount of Rs.100000/- at an interest rate of 12% to be

returned in 12 equal yearly installments. Prepare a generalized worksheet to calculate the
amount of installment and principal payment using financial functions and a
COMPLETE statement of account in the following format, assuming there is no default
in payment of installments.
FORMAT of statement
 Amount of loan ………
Rate of interest ………
Period of repayment in years ………
Amount of installment ………
Period O. Balance Interest Installment Principal-payment Closing Bal.
1
..
n

Ques5. Mr. Z borrowed Rs. 5,00,000 from a bank for a period of 10 years @ 12% p.a to
be repaid in equal quarterly installments. The terms of agreement provide that the
repayment can be rescheduled at the option of the borrower after paying 24 installments.
Mr. Z exercised the option and the banker agreed to reschedule the repayment of loan on
half-yearly basis by reducing the interest rate to 10% p.a. Then after paying 2
installments, Mr. Z again asks for rescheduling the terms of repayment and the banker
agrees to reschedule the repayment of loan on a yearly basis by reducing the interest
further by half a percent. Develop an appropriate spread sheet in MS EXCEL to show the
repayment with respect to a loan when the following basic input is given: (Let this
worksheet be specific to this problem only)

You are required to prepare the Loan repayment Schedule in the following format:

Original terms of repayment

Opening Interest Closing
Period Installment
Balance Due Balance

Rescheduled terms of repayment

Opening Interest Closing
Period Installment
Balance Due Balance

Re-Rescheduled terms of repayment

Opening Interest Closing
Period Installment
Balance Due Balance

Ques6. Mr. Ashok takes a loan of Rs. 5,00,000 for purchasing a car. With a down
payment of Rs. 60,000 he wishes to get his installment fixed from the first month of
loan year for a period of 5 years. Prepare a loan schedule for his loan period. Also
calculate the total amount of interest paid by the customer over the entire loan period.
(Make the worksheet generalized for loan amount, rate of interest, period and
periodicity).

Ques7. A Finance Company has advanced loans to six clients whose crtitical details are
given in the following format:

Client Id Name of Borrower Principal Rate % p. a Period in

Years

(All the loans are repayable in equated installments.)

You are required to compute the Projected Interest Income of Finance Company for Next
ten Quarters in the following format:

Period 1 2 3 4 5 6 7 8 9 10
Client Id

Total

And hence calculate the Total interest earned by the finance company in the 4 th, 5th and 6th
month of business from all the clients in the following format:

Client ID Cumulative interest earned in the 4th

5th
and 6th month
1
2
3
TOTAL

Ques8. A Finance Company has advanced loans to six clients whose critical details are
given in the following format:

Client Id Name of Borrower Principal Rate % p. a Period in

Years
 (All the loans are repayable in equated installments.)

You are required to compute the projected outstanding amount of Loans payable by
clients to be shown in Balance Sheet of Finance Company for Next Six Years in the
following format:

Period 1 2 3 4 5 6
Client Id

Total

Ques9. You are required to prepare a generalized Loan repayment Schedule for all the
four basic inputs in the following format :

Amount of Loan 200000 Yearly

Rate of Interest 10% Half Yearly
Period of Repayment 6 Quaterly
Periodicity Quaterly 4 Bi-Monthly
Instalment Amount Monthly

Loan Repayment Schedule

Interest
Period Opening Balance Due Instalment Closing Balance
1
2
You are also required to compute the following:
1. Compute the amount of interest to be paid for a given installment no. using a
function.
2. Compute the amount of Principal to be paid for a given installment no. using a
function.
3. Compute the Total amount of interest to be paid for installment nos. 5 to 10 using
a function.
4. Compute the Total amount of Principal to be paid for installment nos. 5 to 10
using a function.
 Capital Budgeting

Ques1. The ABC Ltd is in the process of selecting a capital project. The details of 2
Capital projects A and B identified by the company are provided below:
Project A Project B
Rate of Interest 12% 12%
Cost of Project -500000 -550000
Cash Inflows Year 1 400000 20000
Year 2 60000 500000
Year 3 50000 60000
Year 4 70000 70000
Year 5 80000 80000
You are required to
1. evaluate the above capital budgeting projects and recommend the project to be
implemented so that the company earns maximum profit on the basis of:
a. IRR Method
b. NPV Method using 12% Interest Rate
2. Calculate the value of NPV of “Project A” at the rate of interest of 10% to 15%
3. Draw a suitable chart for NPV of “Project A” at the rate of interest of 10% to
15%

Ques2. Given:
Data Description
10% Annual discount rate
-10,000 Initial cost of investment one year from today
3,000 Return from first year
4,200 Return from second year
6,800 Return from third year

Calculate the Net Present Value of this investment.

Ques3. Given:
 Data Description
8% Annual discount rate. This might represent the rate of
inflation or the interest rate of a competing
investment.
-40,000 Initial cost of investment
8,000 Return from first year
9,200 Return from second year
10,000 Return from third year
12,000 Return from fourth year
14,500 Return from fifth year

Calculate the Net Present Value of this investment.