Transcript SEMESTER V

QUESTIONS IN SPREADSHEET
Dr. Faiyaz Gadiwalla Hinduja College
QUESTIONS IN SPREADSHEET
 FV(): Returns the future value of an investment based on periodic,






constant payments and a constant interest rate. Syntax
FV(rate, nper, pmt, pv, type)
rate is the interest rate per period.
nper is the total number of payment periods in an annuity.
pmt is the payment made each period; it cannot change over the life
of the annuity. Typically, pmt contains principal and interest but no
other fees or taxes. If pmt is omitted, you must include the pv
argument.
pv is the present value, or the lump-sum amount that a series of
future payments is worth right now. If pv is omitted, it is assumed to be
0 (zero), and you must include the pmt argument.
type is the number 0 or 1 and indicates when payments are due. 0 or
omitted indicates payments are made at the end of the period, while 1
indicates that they are made at the beginning of the period.
QUESTIONS IN SPREADSHEET
 Suppose you wants to start saving Rs.100 every month
for a child’s college education, starting from the first
month of child’s birth and want to know what would
be the amount after 18 years at the rate of interest 5%
compounded annually, then the future amount can be
computed using the function.

= FV(5%/12,18*12,-100,0,0)
 This function return 34,920.20 which is the
amount returned to you.
QUESTIONS IN SPREADSHEET
 PV(): The PV function is used to determine how much




a specific future amount is worth today
Syntax PV(rate,nper,pmt,fv,type)
Suppose you want to take car loan of Rs.4,00,000
and a company offers an installment of Rs.9100 per
month for 5 years to pay back this loan, assuming that
interest rate is 12% P.A., the following formula will
compute the present value of the amount.
=PV(12%/12,5*12,-9100)
This function returns 409,090.85 which is the
present value of the amount you are paying.
QUESTIONS IN SPREADSHEET
 PMT(): Calculates the payment for a loan based on




constant payments and a constant interest rate.
Syntax
PMT(rate,nper,pv,fv,type)
Suppose you want buy a refrigerator worth Rs.
44,000/- and you want to know how much you
have to pay as monthly payments when you are
making down payment of Rs. 4000/- and the
dealer is offering 4% financing for a 2 year loan.
=PMT(4%/12,2*12,40000,0,0)
This function returns 1,737.00. This is your installment
amount.
QUESTIONS IN SPREADSHEET
 IPMT (Interest payment) function can be used to find






the interest part of a payment based on periodic,
constant payments and a constant interest rate..
PPMT (Principal payment) function can be used to
find the principal part of a Payment based on periodic,
constant payments and a constant interest rate..
These two functions are useful when you need to
determine the interest/principal breakdown of a
particular payment.
Also PMT = IPMT + PPMT
The syntax of the two functions are as follows:
IPMT(rate, per , nper , pv , fv ,type)
PPMT(rate, per , nper , pv , fv ,type)
QUESTIONS IN SPREADSHEET
 NPV() : Calculates the net present value of an
investment by using a discount rate and a series of
future payments (negative values) and income
(positive values). Syntax
 NPV(rate,value1,value2, ...)
 e.g. =NPV(10%,-10000,3000,4200,6800) gives Rs
1,188.44
QUESTIONS IN SPREADSHEET
IRR():Returns the internal rate of return for a series of cash flows represented by the numbers in values.
These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must
occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate
received for an investment consisting of payments (negative values) and income (positive values) that
occur at regular periods. Syntax
IRR(values,guess)
-70,000
Initial cost of a business
12,000
Net income for the first year
15,000
Net income for the second year
18,000
Net income for the third year
21,000
Net income for the fourth year
26,000
Net income for the fifth year
If the above values are entered from A2 to A7 the =IRR(A2:A7) gives 9%
QUESTIONS IN SPREADSHEET
Consider the following worksheet showing the Basic Salary of 4 persons
A
B
C
D
E
F
1 NAME
BASIC
HRA
DA
TAX GROSS
2 ANITA
50000
3 SACHIN
30000
4 SUNIL
25000
5 NIRMAL
50000
Write steps to find
(i) HRA as 30% of basic salary (ii) DA AS 110% of basic salary (iii) Tax as 20% of Gross
QUESTIONS IN SPREADSHEET
find the Present Days and the Proportionate Salary assuming that there are 26 working days in the
month.
A
B
C
D
E
1 W. Name
Monthly Days Absent Days Present Proportionate Salary
Salary
2 Anil Mane
25000 4
3 Preeti Desai 60000 2
: :
:
:
: :
:
:
45 Jal Mody
70000 3
46 Shabnam Khan 40000 1
QUESTIONS IN SPREADSHEET
The following data has been entered in a worksheet.
A
B
C
D
1 Name of the Borrower
Loan Amount Year
EMI
2 SSS
200000
3
3 YYY
500000
7
4 WWW
120000
2
5 KKK
100000
3
6 JJJ
40000
2
Fill the columns D2 to D6 using the formula EMI=Loan Amount*(i/12)/[1-(1+(i/12))-12n]
Where i=.12 is the rate of interest and n is the number of years.
QUESTIONS IN SPREADSHEET
Explain the use of the following functions
 i. FV ()
 ii. ABS ()
 iii. MAX ()
 iv. PMT ()
 v .FLOOR ()
