5 6293813335750083255 PDF

Preview

Summary

ICAI...

Description

THE INSTITUTE OF CHARTERED ACCOUNTANTS OF INDIA “ICAI BHAWAN” KOLKATA-700071

A PROJECT REPORT SUBMITTED BY

Jyoti Kumari (REG NO: CRO0584465) Under the guidance of Mr. Kaushik Chatterjee

Submitted in partial fulfillment of the requirements for INFORMATION TECHNOLOGY TRAINING DEPARTMENT OF ITT ICAI KOLKATA

1|Page [INDEX]

ACKNOWLEDGEMENT First of all, I would like to thank THE INSTITUTE OF CHARTERED ACCOUNTANTS OF INDIA for organizing this course. I am really fortunate that, I had the kind of association as well as supervision of as supervision of MR. KOUSHIK CHATTERJEE. I would also acknowledge my gratitude to faculty advisors their lectures and exemplary guidance, constant encouragement and careful monitoring throughout the course. Also, I gratitude to my friends for being with me during whole course.

At the completion of my ITT project work in the partial fulfillment of the requirements to complete the course would like to thanks THE INSTITUTE OF CHARTERED ACCOUNTANTS OF INDIA for conducting this course and making it extremely convenient for the students to obtain knowledge for the very renowned institute. I would like to thank the Eastern India Regional Council of ICAI having this platform created for the students helping them gain new heights in the field of professionalism.

Thanking You Jyoti Kumari (REG NO: CRO0584465)

2|Page [INDEX]

STUDENT’S DECLARATION I, Jyoti Kumari hereby declare that presented report has based on my own work and observation carried out during the course of our study under the supervision of Mr. Kaushik Chatterjee. . Jyoti Kumari (REG NO: CRO0584465)

3|Page [INDEX]

Contents INTRODUCTION................................................................................................................................................................... 5 I.

FINANCIAL FUNCTIONS............................................................................................................................................. 6 1.

PMT FUNCTION...................................................................................................................................................................6

2.

IPMT FUNCTION..................................................................................................................................................................8

3.

PV FUNCTION......................................................................................................................................................................9

II.

LOGICAL FUNCTIONS.............................................................................................................................................. 10 1.

IF FUNCTION......................................................................................................................................................................11

2.

AND FUNCTION.................................................................................................................................................................12

3.

OR FUNCTION....................................................................................................................................................................13

III.

TEXT FUNCTIONS................................................................................................................................................. 14

1.

MID FUNCTION..................................................................................................................................................................14

2.

PROPER FUNCTION.........................................................................................................................................................15

3.

RIGHT FUNCTION.............................................................................................................................................................16

IV. 3. V.

DATE & TIME FUNCTIONS................................................................................................................................... 17 DATEIF.................................................................................................................................................................................19 LOOKUP AND REFERENCE FUNCTIONS.............................................................................................................. 20

1.

LOOKUP FUNCTION.........................................................................................................................................................20

2.

VLOOKUP FUNCTION......................................................................................................................................................22

3.

HLOOKUP FUNCTION......................................................................................................................................................23

VI.

MATH AND TRIG FUNCTIONS............................................................................................................................. 24

1.

ROUND FUNCTION...........................................................................................................................................................24

2.

ODD FUNCTION.................................................................................................................................................................25

3.

SUM FUNCTION.................................................................................................................................................................26

VII.

STATISTICAL FUNCTIONS................................................................................................................................... 27

1.

AVERAGE FUNCTION......................................................................................................................................................27

2.

MAX FUNCTION.................................................................................................................................................................28

3.

MIN FUNCTION..................................................................................................................................................................29

CONCLUSION.................................................................................................................................................................... 30

4|Page [INDEX]

