## 1 – FV Function (Future Value)

The **FV function** is an Excel financial function that is used to calculate the future value of an investment based on a series of periodic payments and a constant interest rate.

The syntax of the **FV** function is:

`FV(rate,nper,pmt,[pv],[type])`

**Rate:**It is the rate of interest per period**nper:**The total number of payment periods in an annuity.**pmt:**The amount of payment in each period. This amount is fixed. It contains the principal amount and the interest. If pmt is omitted,**pv**must be included.**[pv]:**(Optional) It is the present value. It also indicates the lump sum amount of a future payment.**[type]:**(Optional) This number is 0 or 1 and indicates when payments are due. 0 at the end of the period and 1 at the beginning.

Let’s see an example of how you can apply the **FV **function to get the future value in Excel. We have the following parameters:

- Number of years: 10
- Period value: 12 (as months)
- Interest rate: 9%
- Present value: $1000

**Note:**This value will be inputted as positive. Important for the calculation (see later). - Payment and Type values are left blank (or as 0).

To find the future value, we apply the FV formula as follows:

`=-FV(C6/C5,C4*C5,C8,C7,C9)`

**C6/C5**calculates the Interest per Period (Annual IR/Periods PY).**C4*C5**calculates the number of months (Year*Month=Periods).- Payments (
**C8**) and Type (**C9**). **C7**is the Present Value, valued at $1000.- The
**negative (-)**before the formula plays a crucial role in the calculation.- The PV and FV are opposites (PV=Inflow Cash, FV=Outflow Cash)
- If PV is positive, FV will be negative by calculation.
- Thus, a
**negative operator (-)**is used to get the correct value.

Let’s say you invest $50 per month. Your account will be valued at **$12,127.07 **

## 2 – PV Function (Present Value)

The **PV function** is an Excel financial function that calculates the present value of an expected cash flow, which can either be a loan or an investment based on a constant interest rate.

The syntax of the **PV** function is:

`PV(rate, nper, pmt, [fv], [type])`

**Rate:**It is the rate of interest per period**nper:**The total number of payment periods in an annuity.**pmt:**The amount of payment in each period. This amount is fixed. It contains the principal amount and the interest. If pmt is omitted,**fv**must be included.**[fv]:**(Optional) It is the future value. Also indicated as the lump sum amount of the present payment.**[type]:**(Optional) This number is 0 or 1 and indicates when payments are due. 0 at the end of the period and 1 at the beginning.

Let’s see an example of how you can apply the **PV** function to get the present value of a loan in Excel. We’ll use the following:

- Number of years: 5
- Period value: 12 (as months)
- Interest rate: 8%
- Future value: $20000
- Payment and Type values are left blank (or as 0).

Insert the following formula to apply the **PV **formula to calculate the present value:

`=-PV(C6/C5,C4*C5,C8,C7,C9)`

**C6/C5**calculates the Interest per Period (Annual IR/Periods PY).**C4*C5**calculates the number of months (Year*Month=Periods).- Payments (
**C8**) and Type (**C9**). **C7**is the Future Value, valued at $20000.- The
**negative (-)**before the formula plays a crucial role in the calculation.- The PV and FV are opposites (PV=Inflow Cash, FV=Outflow Cash)
- If FV is positive, PV will be negative by calculation.
- Thus a
**negative operator (-)**is used to get the correct value.

Let’s say you received a cash receipt of $100 per month. Your account will be valued at **$18,356.05**

## 3 – PMT Function (Payment)

The **PMT** function calculates the loan payment for a constant interest rate and constant payment.

The syntax of the **PMT** function is:

`PMT(rate, nper, pv, [fv], [type])`

**Rate:**It is the rate of interest per period**nper:**The total number of payment periods.**pv:**It is the present value. Also indicates the lump sum amount of a future payment.**[fv]:**(Optional) It is the future value. Also indicated as the lump sum amount of the present payment.**[type]:**(Optional) This number is 0 or 1 and indicates when payments are due. 0 at the end of the period and 1 at the beginning.

Let’s apply the **PMT **function to get the constant payment for a loan at a constant interest rate in Excel. We’ll use the following:

- Number of years: 5
- Period value: 12 (as months)
- Interest rate: 8%
- Present value: $20000
- Future value and Type values are left blank (or as 0).

To apply the **PMT** formula to calculate the payment per period, insert the following formula:

`=PMT(C6/C5,C4*C5,C8,C7,C9)`

**C6/C5**calculates the Interest per Period (Annual IR/Periods PY).**C4*C5**calculates the number of months (Year*Month=Periods).**C8**is the Future value, and the Payment Type is**C9**.**C7**is the Present Value, valued at $20000.

