Applied Finance

Assignment2
Financial Modelling
Supplementary Questions
Excel codes

Name:- Varun.S.Patil
Std:- B.Tech Comp A
MIS:- 111603048
Subject:- Applied Finance
College:- College of Engineering, Pune
A Excel Functions:-
1) PV:-
A 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.
Formulas:-
=PV(rate, nper, pmt, [fv], [type])
The PV function uses the following arguments:
i) 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%.
ii) 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.
iii) pmt (required argument) – The fixed payment per period.
iv) 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.
v) 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.

2) NPV:-
The NPV Function is an Excel Financial function that will calculate the Net Present Value (NPV)
for a series of cash flows and a given discount rate. It is important to understand the Time Value
of Money, which is a foundational building block of various Financial Valuation methods.
In financial modeling, the NPV function is useful in determining the value of an investment or
understanding the feasibility of a project. It should be noted that it’s usually more appropriate
for analysts to use the XNPV function instead of the regular NPV function.
Formulas:-
=NPV(rate,value1,[value2],…)
The NPV function uses the following arguments:
i) Rate (required argument) – This is the rate of discount over the length of the period.
ii) Value1, Value2 – Value1 is a required option. They are numeric values that represent a series
of payments and income where:
Negative payments represent outgoing payments.
Positive payments represent incoming payments.
The NPV function uses the following equation to calculate the Net Present Value of an
Investment:
NPV Function

3) XNPV:-
The XNPV formula in Excel requires the user to select a discount rate, a series of cash flows, and
a series of corresponding dates for each cash flow.
Formulas:-
The Excel formula for XNPV is:
=XNPV(Rate, Cash Flows, Dates of Cash Flow)

4) IRR:-
The IRR function is categorized under Excel Financial functions. IRR will return the Internal Rate
of Return for a given cash flow, that is, the initial investment value and a series of net income
values.
IRR Formulas:-
=IRR(values,[guess])
It uses the following formula to calculate:
ΣN
n=0
Cn
(1+r)n = 0
5) XIRR:-
It get the Internal Rate of Return (IRR) for a series of cash flows that may not be periodic. It is
similar to the IRR function except that the series of cashflows for the XIRR calculation do not
necessarily have to be periodic.
Formulas:-
=XIRR(values, dates,[guess])

6) FV:-
The FV Function Excel formula is categorized under Financial functions. This function helps
calculate the future value of an investment.
As a financial analyst, the FV function helps calculate the future value of investments made by a
business, assuming periodic, constant payments with a constant interest rate. It is useful in
evaluating low-risk investments such as certificates of deposit or fixed rate annuities with low
interest rates. It can also be used in relation to interest paid on loans.
Formulas:-
=FV(rate,nper,pmt,[pv],[type])

7) Slicer:-
Using a slicer, you can filter your data (or pivot table, pivot chart) by clicking on the type of data
you want.

8) VLOOKUP:-
A VLOOKUP function in Excel is a tool for looking up a piece of information in a table or data
set and extracting some corresponding data/information.
VLOOKUP Formulas:-
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The VLOOKUP function uses the following arguments:-
i) Lookup_value (required argument) – Lookup_value specifies the value that we want to look up
in the first column of a table.
ii) Table_array (required argument) – The table array is the data array that is to be searched. The
VLOOKUP function searches in the left-most column of this array.
iii) Col_index_num (required argument) – This is an integer, specifying the column number of the
supplied table_array, that you want to return a value from.
iv) Range_lookup (optional argument) – This defines what this function should return in the event
that it does not find an exact match to the lookup_value. The argument can be set to TRUE or
FALSE, which means:
TRUE – Approximate match, that is, if an exact match is not found, use the closest match below
the lookup_value.
FALSE – Exact match, that is, if an exact match not found, then it will return an error.

