VBA NPer Function

The VBA NPer function is used to calculate the number of periods required to pay off a loan or investment with a fixed interest rate. It takes into account the present value, future value, interest rate, and payment amount and returns the number of periods needed to reach the specified future value.

VBA NPer Function – Purpose, Syntax and Arguments

Syntax

The syntax for the NPer function is:

VBA NPer (Rate, Pmt, PV, [FV], [Type])

Where:
– Rate: the interest rate per period.
– Pmt: the fixed payment amount made each period.
– PV: the present value or principal amount.
– FV (optional): the future value or desired amount after all payments have been made. Defaults to 0.
– Type (optional): specifies whether payments are due at the beginning or end of each period. Defaults to 0, which means payments are due at the end of each period.

Arguments

• Rate: The interest rate per period expressed in percentage. For example, if the interest rate is 10%, the value should be entered as 10/100 or 0.1.
• Pmt: The fixed payment amount made each period. This amount should not change throughout the life of the loan or investment.
• PV: The present value or principal amount. This is the initial amount of the loan or investment.
• FV (optional): The future value or desired amount after all payments have been made. This is typically set to 0, which means the loan or investment will be paid off in full.
• Type (optional): Specifies whether payments are due at the beginning or end of each period. 0 represents payments at the end of each period, while 1 represents payments at the beginning of each period.

Example

Suppose we have taken out a loan of $10,000 for 5 years at an interest rate of 8% and making monthly payments of $200. We want to know how many months it will take to pay off the loan.

    
   Dim n As Double
   n = NPer(0.08/12, 200, 10000, 0, 0)
   'returns 53.27, meaning it will take 53 months to pay off the loan.
    

Remarks and Important Notes

• The NPer function can only be used for fixed interest rates. It cannot accurately calculate the number of periods for variable interest rates.
• The number returned by the NPer function includes all payments made, including both principal and interest.
• If the rate or payment arguments are not given as monthly values, they must be converted to monthly values before being used in the function.
• If the payment amount is not high enough to cover the interest, the function will return a #NUM error.
• The ‘Type’ argument only applies if the Rate argument is greater than 0.
• If the present value is negative, the NPer function will return a #NUM error.

The VBA NPer function is a useful tool for calculating the number of periods required to pay off a loan or investment. It takes into account various factors such as interest rate, payment amount, and present and future values to provide an accurate result. It is important to note the arguments and their format when using this function, as well as any potential errors that may occur.

Understanding VBA NPer Function with Examples

The NPer function, which is used to calculate the number of periods required to reach a specific goal, such as paying off a loan or reaching an investment goal. In this blog post, we will explore the NPer function in detail and provide multiple examples to help you understand its usage better.

Example 1: Calculating the Number of Periods required to pay off a loan

Suppose you have taken a loan of $10,000 with an interest rate of 5% per annum, and you want to pay it off in 10 years. You want to know the number of monthly payments you would have to make to clear the loan. This is where the NPer function comes in handy.

Dim LoanAmount As Long
Dim InterestRate As Double
Dim NumberOfYears As Integer
Dim TotalNumberOfPayments As Integer
LoanAmount = 10000
InterestRate = 0.05
NumberOfYears = 10
TotalNumberOfPayments = - NPer(InterestRate / 12, NumberOfYears * 12, LoanAmount)

1. LoanAmount variable is declared as Long to store the loan amount of $10,000.
2. InterestRate variable is declared as Double to store the interest rate of 5% per annum.
3. NumberOfYears variable is declared as Integer to store the number of years for which the loan is taken, which is 10 years in this case.
4. The NPer function is used to calculate the number of periods required to repay the loan.
5. The first argument of the function is the interest rate per period, which is calculated by dividing the annual interest rate by 12, as it is a monthly loan.
6. The second argument is the total number of payments, which is calculated by multiplying the number of years by 12, as there are 12 months in a year.
7. The third argument is the loan amount, which is entered as a negative value as it represents a cash outflow.
8. The result of the NPer function is then stored in the TotalNumberOfPayments variable.