INTRODUCTION Microsoft Excel is a spreadsheet program used to record and analyze numerical and statistical data. Microsoft Excel provides multiple features to perform various operations like calculations, pivot tables, graph tools, macro programming, etc. Excel 2016 has 484 functions. Of these, 360 existed prior to Excel 2010. Microsoft classifies these functions in 14 categories. Of the 484 current functions, 386 may be called from VBA as methods of the object "Worksheet Function" and 44 have the same names as VBA functions. An Excel spreadsheet can be understood as a collection of columns and rows that form a table. Alphabetical letters are usually assigned to columns, and numbers are usually assigned to rows. The point where a column and a row meet is called a cell. The letter representing the column and the number representing a row gives the address of a cell.

Cell address box (A1)

Formula bar

Row numbers Column letters

Collection of rows and columns forming a spreadsheet (looks like a table)

The ribbon provides shortcuts to commands in Excel. A command is an action that the user performs. An example of a command is creating a new document, printing a documenting, etc. The image below shows the ribbon used in Excel Ribbon tabs – used to group similar command together 2016.

Ribbon bar – provides further

5|Page [INDEX]

I. FINANCIAL FUNCTIONS The excel financial functions have been made available to execute a variety of financial calculations, including calculations of yield, investment valuations, interest rates, internal rate of return, asset depreciation, and payments. These financial functions can be, however, classified into different categories so as to enable you to stumble on the required function. There are generally four interrelated quantities that include: 

Present Value (PV) equivalent to the value of the load or savings today. This function is used to estimate the loan size that could be paid off provided a periodic payment over a provided total number of payments and a provided periodic interest rate.



Interest Rate per period (RATE) equivalent to the interest rate, generally monthly. This function is used to calculate the periodic interest rate required to pay off a provided present value with a provided periodic payment and a total number of payments.



Number of payment periods (NPER) equivalent to number of payment periods. This function is used to evaluate the total number of payment periods required to pay off a given present value with a provided payment and periodic interest rate.



Periodic payment (PMT) equivalent to the payment per period. This function is used for calculating the fixed periodic payment with a given present value, periodic interest rate, and the total number of payments.

Excel Financial Functions List PMT IPMT PV FV RATE MPER ISPMT PPMT

1. 2. 3. 4. 5. 6. 7. 8.

1. PMT FUNCTION The PMT function is categorized under financial Excel functions. The function helps calculate the total payment (principal and interest) required to settle a loan or an investment with a fixed interest rate over a specific time period.

Formula: =PMT(rate, nper, pv, [fv], [type]) The PMT function uses the following arguments: 

Rate (required argument) – The interest rate of the loan.



Nper (required argument) – Total number of payments for the loan taken.



Pv (required argument) – The present value or total amount that a series of future payments is worth now. It is also termed as the principal of a loan.

6|Page [INDEX]



Fv (optional argument) – This is the future value or a cash balance we want to attain after the last payment is made. If Fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.



Type (optional argument) – The type of day count basis to use. The possible values of the basis are:

Basis

Example:

7|Page [INDEX]

Day count basis

0 or omitted

At the end of the period

1

At the beginning of the period

2. IPMT FUNCTION The IPMT function is categorized under Excel Financial functions. The function calculates the interest portion based on a given loan payment and payment period. We can calculate, using IPMT, the interest amount of a payment for the first period, last period, or any period in between. As a financial analyst, we will often be interested in knowing the principal component and interest component of a loan payment for a specific period. IPMT helps to calculate the amount.

Formula: =IPMT(rate, per, nper, pv, [fv], [type]) The IPMT function uses the following arguments: 

Rate (required argument) – This is the interest per period.



Per (required argument) – This is the period for which we want to find the interest and must be in the range from 1 to nper.



Nper (required argument) – The total number of payment periods.



Pv (required argument) – This is the present value, or the lump sum amount, that a series of future payments is worth as of now.



Fv (optional argument) – The future value or a cash balance that we wish to attain after the last payment is made. If we omit the Fv argument, the function assumes it to be zero. The future value of a loan would be taken as zero.



Type (optional argument) – Accepts the numbers 0 or 1 and indicates when payments are due. If omitted, it is assumed to be 0. Set type to 0 if payments are at the end of the period, and to 1 if payments are due at the start.