9) Data Analysis:-
Data Analysis command is not available in most versions of Excel, we need to load the
Analysis ToolPak add-in program. After the add in has been successfully installed, data analysis
can be selected from far right of the data tab. It offers access to a variety of analysis tools. We
have used it to create Histograms.
To draw a histogram, we need to first create a bin column manually. Then we select Data
Analysis from Data tab and Histogram from the pop up box. Finally, we need to add the input
data as the Input Range and Bin column as the Bin Range. After selecting a cell as the Output
Range and clicking OK we get the Histogram.

10) Goal Seek:-

It is part of the What-if Analysis option under the Data tab of Excel. This feature lets you
adjust a value used in a formula to achieve a specific goal. We use Goal Seek to find IRR for a
project by setting the 3 inputs as:-
Set cell:- Cell where NPV value is calculated using formula
To value:- 0 (as for IRR the NPV is zero)
By changing: Rate Cell.

11) Data Table:-

It is also part of the What-if Analysis option. We use a Data table to calculate NPV for various
rates after we have the NPV for a certain rate.

12) AVERAGE:-
The AVERAGE function is categorized under Excel Statistical functions. It will return the average
value of a given series of numbers in Excel. It is used to calculate the arithmetic mean of a given
set of arguments in Excel.
Formulas:-
=AVERAGE(number1, [number2], …)
The function uses the following arguments:-
Number1 (required argument) – This is the first number of a cell reference or a range for which
we want the average.
Number2 (optional argument) – They are the additional numbers, cell references or a range for
which we want the average. A maximum of 255 numbers is allowed.

13) BINOMIAL:-
It Get the binomial distribution probability for the number of successes from the trials
Formulas for Binomial Distribution:-
=BINOM.DIST(number_s,trials,probability_s,cumulative)
The BINOM.DIST uses the following arguments:
i) Number_s (required argument) – This is the number of successes in trials.
Trials (required argument) – This is the number of independent trials. It must be greater than or
equal to 0.
ii) Probability_s (required argument) – This is the probability of success in each trial.
iii) Cumulative (required argument) – This is a logical value that determines the form of the
function. It can either be:
TRUE – Uses the cumulative distribution function.
FALSE – Uses the probability mass function.

14) COVARIANCE.P:-
The covariance Excel function is categorized under Statistical functions. It calculates the joint
variability of two random variables, given two sets of data.
Covariance Formulas in Excel:-
=COVARIANCE.P(array1, array2)
The COVARIANCE.P function uses the following arguments:-
i) Array1 (required argument) – This is a range or array of integer values.
Ii) Array2 (required argument) – This is a second range or array of integer values.

15) COVARIANCE.S:-
The COVARIANCE.S function is categorized under Excel Statistical functions. It will calculate the
sample covariance for two sets of values provided by a user.
Covariance Formulas:-
=COVARIANCE.S(array1, array2)
The function uses the following arguments:-
i) Array1 (required argument) – This is the first array of numeric values.
ii) Array2 (required argument) – This is the second array of numeric values.
The arguments can be either numbers or names, arrays, or references that contain numbers.

16) NORM.DIST:-
The NORM.DIST function is categorized under Excel Statistical functions. It will calculate the
probability that variable x falls below or at a specified value. That is, it will calculate the normal
probability density function or the cumulative normal distribution function for a given set of
parameters.
Formulas:-
=NORM.DIST(x,mean,standard_dev,cumulative)
The NORM.DIST uses the following arguments:
i) X (required argument) – This is the value for which we wish to calculate the distribution.
ii) Mean (required argument) – The arithmetic mean of the distribution.
iii) Standard_dev (required argument) – The standard deviation of the distribution.
iv) Cumulative (required argument) – This is a logical value. It specifies the type of distribution to
be used: TRUE (Cumulative Normal Distribution Function) or FALSE (Normal Probability Density
Function).
We can use 1 for TRUE and 0 for FALSE when entering the formula.
The formula used in calculating the normal distribution is:

Where:-

 μ is the mean of the distribution

 σ2 is the variance
  x is the independent variable for which you want to evaluate the function.