QUESTIONS IN SPREADSHEET
Q) Explain what are Sub-totals.
Q) What is a Pivot table?
Q) Explain Relative, Absolute and Mixed cell references.
QUESTIONS IN SPREADSHEET
Given the worksheet.
D
E
A
B
C
1
AMOUNT
SIMPLE INT.
COMPOUND INT.
2
200000
3
500000
4
100000
5
900000
6
700000
7
NO. OF YRS
3
8
RATE
9%
Write the steps to obtain the simple interest and compound interest in columns C and D.
(Hint: Simple Interest = (P x R x N) / 100
P-Amount, R-Rate, N-No. of yrs.
Compound Interest = P x ( 1 + R / 100)^ N - P )
QUESTIONS IN SPREADSHEET
Let us say you have taken a car loan of Rs 1, 00,000 from
a bank. You have agreed to pay Rs 2700 at the end of
each month for next 5 years. The bank interest is 12%
P.A. write down the procedure to find out the present
value.
QUESTIONS IN SPREADSHEET
The following data has been entered in a worksheet
A
B
1
CNO NAME
2
4
KELKAR A.
3
101
DATE V.
4
112
KALE A.
5
88
LIMAYE P.
6
49
EKBOTE D
7
54
GORE M.
Write steps to do the following:(i)
(ii)
C
PHONE
25624111
24721742
25872462
25671029
24760023
24598001
D
BALANCE
40000
20500
10000
35000
32100
49800
Arrange data in the alphabetic order of Name.
Arrange the data as per Name and further in the descending order of Balance.
QUESTIONS IN SPREADSHEET
For the following spreadsheet obtain the Subtotals of the fees class wise.
A
B
C
1 ROLL NO. CLASS
FEES PAID
2 23
F.Y.B.COM 2000
3 45
S.Y.B.COM 2500
4 48
T.Y.BCOM 3000
5 55
F.Y.B.COM 2000
6 78
T.Y.BCOM 3000
7 89
S.Y.B.COM 2500
QUESTIONS IN SPREADSHEET
Answer the following using Spreadsheet
1
2
3
4
5
6
A
B
C
D
E
F G
H
I
NAME ENG HINDI ECO BK A/C Tax TOTAL AVERAGE
GUPTA 76
63
78
66 86 68
SURI
54
49
48
52 46 59
KHAN
72
69
66
78 75 81
JUDE
87
73
84
82 91 93
MINAZ 54
49
28
31 17 21
For the above spreadsheet write the steps to obtain the Total marks in 6 subjects and the
Average, as the average of the best five subjects for each student.
QUESTIONS IN SPREADSHEET
Consider the following worksheet showing the Basic Salary of 4 persons
A
B
C
D
E
1 NAME BASIC HRA DA
TAX
2 Dany
6650
3 Sachin 19300
4 Mitul
9100
5 Dimple 55000
Write the steps to compute (i) HRA as 25% of basic salary. (ii) DA as 110% of basic salary or
20000 whichever is less. (iii) Tax as 20% of Basic+DA+HRA
QUESTIONS IN SPREADSHEET
Consider the following worksheet showing cost of machinery and its life in years and rate of
depreciation.
A
B
C
D
E
F
1 COST
LIFE IN YEARS Rate of depr
2 2000000 5
8%
3 Years
1
2
3
4
5
4 Depr.
5 Depr. Value
Using straight line method explain the procedure to display year wise depreciation and depreciated
value for each year till the life of Machinery.
QUESTIONS IN SPREADSHEET
Cost of machinery is entered in cell A4, estimated life in
years is 5 is entered in cell A6 this asset has no salvage
value. Explain how you will create an EXCEL worksheet
showing year, depreciation, and WDV use reducing
balance method.
QUESTIONS IN SPREADSHEET
The following data has been entered in a MS Excel worksheet:
A
B
C
D
1 CITY
CUSTOMER
INCOME
SAVINGS
2 BANGLORE MOHAN
933000
88999
3 CHENNAI
VEEERAMANI 7780000
644440
4 CHENNAI
HARIHARAN
533000
55500
5 DELHI
BALVINDER
77800
66660
6 PUNE
RAJ
770700
61100
7 MUMBAI
GANESH
650000
97789
8 NAGPUR
DEVA
3232000
120000
9 NAGPUR
SANDEEP
434000
76000
10 PUNE
CHANDRA
540000
9899
Write steps to set up a Pivot table at Cell A20 of the same worksheet displaying CITY in rows,
CUSTOMER in columns, INCOME & SAVINGS as data items.