## 4 – Rate Function (Interest Rate)

The **RATE** function calculates the interest rate per period for an investment or loan.

The syntax of the **RATE** function is:

`RATE(nper, pmt, pv, [fv], [type])`

**nper:**The total number of payment periods.**pmt:**The amount of payment in each period. This amount is fixed. It contains the principal amount and the interest. If pmt is omitted,**fv**must be included.**pv:**It is the present value. It also indicates the lump sum amount of a future payment.**[fv]:**(Optional) It is the future value. Also indicated as the lump sum amount of the present payment.**[type]:**(Optional) This number is 0 or 1 and indicates when payments are due. 0 at the end of the period and 1 at the beginning of the period.

Let’s apply the **RATE** function to get the interest rate for a loan for a fixed period of time in Excel. We’ll use the following:

- Number of years: 5
- Period value: 12 (as months)
- Present value: -$3000
- Future value: $30000
- Payment and Type values are left blank (or as 0).

To apply the **RATE **formula to calculate the interest rate, insert the following formula:

`=RATE(C5*C4,C7,C6,C8,C9)`

**C5*C4**calculates the**NPER**or number of months (Year*Month=Periods).**C4*C5**calculates the number of months (Year*Month=Periods).- Payments (
**C7**) and Type (**C9**). **C6**is the Present Value, valued at $3000 and entered as negative as,- The PV and FV are opposites (PV=Inflow Cash, FV=Outflow Cash)
- If PV is positive, FV will be negative by calculation.
- Thus a
**negative operator (-)**is used to get the correct value.

**C8**is the future value of the loan entered as positive.

## 5 – NPER Function (Number of Periods)

The financial function **NPER** calculates period numbers for periodic, constant payments for constant interest rates.

The syntax of the **RATE** function is:

`NPER(rate, pmt, pv, [fv], [type])`

**Rate:**It is the rate of interest per period**pmt:**The amount of payment in each period. This amount is fixed. It contains the principal amount and the interest. If pmt is omitted,**fv**must be included.**pv:**It is the present value. Also indicated as the lump sum amount of a future payment.**[fv]:**(Optional) It is the future value. Also indicated as the lump sum amount of the present payment.**[type]:**(Optional) This number is 0 or 1 and indicates when payments are due. 0 at the end of the period and 1 at the beginning of the period.

Let’s apply the **NPER** function to get the number of periods to pay a loan for a fixed interest rate in Excel. We’ll use the following:

- Number of years: 12
- Annual Interest Rate: 10%
- Present value: $10000
- Payment: -$500
- Future value and Type values are left blank (or as 0).

Insert the following formula to apply the **NPER **function to calculate the interest rate:

`=NPER(C5/C4,C7,C6,C8,C9)`

## 6 – NPV Function (Net Present Value)

The** NPV** function calculates the net present value of an investment.

The syntax of the **NPV** function is:

`NPV(rate,value1,[value2],...)`

**Rate**: discount rate**Value 1, [value2],[value3]…:**payments and income. It should be equally spaced in time and occur at the end of each period.

Let’s use the **NPV** function to calculate net present value. We’ll use the following:

- Annual Discount Rate: 5%
- Initial Investment Cost: $30000
- 1st Year Return: $10000
- 2nd Year Return: $5000
- 3rd Year Return: $12000
- 4th Year Return: $7000

Insert the following formula to apply the **NPV **function to calculate the net present value:

`=NPV(C4,C6:C9)+C5`

## 7 – IRR Function (Internal Rate of Return)

The **IRR** function calculates the internal rate of return for a series of cash.

The syntax of the **IRR** function is:

`IRR(values, [guess])`

**Values:**An array or a reference to cells that contain numbers for which the internal rate of return is calculated**[guess]:**Guessed number close to the result of IRR.

Here we have some data relevant to **IRR** function:

- Initial Investment Cost: $30000
- 1st Year Return: $10000
- 2nd Year Return: $5000
- 3rd Year Return: $12000
- 4th Year Return: $7000

Insert the following formula to apply the **IRR **formula to calculate the internal rate of return:

`=IRR(C4:C8)`

## 8 – XNPV Function (NPV for Non-Scheduled Cash Flow)

The** XNPV **function calculates the net present value of net present value of a cash flow series that are not necessarily periodic.

The syntax of the **XNPV** function is:

`XNPV(rate, values, dates)`

**Rate:**An array or a reference to cells that contain numbers for which the internal rate of return is calculated**Values:**A series of cash flows corresponding to a payment schedule (in dates). The first payment is optional and corresponds to a payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All payments are discounted based on a 365-day year. The values must contain minimum one positive value and one negative value.**Dates:**A schedule of payment dates that corresponds to the cash flow payments. The first payment date must be in top of the list. But the other dates can occur in any order.