17) NORM.INV:-
The NORM.INV Function is categorized under Excel Statistical functions. It will calculate the
inverse of the normal cumulative distribution for a supplied value of x, with a given distribution
mean and standard deviation. The function will calculate the probability to the left of any
particular point in a normal distribution.
Formulas:-
=NORM.INV(probability,mean,standard_dev)
The NORM.INV formula uses the following arguments:-
i) Probability (required argument) – This is the probability corresponding to normal distribution.
It is the value at which we want to evaluate the inverse function.
ii) Mean (required argument) – This is the arithmetic mean of the distribution.
iii) Standard_dev (required argument) – This is the standard deviation of the distribution.
The normal distribution is a continuous probability function and is given by the formulas:-

Where:-

 μ is the mean of the distribution

 σ2 is the variance
 x is the independent variable for which you want to evaluate the function

18) VAR.P:-
The VAR.P function is categorized under Excel Statistical functions. This function will return the
variance of a given set of values.
Variance is a statistical measure used across a set of values to identify the amount that the values
vary from the average value.
Formulas:-
=VAR.P(number1,[number2],…)
The VAR.P function uses the following arguments:-
i) Number1 (required argument) – This is the first argument corresponding to a population.
ii) Number 2,.. (optional argument) – Here, the number arguments can be up to 254 values or
arrays of values that provide at least two values to the function.

19) VAR.S:-
The VAR.S function is categorized under Excel Statistical functions. This function will return the
variance of a sample.
Formulas:-
=VAR.S(number1,[number2],…)
The VAR.S function uses the following arguments:-
i) Number1 (required argument) – This is the first argument corresponding to a sample of a
population.
ii) Number 2,… (optional argument) – Here, the number arguments can be up to 254 values or
arrays of values that provide at least two values to the function.

20) STDEV.P:-
The STDEV.P Function is categorized under Excel Statistical functions. STDEV.P will calculate the
standard deviation that is based on an entire population given as arguments. It will ignore logical
values and text.
Formulas:-
=STDEV.P(number1,[number2],…)
The STDEV.P function uses the following arguments:-
i) Number1 (required argument) – This is the first number argument that corresponds to a
population.
Ii) Number2 (required argument) – This is another number argument that corresponds to the
population.

21) STDEV.S:-
The STDEV.S Function is an Excel Statistical function that will calculate the standard deviation
that is based on a sample of the population. It will ignore logical values and text.

While doing financial analysis, the STDEV.S function can be useful in, for example, measuring the
income standard deviation representative of an entire population.
Formulas:-
=STDEV.S(number1,[number2],…)
The STDEV.S function uses the following arguments:-
i) Number1 (required argument) – This is the first number argument that corresponds to a sample
of the population.
ii) Number2 (optional argument) – This is a number argument that corresponds to a second
sample of the population.

22) MEDIAN:-
The MEDIAN Function is categorized under Excel Statistical functions. The function will calculate
the middle value of a given set of numbers. Median can be defined as the middle number of a
group of numbers. That is, half the numbers return values that are greater than the median, and
half the numbers return values that are less than the median. For example, the median of 2, 3, 3,
5, 7, and 10 is 4.
In financial analysis, the function can be useful in calculating the median of certain numbers, e.g.,
median sales or median expenses.
Formulas:-
=MEDIAN(number1, [number2], …)
The function uses the following arguments:-
i) Number1 (required argument) – The number arguments are a set of one or more numeric
values (or arrays of numeric values), for which we wish to calculate the median.
ii) Number2 (optional argument).

23) MODE:-
A MODE Function is categorized under Excel Statistical functions. MODE will calculate the most
frequently occurring value from a list of supplied numbers. If there are two or more most
frequently occurring values in the supplied data, the function returns the lowest of the values.
Formulas:-
=MODE(number1, [number2], …)
The function uses the following arguments:-
i) Number1 (required argument) – The number arguments are a set of one or more numeric
values (or arrays of numeric values) for which we want to calculate the mode.
ii) Number2 (optional argument).