EX: 3
Aim: To compute various useful Financial Functions using Excel
1. INTRATE Function:
The Excel Intrate function calculates the interest rate for a fully invested security .
Syntax:
=INTRATE(settlement, maturity, investment, redemption, [basis] )
Arguments:
1. Settlement (required argument) – This is the settlement date of the security. The
settlement date of a security is the one after the issue date when the security is traded
to the buyer.
2. Maturity (required argument) – This is the maturity date of security or the date when
the security expires.
3. Investment (required argument) – The amount that is invested in the security.
4. Redemption (required argument) – This is the amount we will receive at maturity.
OUTPUT:
2. IPMT Function:
The IPMT function in Excel calculates the interest paid on a given loan where the
interest and periodic payments are constant.
Syntax
=IPMT(rate, per, nper, pv, [fv], [type])
Arguments:
1. Rate (required argument) – This is the interest per period.
2. 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.
3. Nper (required argument) – The total number of payment periods.
4. Pv (required argument) – This is the present value, or the lump sum amount, that a
series of future payments is worth as of now.
5. 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.
OUTPUT
3. IRR function
Returns the internal rate of return for a series of cash flows
Syntax
=IRR(values,[guess])
Arguments:
1. Values (required argument) – This is an array of values that represent the series of
cash flows. Cash flows include investment and net income values. Values can be a
reference to a range of cells containing values.
2. [Guess] (optional argument) – This is a number guessed by the user that is close to the
expected internal rate of return (as there can be two solutions for the internal rate of
return). If omitted, the function will take a default value of 0.1 (=10%).
OUTPUT:
4. NPER Function
NPER function - Returns the number of periods for an investment
Syntax
=NPER(rate,pmt,pv,[fv],[type])
Arguments:
1. Rate (required argument) – This is the interest rate per period.
2. Pmt (required argument) – The payment made each period. Generally, it contains
principal and interest but no other fees and taxes.
3. Pv (required argument) – The present value, or the lump-sum amount that a series of
future payments is worth right now.
4. Fv (optional argument) – This is the future vale or the cash balance which we want at
the end after the last payment is made. When omitted, it takes the value as zero.
5. Type (optional argument) – Indicates when payments are due. If type is set to 0 or
omitted, then payments are due at the end of the period. If set to 0, payments are due
at the start of the period.
OUTPUT
5. NPV Function
Returns the net present value of an investment based on a series of periodic cash flows and a
discount rate
Syntax
=NPV(rate,value1,[value2],…)
Arguments:
1. Rate (required argument) – This is the rate of discount over the length of the
period.
2. Value1, Value2 – Value1 is a required option. They are numeric values that
represent a series of payments and income where:
a. Negative payments represent outgoing payments.
b. Positive payments represent incoming payments.