Here we have some data relevant to **XNPV** function:

- Date values arranged in a column.
- Annual Discount Rate: 5%
- Initial Investment Cost: $30000
- 1st Year Return: $10000
- 2nd Year Return: $5000
- 3rd Year Return: $12000
- 4th Year Return: $7000

Insert the following formula to apply the **XNPV **function to calculate the net present value for non-scheduled cash flow:

`=XNPV(D,D6:D10,B6:B10)`

## 9 – XIRR Function (IRR for Non-Scheduled Cash Flow)

The **XIRR** function calculates the internal rate of return for a series of cash flows that occur at irregular intervals.

The syntax of the **XIRR** function is:

`XIRR(values, dates, [guess])`

**Values:**A series of cash flows corresponding to a payment schedule (in dates). The first payment is optional and corresponds to a payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All payments are discounted based on a 365-day year. The values must contain minimum one positive value and one negative value.**Dates:**Payment schedule (in dates). The dates can occur in any order in the list. But dates must be given by using the**DATE**function.**[guess]:**A number that is close to the output of XIRR.

Here we have some data relevant to **XIRR** function:

- Date values arranged in a column.
- Initial Investment Cost: $30000
- 1st Year Return: $10000
- 2nd Year Return: $5000
- 3rd Year Return: $12000
- 4th Year Return: $7000

Insert the following formula to apply **XIRR **formula to calculate the internal rate of return for non-scheduled cash flow:

`=XIRR(D5:D9,B5:B9)`

## 10 – MIRR Function (Modified Internal Rate of Return)

The** MIRR **function calculates the modified internal rate of return for a series of periodic cash flows. **MIRR **considers both the cost of the investment and the reinvestment rate.

The syntax of the **MIRR** function is:

**MIRR(values, finance_rate, reinvest_rate)**

**Values:**An array or a cell reference that contains cash amount.**Finance_rate:**The interest rate paid on the money used in the cash flows.**Reinvest_rate:**The interest rate received on the cash flows after reinvesting them.

Here we have some data relevant to **MIRR** function:

- Finance Rate: 5%
- Reinvest Rate: 10%
- Initial Investment Cost: $30000
- 1st Year Return: $10000
- 2nd Year Return: $5000
- 3rd Year Return: $12000
- 4th Year Return: $7000

Insert the following formula to apply the **MIRR **function to calculate the modified internal rate of return after 2 years:

`=MIRR(D7:D9,D4,D5)`

Insert the following formula to apply the **MIRR **function to calculate the modified internal rate of return after 4 years:

`=MIRR(D7:D11,D4,D5)`

## 11 – FVSCHEDULE Function (Future Value Applying Series of Compound Interest Rates)

**The FVSCHEDULE function** in Excel calculates the future value of an investment based on a schedule of interest rates.

The syntax of the **FVSCHEDULE **function:

`FVSCHEDULE(principal, schedule)`

**principal:**present value**schedule:**an array of interest rates

Here we have some data relevant to **FVSCHEDULE** function:

- Initial Investment Cost: $30000
- 1st Year Schedule: 5%
- 2nd Year Schedule: 7%
- 3rd Year Schedule: 8%
- 4th YearSchedule: 3%

Insert the following formula to apply the** FVSCHEDULE **function to calculate the future investment value:

`=FVSCHEDULE(C4,C5:C8)`

## 12 – PPMT Function (Principal Payment)

The** PPMT **function in Excel calculates the payment on principal for a given period.

The syntax of the **PPMT **function:

`PPMT(rate, per, nper, pv, [fv], [type])`

**Rate:**Interest rate**Per:**Period**Nper:**Number of periods in an annuity.**Pv:**Present value**[fv]:**Future value**[type]:**The number 0 or 1 and indicates when payments are due.

Here we have some data relevant to **PPMT** function:

- Year: 5
- Periods Per Year: 12
- Annual Interest Rate: 8%
- Present Value: $20000
- Future Value: $ –
- Period Number: 5
- Type(End=0/Beginning=1)

Insert the following formula to apply the** PPMT **function to calculate the payment on the principal:

`=PPMT(C6/C5,C9,C4*C5,C7,C8,C10)`

## 13 – RRI Function (Rate of Return)

The **RRI** function in Excel calculates an equivalent rate of interest for the growth of an investment.

The syntax of the **RRI **function:

`RRI(nper, pv, fv)`

**nper:**Number of Period**pv:**Present Value**fv:**Future Value

Here we have some data relevant to **RRI **function:

