NPER Function Question?

[MXL]

Greetings

I have a question regarding the NPER function when you have a different compounding period of interest on the loan and a different periodic payment you make on that loan.

Loan: $5,000
Interest Compounded: Daily
Annual Interest Rate on Loan: 10%
Loan Period Compound Rate (based on daily or 365 days): 10%/365 = 0.000273%
My Periodic Payment: $50 (I can choose how much I wish to pay)
Period Payments: 4, Quarterly (this means I can choose to pay how many times I wish during the year, in this case, I choose to pay $50 each quarter, or $50, 4 times per year)

I wanted to know, how many quarterly payments I need to make in order to pay off the $5,000 loan at an interest rate of 10% compounded daily?

I tried this in the NPER function,

=NPER(0.000273%,-50*4,-5000,,) = 24.9

The answer it gave me was 24.9 which meant 24.9 quarterly periods, 24.9 x 4 x $50 = $4,982, it is not even close to $5,000 much less taking into account the interest.

Look forward to hearing from you all, thank you for taking the time to read this, much appreciated.

 

[joeu2004]

I have a question regarding the NPER function when you have a different compounding period of interest on the loan and a different periodic payment you make on that loan.

Loan: $5,000
Interest Compounded: Daily
Annual Interest Rate on Loan: 10%
Loan Period Compound Rate (based on daily or 365 days): 10%/365 = 0.000273%
My Periodic Payment: $50
[....]
I wanted to know, how many quarterly payments I need to make in order to pay off the $5,000 loan at an interest rate of 10% compounded daily?

I tried this in the NPER function,
=NPER(0.000273%,-50*4,-5000,,) = 24.9

No: NPER returns about -24.9, a negative number!

The correct usage is:

B1, loan:      5000
B2, annl rate:   10%
B3, daily rate:   0.027397%  =B2/365
B4, qtrly rate:   2.531161%  =(1+B3)^(365/4)-1
B5, min pmt:    126.56       =ROUNDUP(B1*B4,2)
B6, #qtrs:      443          =ROUND(NPER(B4,B5,-B1),0)

Whether to round, round up or round down NPER depends on lender policy. But in this case, NPER is returning the number of payments, which must be an integer.

The daily rate (B3) is the annual rate divided by 365, as you did. Some people might suggest using 365.25, in deference to leap years over the long run. But that incorrectly understates the daily rate in 3 out of 4 years, and it overstates the daily rate in 1 out of 4 years. If we want to account for leap years, we must divide by 366, but only in those years.

The compounded daily rate paid quarterly (B4) is based on the average days per quarter (365/4). In reality, a lender probably uses the actual days in each quarter, which is 89 to 92 days. Note that a quarter is 92 days nearly 58% of the time. So arguably, we might compound by 92 instead if 365/4 to be conservative.

The minimum payment (B5) must at least cover the initial interest in order to avoid "negative amortization". Otherwise, the balance will increase instead of decrease; and Excel NPER returns a #NUM error. Since the initial interest is about 126.56 (B1*B4), you cannot use 50.00 arbitrarily.

And noting again that a quarter is 92 days nearly 58% of the time, a more conservative minimum payment would be =ROUNDUP(B1*((1+B3)^92-1),2), which is 127.62, regardless of what we choose for the quarterly rate in B4.

In either case, payment must be rounded up at least to the cent (smallest coin of the realm) due to real-world constaints, again to avoid negative amortization.

Finally, it is important to note the opposite signs of the pmt (B5) and pv (-B1) parameters to NPER. If the fv parameter is not zero, it must be the same sign as the pmt parameter. This is a common Excel convention to denote the opposite cash flow directions.

The choice of negative for one direction and positive for the other direction is arbitrary. Technically, it depends on which point of view you want to adopt: lender or borrower. In practice, it depends on the sign of the result that you want for some functions -- although it makes no difference for the NPER function.

Also, it is important that the rate (B4) and pmt (B5) parameters be the same unit of time. Since you want the number of quarters (quarterly payments), the rate and pmt parameters must be quarterly, not daily and annually respectively as you did.

 

[joeu2004]

PS....

Interest Compounded: Daily
Annual Interest Rate on Loan: 10%
Loan Period Compound Rate (based on daily or 365 days): 10%/365 = 0.000273%

The correct usage is:

B2, annl rate:   10%
B3, daily rate:   0.027397%  =B2/365
B4, qtrly rate:   2.531161%  =(1+B3)^(365/4)-1

You might want to double-check your assertion that interest is truly compounded daily. This is a common source of misunderstanding and misstatements.

In some regions (notably the UK and EU), the advertised rate is a daily-compounded rate. This is called the APR or AER.

But that is just for reporting purposes. Some people believe it makes it easier to compare loans with different terms.

Mechanically, however, simple annual interest is calculated per billing cycle. And the periodic rate is the simple annual rate (not the APR) divided by the billing periods per year.

In your example, if 10% is truly the simple annual interest rate and the daily rate is indeed 10%/365 as you wrote, the quarterly rate might be approximately 3*10%/12, not (1+10%/365)^(365/4)-1.

The lender might specify a daily rate because the actual simple quarterly rate is based on the true number of days in each quarter; that is, 89 to 92 days.

On the other hand, if 10% is called the APR or AER (not "interest rate"), the daily rate is (1+10%)^(1/365)-1. And the quarterly rate might be approximately (1+10%)^(1/4)-1.

 

[joeu2004]

Errata....

In your example, if 10% is truly the simple annual interest rate and the daily rate is indeed 10%/365 as you wrote, the quarterly rate might be approximately 3*10%/12, not (1+10%/365)^(365/4)-1.

Duh, that is 10%/4.

 

[MXL]

oh yes, thank you joeu2004 for your wonderfully insightful post, you are correct 100%, i cannot choose how much I wish to pay because as you said, i would end up paying more interest and it would give me #NUM error. Thank you again, sir!