Calculate The Period of Loan or Investment in Excel

In our daily life, most of us will invest or take a loan, so we need to master some simple financial functions to calculate the period needed for loans or investments. If you work as an accountant, you need to master some basic Excel financial functions even more.

In fact, Excel provides many basic financial functions (such as IRR, FV, PMT, etc.). By applying these functions, we can make a simple estimate of interest rate, period, payment, etc.

Today we will introduce an Excel financial function NPER. The function name consists of Number + Period. It is a function often used by accountants. In addition to the basic introduction of the function, we will also provide some examples to illustrate the usage of the function.

NPER FUNCTION

 Excel NPER function is a financial function, based on a fixed interest rate and equal payments, used to calculate the number of periods of a loan or investment.

Syntax:

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

 Arguments:

  • rate – it is the interest rate for each period, which is a fixed value
  • pmt – it is the amount payable for each period and remains constant throughout the annuity period. Typically, payment includes principal and interest, but excludes other fees and taxes.
  • pv – present value. This is the sum of the amounts already accounted for from the beginning of the calculation of the investment, or the cumulative sum of the current values of future payments, also known as the principal.
  • fv – future value [optional]. The cash balance after the last payment, if it is omitted, is assumed to have a value of zero, i.e., the future value of a loan is zero.
  • type – 0 or 1 to specify whether the payment for each period is at the beginning or at the end of the period. Optional.

NPER FUNCTION EXAMPLES

Example1: Calculate the number of repayment periods for the loan

To repay a loan of 50,000 with an annual interest rate of 5% and a monthly repayment of 1,000 (at the end of each month), calculate the number of months required to repay the loan.

Calculate The Period of Loan or Investment in Excel1

In this example, the formula is:

=NPER(C2/12,-C3,C4)

 Rate:

C2/12 (5%/12). Rate in C2 is annual interest rate. Since we want to know the number of periods (months) to repay the loan, we use C2/12 to get the monthly interest rate.

 PMT:

-C3. The equal payment is $1000. Because it is used to pay the loan, it is a cash outflow, so it has a negative value. So for the argument payment, it has a value of “-C3”.

 PV:

C4, present value is $50000. That means we will pay for a loan of $50000.

FV:

The future value of the loan is 0.

Type:

Type is 0. At the end of period.

In this example, the result is 56.18, so it takes 57 months to repay the loan.

Example2: Calculate the number of installments

 Suppose an item is purchased at a cost of 10,000, and the monthly payment of 1,000 is made at the end of each month, and the monthly compounding rate is 0.5%, calculate the number of months of repayment.

Calculate The Period of Loan or Investment in Excel1

In this example, the formula is:

=NPER(C2,-C3,C4)

 This example is similar to the previous one. The difference is that this is a monthly interest rate, so you don’t have to divide it by 12 months.

Example3: Calculate the cumulative investment period

 Suppose the annual rate of return on investment is 10%, now you have invested $100000, add $10000 every year, calculate the number of years to reach 1 million.

Calculate The Period of Loan or Investment in Excel1

In this example, the formula is:

=NPER(C2,-C3,-C4,C5)

 In this example, we are calculating the number of years it would take to reach $1 million with a 10% annual return, so the period is also “years”. Since payments and present value are paid for investments, they are cash outflows, so they are negative in the formula. The future value is out expected value $1 million.

Related Functions

  • Excel NPER Function
    The Excel NPER function returns the number of periods for an investment or loan based on periodic payment amount and a constant interest rate.The NPER function is a build-in function in Microsoft Excel and it is categorized as a Financial Function.…

Related Posts

Excel Formulas To Calculate The Bond Valuation

Assume that you've been assigned a task of calculating bond valuation; so if you are new to Ms Excel or do not have much experience with it, then I am pretty sure about it that doing this task manually might ...

Calculate Cumulative Totals with Excel SUM Function

Today, through a simple example, we will show you how to use one of the most common-used Mathematical functions in excel, the SUM function, to add up the sum. In our daily life, we keep an account of what we ...

BMI Calculation Formula In Ms Excel

You might have come across a task in which you were assigned to make BMI calculations of the supplied numbers, and you may be looking for an efficient approach to accomplish this process rather than doing BMI calculations manually, by ...

Working Time Calculation Based on Timesheets

In the office, a special machine record the time when you start working (clock in office) and when you finish working (clock out of office). We can calculate the total working time by subtracting the end working time from the ...

Sort/Rank Numeric Values with Duplicate Values Exist

Excel built-in RANK function can sort a set of values. If there are duplicate numbers, then the rank number is also duplicated. See the following example: There are two numbers “100” in range A2:A9, they are both the third largest ...

Sidebar