Question

Write a report on Excel functions and formulas most commonly used in finance for solving finance problems (at least 5 functions).

In the report, please introduce at least one example for each function used.

Also, please specify the contribution of each member in group.

Answer 1

Ans) Excel spreadsheet are used by most of the investment bankers and financial analyst for preparing financial reports and mking financial models.The formulas used in financial fields are as follows::

1 . Future value ( FV) which is used to calculate future value of an investment with a given uniform interest rate and time period.The formul is FV(Rate,Nper, [Pmt],PV,[Type])

Here FV = future value of investment

Rate = interest rate

Nper = no .of periods

Pmt = payment/ period

PV = Present value

[ Type] = when payment is made at the begining or end period.

For example if Mr B has to invest $ 200 in 2015.There should be yearly payment.The interest rate is 8 % .what would be its fv in 2020.

The formula for FV = (8%,5,1, -200)

Formula 2.Net present value formula

NPV = (Rate, Value 1, [Value 2], [Value 3]......)

Rate = discounted rate for the period

Value 1, [Value 2], [Value 3]... = positive or negative cash flow .

Example Rate of discount 7 %

Cash flows year 1 = 20000

Year 2 = 20000

Year 3 = 30000

Initial inveatment = - 50000

NPV = ( 7 % , B5:B7) + B3

Here B5: B7 is the cell reference for cashinflow and B3 is the cell reference for initial investment

Formula 3:Internal rate of return is the hurdle rate or the return required from a project inorder to undertake it.

IRR = ( Values, [Guess])

Value = positive or negative cash flow

[ Guess] = An assumed value for IRR.

Let initial investment be $ 500

Return get in 1 year = 100

Retirn get in 2 year = 150

Return get in 3 year = 200

IRR = ( C3: C6,0.1)

Here C3: C6 is the column which contains initial investment and returns and 0.1 is the guess rate .

Formula 4 : Formula for calculating nominal rate of interest with effective interest rate per annum and no of times compounding per year is given

Nominal interest rate =(Effect_Rate ,NPERY)

Effect _Rate = Effective annaula rate of interest

NPERY = No.of years of compounding

For example effective annual rate of interest = 12% and frequeny of compounding is 12 per year

Nominal rate of interest =(12%,12)

Formula 5 :: SLN is a function used to calculate depreciation on straight line method .

SLN = ( Cost, Salvage, life)

Cost = intial cost of perchase

Salvage = value of asset after the life

Life = life time for use of an asset.

For example initial cost of machine is $ 1000, with life of use of machine 10 years and salvage value $200 .

SLN = ( 1000,200,10)