Excel Finance Formulas

0
179
Top-Financial-Functions-in-Excel

We all use excel when it comes to some calculation, analyzing company data such as sales figures, sales taxes or commissions. I will list down the financial formulas used in excel.

XNPV

Returns the net present value for a schedule of cash flows that is not necessarily periodic. To calculate the net present value for a series of cash flows that is periodic, use the NPV function.

Syntax

XNPV(rate, values, dates)
  • Rate = Discount rate for a period
  • Values = Positive or negative cash flows (an array of values)
  • Dates = Specific dates (an array of dates)

XNPV

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],...)
  • Rate = Discount rate for a period
  • Value 1, [Value 2], [Value 3]… = Positive or negative cash flows
  • Here, negative values would be considered as payments and positive values would be treated as inflows.

NPV

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])
  • Values = Positive or negative cash flows (an array of values)
  • [Guess] = An assumption of what you think IRR should be

IRR

XIRR

Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the IRR function.

Syntax

XIRR(values, dates, [guess])
  • Values = Positive or negative cash flows (an array of values)
  • Dates = Specific dates (an array of dates)
  • [Guess] = An assumption of what you think IRR should be

MIRR

Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both the cost of the investment and the interest received on reinvestment of cash.

Syntax

MIRR(values, finance_rate, reinvest_rate)
  • Values = Positive or negative cash flows (an array of values)
  • Finance rate = Interest rate paid for the money used in cash flows
  • Reinvestment rate = Interest rate paid for reinvestment of cash flows

MIRR

NPER

Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.

Syntax

NPER(rate,pmt,pv,[fv],[type])
  • Rate = It is the interest rate/period
  • PMT = Amount paid per period
  • PV = Present Value
  • [FV] = An optional argument which is about the future value of a loan (if nothing is mentioned, FV is considered as “0”)
  • [Type] = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)

NPER

PMT

Calculates the payment for a loan based on constant payments and a constant interest rate.

Syntax

PMT(rate, nper, pv, [fv], [type])
  • Rate = It is the interest rate/period
  • Nper = Number of periods
  • PV = Present Value
  • [FV] = An optional argument which is about the future value of a loan (if nothing is mentioned, FV is considered as “0”)
  • [Type] = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)

PMT

PPMT

Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

Syntax

PPMT(rate, per, nper, pv, [fv], [type])
  • Rate = It is the interest rate/period
  • Per = The period for which the principal is to be calculated
  • Nper = Number of periods
  • PV = Present Value
  • [FV] = An optional argument which is about the future value of a loan (if nothing is mentioned, FV is considered as “0”)
  • [Type] = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)

PPMT

IPMT

Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.

Syntax

IPMT(rate, per, nper, pv, [fv], [type])
  • Rate = The interest rate per period.
  • Per = The period for which you want to find the interest and must be in the range 1 to nper.
  • Nper = The total number of payment periods in an annuity.
  • Pv = The present value, or the lump-sum amount that a series of future payments is worth right now.
  • Fv  Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
  • Type  Optional. The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

IPMT

EFFECT

Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.

Syntax

EFFECT(nominal_rate, npery)
  • Nominal_Rate = Nominal Interest Rate
  • NPERY = Number of compounding per year

EFFECT

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 = It is the interest rate/period
  • Nper = Number of periods
  • [Pmt] = Payment/period
  • PV = Present Value
  • [Type] = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)`

FV

FVSCHEDULE

Returns the future value of an initial principal after applying a series of compound interest rates. Use FVSCHEDULE to calculate the future value of an investment with a variable or adjustable rate.

Syntax

FVSCHEDULE(principal, schedule)
  • Principal = is the present value of a particular investment
  • Schedule = A series of interest rate put together (in case of excel, we will use different boxes and select the range)

FVschedule

PV

Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.

Syntax

PV(rate, nper, pmt, [fv], [type])
  • Rate = It is the interest rate/period
  • Nper = Number of periods
  • [Pmt] = Payment/period
  • FV = Future Value
  • [Type] = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)

PV

DB

Returns the depreciation of an asset for a specified period using the fixed-declining balance method.

Syntax

DB(cost, salvage, life, period, [month])
  • Cost = The initial cost of the asset.
  • Salvage = The value at the end of the depreciation (sometimes called the salvage value of the asset).
  • Life = The number of periods over which the asset is being depreciated (sometimes called the useful life of the asset).
  • Period = The period for which you want to calculate the depreciation. Period must use the same units as life.
  • Month Optional. The number of months in the first year. If month is omitted, it is assumed to be 12.

DB

I have listed which are used on frequent basis. I will keep on adding to the list as I learn more functions.