- Period Number: 5
- Present Value: $20000
- Future Value: $30000
- Rate of Return: 8%

Insert the following formula to apply the** RRI **function to calculate the rate of return:

`=RRI(C4,C5,C6)`

## 14 – EFFECT Function (Effective Annual Rate of Return)

The **EFFECT** function in Excel calculates the effective annual rate of interest or rate of return.

The syntax of the **EFFECT **function:

`EFFECT(nominal_rate, npery)`

**Nominal_rate:**Nominal interest rate**Npery:**number of compounding periods per year.

Here we have some data relevant to **EFFECT **function:

- Nominal Interest Rate: 7%
- Period Per Year: 12

Insert the following formula to apply the** EFFECT **function to calculate the rate of return:

`=EFFECT(C4,C5)`

## 15 – IPMT Function (Interest Payment)

The **IPMT** function calculates the due interest payment for a periodic, constant payment and interest rate for a given time period.

The syntax of the **IPMT** function is:

`IPMT(rate, per, nper, pv, [fv], [type])`

**Rate:**Interest rate**Per:**Period**Nper:**Number of periods in an annuity.**Pv:**Present value**[fv]:**(Optional)Future value**[type]:**(Optional) The number 0 or 1 indicates when payments are due.

Here we have some data relevant to **IPMT** function:

- Year: 5
- Periods Per Year: 12
- Annual Interest Rate: 8%
- Present Value: $20000
- Future Value: $ –
- Period Number: 5
- Type(End=0/Beginning=1): 0

Insert the following formula to apply the** IPMT **function to calculate the interest payment:

`=IPMT(C6/C5,C9,C4*C5,C7,C8,C10)`

## 16 – DB Function (Depreciation Value)

The **DB function** calculates the depreciation of any asset using a fixed declining way for a definite time period.

The syntax of DB function is:

`DB(cost, salvage, life, period, [month])`

**cost:**the asset’s initial cost**salvage:**the asset’s remaining value after the depreciation.**life:**the number of periods at the end of the depreciating period or the lifetime of an asset when it can be used**period:**the time period for which you want the depreciation value of the asset. It uses the unit of life.**[month]:**(Optional) The month number of first year. If it is omitted, then Excel takes it as 12 by default.

Let us calculate the depreciation value of an asset using **DB **function. We’ll use the following:

- Initial Cost: $90000
- Salvage Value: $10000
- Life in Years: 9
- Number of Months in 1st Year: 5

We will calculate the depreciation value of the asset after 3, 6, and 9 years.

To apply the **DB** function in Excel, insert the following formula in a cell:

`=DB($C$4,$C$5,$C$6,3,$C$7)`

**C4**is the initial cost**C5**is salvage value**C6**is life- 3 is the period till which we want depreciation
**C7**is the month number in 1st year

We calculated the depreciation value after 6 and 9 years, as shown in the image below.

## 17 – STOCKHISTORY Function (Get Stock’s Historical Data)

The **STOCKHISTORY** function loads the historical data from a certain period of time regarding a financial instrument (stock) as an array. When this spill formula is applied, Excel dynamically returns the data range in the required amount of cells.

*Note:**This function is available only for Microsoft 365 Personal, Family, Business Standard, and Business Premium versions
*

The syntax of** STOCKHISTORY** function is:

`STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])`

**stock:**it is the ticker symbol for the financial instrument of which you want the historical data.**start_date:**the starting date from which you want the data**[end_date]:**(Optional) the date till which you want the data**[interval]:**(Optional) This number denotes the interval of each data. “0” for daily, “1” for weekly, and “2” for monthly interval.**[headers]:**(Optional) This number is to decide if you want to display headings or not. “0” for no headers, “1” for showing headers, and “2” for showing instrument identifiers and headers.**[property0- property5]:**(Optional) This number indicates which column from historical data you want to retrieve. “0” for Dates, “1” for Close, “2” for Open, “3” for High, “4” for Low, and “5” for Volume.

Let’s see how this **STOCKHISTORY** function works in Excel. We’ll use the following information:

- Stock name/Ticker Symbol: Amzn
- Start date: 1/1//2024
- End Date: 1/20/2024

Insert the following formula in a cell that has enough blank cells below to place the data fetched using the **STOCKHISTORY** function:

`=STOCKHISTORY(C4,C5,C6,0,2,0,1)`

**C4**is ticker symbol**C5**is start date**C6**is end date- 0 returns daily data, and 2 retrieves the headers
- 0 fetches the Dates, and 1 brings the closing amount of the financial instrument

**<< Go Back to Excel Function Categories | Excel Functions | Learn Excel**