Example:

8|Page [INDEX]

3. PV FUNCTION The PV function is a widely used financial function in Microsoft Excel. It calculates the present value of a loan or an investment. In financial statement analysis, PV is used to calculate the dollar value of future payments in the present time. For multiple payments, we assume periodic, fixed payments and a fixed interest rate. Alternatively, the function can also be used to calculate the present value of a single future value.

Formula: =PV(rate, nper, pmt, [fv], [type]) The PV function uses the following arguments: 

rate (required argument) – The interest rate per compounding period. A loan with a 12% annual interest rate and monthly required payments would have a monthly interest rate of 12%/12 or 1%. Therefore, the rate would be 1%.



nper (required argument) – The number of payment periods. For example, a 3 year loan with monthly payments would have 36 periods. Therefore, nper would be 36 months.



pmt (required argument) – The fixed payment per period.



fv (optional argument) – An investment’s future value at the end of all payment periods (nper). If there is no input for fv, Excel will assume the input is 0.



type (optional argument) – Type indicates when payments are issued. There are only two inputs, 0 and 1. If type is omitted or 0 is the input, payments are made at period end. If set to 1, payments are made at period beginning.

Example:

9|Page [INDEX]

II.LOGICAL FUNCTIONS Logical functions provide decision-making tools for information in a spreadsheet. They allow you to look at the contents of a cell, or to perform a calculation, and then test that result against a required figure or value. You can then use the IF logical function to determine which calculation to perform or action to take depending on the outcome of the test. Excel logical functions - facts and figures 

In arguments of the logical functions, you can use cell references, numeric and text values, Boolean values, comparison operators, and other Excel functions. However, all arguments must evaluate to the Boolean values of TRUE or FALSE, or references or arrays containing logical values.



If an argument of a logical function contains any empty cells, such values are ignored. If all of the arguments are empty cells, the formula returns #VALUE! error.



If an argument of a logical function contains numbers, then zero evaluates to FALSE, and all other numbers including negative numbers evaluate to TRUE. For example, if cells A1:A5 contain numbers, the formula =AND(A1:A5) will return TRUE if none of the cells contains 0, FALSE otherwise.



A logical function returns the #VALUE! error if none of the arguments evaluate to logical values.



A logical function returns the #NAME? error if you've misspell the function's name or attempted to use the function in an earlier Excel version that does not support it. For example, the XOR function can be used in Excel 2016 and 2013 only.



In Excel 2016, 2013, 2010 and 2007, you can include up to 255 arguments in a logical function, provided that the total length of the formula does not exceed 8,192 characters. In Excel 2003 and lower, you can supply up to 30 arguments and the total length of your formula shall not exceed 1,024 characters.

Excel Logical Functions List 1. 2. 3. 4. 5. 6. 7.

10 | P a g e [INDEX]

IF AND OR NOT TRUE FALSE IFERROR

1. IF FUNCTION The IF function is categorized under Excel Logical functions. It is used to tests a given condition and returns one value for a TRUE result and another value for a FALSE result. We can also use the IF function to evaluate a single function, or we can include several IF functions in one formula. Multiple IF statements in Excel are known as nested IF statements. As a financial analyst , the IF function is used often to evaluate and analyze data by evaluating specific conditions. The function can be used to evaluate text, values, and even errors. It is not limited to only checking if one thing is equal to another and returning a single result. We can also use mathematical operators and perform additional calculations, depending on our criteria. We can also nest multiple IF functions together to perform multiple comparisons.

Formula: =IF(logical_test, value_if_true, value_if_false) The function uses the following arguments: 

Logical_test (required argument) – This is the condition to be tested and evaluated as either TRUE or FALSE.



Value_if_true (optional argument) – The value that will be returned if the logical_test evaluates to TRUE.



Value_if_false (optional argument) – The value that will be returned if the logical_test evaluates to FALSE.

When using the IF function to construct a test, we can use the following logical operators: 

= (equal to)



> (greater than)



>= (greater than or equal to)



< (less than)

...