NPER function - The function calculates the period number of an investment in Excel

How to use the NPER function in Excel to plan loans and savings
Coupdays function - Calculates the number of days in a coupon period and contains the settlement date in Excel

Usually, the interest rate for securities is usually calculated by period. So if you are a stock player, please know this calculation offline to be able to actively calculate the interest received. The following article shows how to use the NPER function to calculate the number of periods for an investment.

Description: The function is used to calculate the number of periods for an investment with a given security based on equal payments and a constant interest rate.

Syntax: NPER (rate, pmt, pv, [fv], [type]) .

Inside:

- rate: Interest rate of securities according to maturity, required parameters.

- pmt: Payments for each period, required parameters. Typically pmt contains principal and interest.

- pv: Current value or one-off payment amount corresponding to future payments, it is a required parameter.

- fv: Future value or your desired balance after the final payment is made, this parameter is not required, if omitted the default is 0.

- type: Determine the payment time, there are 2 values: 0 => final payment and 1 => payment at the beginning of the period.

Example: Give the following data table.

NPER function - The function calculates the period number of an investment in Excel Picture 1

- Calculate the number of periods of the investment.

- Calculate the number of periods of the investment minus initial payments.

- Calculate the number of periods of the first paragraph unless the future value is 0.

1. Calculate the number of periods of the investment

- In the cell to calculate enter the following formula: = NPER (D5 / 12, D6, D7, D8, D9) .

NPER function - The function calculates the period number of an investment in Excel Picture 2

- Results:

NPER function - The function calculates the period number of an investment in Excel Picture 3

2. Calculate the number of periods of the investment minus the initial payments

- In the cell to calculate, enter the formula: NPER (D5 / 12, D6, D7, D8) implies the final payment.

NPER function - The function calculates the period number of an investment in Excel Picture 4

- Results:

NPER function - The function calculates the period number of an investment in Excel Picture 5

3. Calculate the number of periods in the first clause unless the future value is 0

- In the cell to calculate enter the following formula: = NPER (D5 / 12, D6, D7) .

NPER function - The function calculates the period number of an investment in Excel Picture 6

- Results:

NPER function - The function calculates the period number of an investment in Excel Picture 7

The result of comparing 3 cases if the payment at the end of the period is higher than that at the beginning of the period, the case of the future value equal to 0, the number of periods is too small to be negative.

Hopefully with the above article you will have the best choice.

Good luck!

Kareem Winters

Update 19 May 2020

You should read it

May be interested

VAR.P function - Function that calculates variance based on the entire set, ignoring logical values and text in Excel
var.p function: function that calculates variance based on the entire set, ignoring logical values and text. support functions from excel 2010 onwards. syntax: var.p (number1, [number2], ...)
DB function - The function calculates the depreciation of assets with specific maturity in Excel
the following article details the db function - the function of depreciation of assets with specific maturity with the use of the method of calculating the declining balance by fixed level.
How to use SUMPRODUCT function in Excel
sumproduct function in excel is a function that calculates the total value of corresponding products that users conduct zoning.
Round function, how to use rounded functions in Excel
the round function in excel will help the user round the number to a number of predefined words, making the worksheet more scientific.
GROWTH function - The function calculates the exponential growth value using existing data in Excel
growth function: the function calculates the exponential growth value using existing data. syntax: growth (known_ys, [known_xs], [new_xs], [const])
TBILLPRICE function - The function calculates the value of treasury bonds by the $ 100 face value in Excel
the following article details the meaning and usage of the tbillprice function - the function of calculating the value of treasury bonds at $ 100 face value in excel.
Round a number in Excel (ROUND function)
instructions on how to round numbers in excel, help calculate faster depending on how to round the number of digits after and before the comma, there are many number rounded functions in excel. 1. round a number using the round () function - meaning: rounds to the nearest number
AMORDEGRC function - The function returns the depreciation for each accounting period in Excel
excel is a powerful tool in financial accounting. in each accounting period depreciation values are always mentioned. the following article details the amordegrc function, the function returns the depreciation for each accounting period.
TBILLYIELD function - The function calculates the discount rate for a bond in treasury in Excel
the following article details the meaning and specific examples of how to use the tbillyield function - the function of calculating the discount rate for a bond in the treasury.
TBILLEQ function - Calculates the returns corresponding to bonds in treasury in Excel
the following article details the meaning and usage of tbilleq function - calculate the profit corresponding to bonds in the treasury.