The value of the TotalNumberOfPayments variable would be 120, which means that you would have to make 120 monthly payments of $86.07 to pay off the loan of $10,000 at an interest rate of 5% per annum in 10 years.

Example 2: Calculating the Number of Periods required to reach an investment goal

Another use case for the NPer function is to calculate the number of periods required to reach a specific investment goal. Suppose you have invested $5,000 in a mutual fund that promises a return of 8% per annum, and you want to know how many years it would take for your investment to grow to $10,000.

Dim PresentValue As Double
Dim FutureValue As Double
Dim InterestRate As Double
Dim TotalNumberOfPeriods As Double
PresentValue = -5000
FutureValue = 10000
InterestRate = 0.08
TotalNumberOfPeriods = - NPer(InterestRate, 0, PresentValue, FutureValue)

1. PresentValue variable is declared as Double to store the initial investment amount of $5,000.
2. The FutureValue variable is declared as Double to store the desired investment goal of $10,000.
3. The InterestRate variable is declared as Double to store the annual return rate of 8%.
4. The TotalNumberOfPeriods variable is used to store the result of the NPer function.
5. The first argument of the function is the interest rate per period, which is the annual return rate in this case.
6. The second argument is 0 as there are no regular payments made in this scenario.
7. The third argument is the present value, which is entered as a negative value as it represents a cash outflow.
8. The fourth argument is the future value, which is entered as a positive value as it represents a cash inflow.
9. The result of the NPer function is then stored in the TotalNumberOfPeriods variable.

The value of the TotalNumberOfPeriods variable would be 9.46, which means that it would take approximately 9 and a half years for your investment of $5,000 to grow to $10,000 at a return rate of 8% per annum.

Example 3: Calculating the Number of Periods required to reach a retirement savings goal

The NPer function can also be used to plan for retirement. Suppose you want to save $500,000 for your retirement, and you have 30 years left before you retire. You want to know the number of monthly contributions you would have to make to your retirement account to reach your goal, assuming an interest rate of 6% per annum.

Dim FutureValue As Double
Dim MonthlyContribution As Double
Dim InterestRate As Double
Dim TotalNumberOfPeriods As Double
FutureValue = 500000
MonthlyContribution = -500
InterestRate = 0.06 / 12
TotalNumberOfPeriods = - NPer(InterestRate, 30 * 12, MonthlyContribution, FutureValue)

1. FutureValue variable is declared as Double to store the retirement savings goal of $500,000.
2. The MonthlyContribution variable is declared as Double to store the desired monthly contribution amount of $500.
3. The InterestRate variable is declared as Double to store the monthly interest rate, which is calculated by dividing the annual interest rate by 12.
4. The TotalNumberOfPeriods variable is used to store the result of the NPer function.
5. The first argument of the function is the interest rate per period, which is the monthly interest rate in this case.
6. The second argument is the total number of payments, which is calculated by multiplying the number of years by 12, as there are 12 months in a year.
7. The third argument is the monthly contribution, which is entered as a negative value as it represents a cash outflow.
8. The fourth argument is the future value, which is entered as a positive value as it represents a cash inflow.
9. The result of the NPer function is then stored in the TotalNumberOfPeriods variable.

The value of the TotalNumberOfPeriods variable would be 214.37, which means that it would take approximately 215 months (17 years and 10 months) to reach your retirement savings goal of $500,000 by making monthly contributions of $500 at a monthly interest rate of 6%.

Conclusion

In this blog post, we explored the NPer function in VBA, which is used to calculate the number of periods required to reach a specific goal. We discussed three different examples to help you understand its usage better. It is a handy function for financial planning and analysis and can be used in various scenarios, such as loan repayments, investment planning, and retirement savings. With the knowledge of the NPer function, you can now automate various financial calculations and make informed decisions.