Interest rate problem computing NPER

[Guest]

I seem to be getting an error whenever I mak
the interest rate anything above 10%

Any ideas

Thanks

Rob

 

[JE McGimpsey]

Awfully hard to tell without knowing your other values. Rates above 10%
work fine for me.


Are you using appropriate rates - i.e., if you use monthly payments are
you using monthly rates?

 

[Norman Harker]

Hi Rob!

Without an example of your data as suggested by JE, I'll hazard a
guess that at high rates the amount of the payments are less than rate
* PV. In such an instance, in a (eg) loan example the repayments are
less than the interest that is accruing and the loan balance increases
to infinity and is never repaid. Excel has a problem with that.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/

 

[Guest]

Well, I got >10% to work, but now I'm having
other problems. Changing the present valu
now causes errors. I'm perplexed

Thanks again

----- JE McGimpsey wrote: ----

Awfully hard to tell without knowing your other values. Rates above 10%
work fine for me


Are you using appropriate rates - i.e., if you use monthly payments are
you using monthly rates

 

[Guest]

Okay, you guys are great. I've downloaded a sampl
from Microsoft and re-examining this. I've been
getting all kinds of weird responses when changin
the independent variables

I'll look it over and get back to you

Thanks much

----- Norman Harker wrote: ----

Hi Rob

Without an example of your data as suggested by JE, I'll hazard a
guess that at high rates the amount of the payments are less than rate
* PV. In such an instance, in a (eg) loan example the repayments are
less than the interest that is accruing and the loan balance increases
to infinity and is never repaid. Excel has a problem with that

--
Regard
Norman Harker MVP (Excel
Sydney, Australi
(e-mail address removed)

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH
See
http://www.microsoft.com/security/protect/

 

[Guest]

Alright, it looks like you are correct.

But this I do not understand: I copied the example from
http://office.microsoft.com/assistance/preview.aspx?AssetID=HP052091981033&CTT=98
whereby Rate = 12%, Pmt = 100, PV = 1000, and FV = 0

The computation for number of payments is 9.57 (effectively "10"
but that only amortizes the principal. There is no consideratio
of the accrued interest

What am I missing?!

Thanks again

----- Norman Harker wrote: ----

Hi Rob

Without an example of your data as suggested by JE, I'll hazard a
guess that at high rates the amount of the payments are less than rate
* PV. In such an instance, in a (eg) loan example the repayments are
less than the interest that is accruing and the loan balance increases
to infinity and is never repaid. Excel has a problem with that

--
Regard
Norman Harker MVP (Excel
Sydney, Australi
(e-mail address removed)

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH
See
http://www.microsoft.com/security/protect/

 

[Norman Harker]

Hi Rob!

The example you referred to uses:

Rate: 12%
Payments: -100
PV: -1000
FV: 10000
Type: 1

The third example given uses FV of 0 and Type = 1. But note that the
interest rate is 12% per annum whereas the payments are made monthly
and that with a nominal rate compounded monthly, the rate used is
12%/12. The third example is thus:

=NPER(12%/12,-100,-1000)
Returns: -9.57859403981316

Negative NPERs are difficult to conceptualise but perhaps the best way
is to consider:
=FV(12%/12,9.57859403981316,100)
Returns: -1000


Here's some examples that illustrate the point about relationship
between the rate of interest the PMT and the PV.

Using:
=NPER(12%,-100,1000,0,0)
Returns: #NUM!

Note that 12% of 1000 = 120; This is less than the repayment.

=NPER(12%,-120,1000,0,0)
Still returns #NUM!

There is still no contribution to redemption of principal

But:
=NPER(12%,-120.01,1000,0,0)
Returns: 82.880563144153


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/

 

[Harlan Grove]

The example you referred to uses:

Rate: 12%
Payments: -100
PV: -1000
FV: 10000
Type: 1 ....
=NPER(12%/12,-100,-1000)
Returns: -9.57859403981316

Negative NPERs are difficult to conceptualise but perhaps the best
way is to consider:
=FV(12%/12,9.57859403981316,100)
Returns: -1000

....

Begging the question what interpretation should be given to fractional
periods if compounding isn't continuous.

Also, you're playing fast & loose with the sign convention in drawing your
claimed equivalence between the NPER and FV function calls above. You need
to come up with a function call using *NEGATIVE* NPer argument to make your
point.

NPER is the number of periods needed for the present value of a set of
constant payments to equal the PV (when FV is zero). If PMT and PV have the
same sign and are nonzero, there's no meaningful answer even if there may be
a mathematical solution. In this case, there's no way the present value of a
series of constant payments (cash out) results in an outstanding debt. The
economics of the transaction would imply that if A doesn't pay B anything, A
owes B nothing, but if A starts paying B something, then the payments from A
to B build up an outstanding debt that A would owe B. In other words, b
would be charging A for the priviledge of A paying B periodic amounts.
Perhaps you deal with irrational economic actors on a day-to-day basis, but
the sign convention is based on rationality. If you accept the sign
convention, you have to accept the underlying economic asssumptions that
come with it.

 

[Harlan Grove]

Also, you're playing fast & loose with the sign convention in drawing
your claimed equivalence between the NPER and FV function calls
above. You need to come up with a function call using *NEGATIVE* NPer
argument to make your point.

....

WTH, here they are, either

=FV(1/(1+12%/12)-1,NPER(12%/12,-100,-1000),-100,,1)

or

=PV(12%/12,NPER(12%/12,-100,-1000),-100)

The second is an obvious tautology. The first, however, points out that
negative periods can transform positive interest rates into negative ones.

 

[Norman Harker]

Hi Harlan!

Put briefly; it would be better all round if negative NPER returns
were treated as errors.

Fractional NPV's are bad enough to have to explain in the context of
fixed dates for payments.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/

 

[Guest]

Guys

Thanks for the superb insights. I'm going to study thi
all a little bit further. (I've got the formulas workin
now but still need to get it clear in my head as to ho
it all works, particularly with regard to the monthl
compounding issue)

I wish Microsoft would explain the negative signag
convention in their help menus

Otherwise, I'm much obliged to you both for the effor
in examining this issue, though you may hear from
me again soon with additional questions

Best regards

Rob

 

[macropod]

Hi Rob,

For a discussion of the formulae etc underlying calculations like PV, FV,
NPER, etc, download the Word file at:
http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=wrd&Number=365442
Although this file deals with doing maths in Word, it contains a section on
financial calculations that you might find helpful, since the examples given
have been coded to use the same syntax as Excel

Cheers

 

[Tushar Mehta]

I wish Microsoft would explain the negative signage
convention in their help menus.

It's kinda explained in the help for the PV function. But, there
really isn't much to it. Basically, cash inflows and outflows have
opposite signs. That's it. That is all there is to it.

For the sake of simplicity, think of inflows (cash coming to you) as
positive, outflows (cash you have to pay out) as negative. So, if you
get a $1,000 loan and have to pay out $100 each period, the PV is +1000
and the PMT is -100. If, on the other hand, you invest $1,000 and
receive $100 each period, the PV is -1000 and the PMT is +100.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Harlan Grove]

...

Snipping is meant to ELIMINATE text from the previous cumulative thread to which
you're NOT responding, not duplicate the portions to which you're responding
while carrying the full previous thread baggage elsewhere in your response. If
you want to waste bandwidth, why not write responses in Word with elaborate
stationery and post as HTML? Or maybe attach a multimegabyte JPEG signature
file?