Problems with "Rate" function

[Stuart Crofts]

I have a simple loan that I want to calculate the interest
rate for.
6 months ago I owed say £8,000. I've paid £100 per month,
Now I owe say £7,700.
I've tried rate(Nper=6,Pmt=100,PV=-8000,FV=-7700)
I've also tried reversing the PV and FV (Assuming it can't
work out a negative rate of interest??) and the Pmt
figure - all to no avail.
Anyone any suggestions?

 

[Bernard Liengme]

Hi Stuart,
You have to look at it like this.
The bank gave you 8,000 (pv); this is money TO you so it is positive
You paid the bank 100 (pmt); this is money FROM you so it is negative.
To settle the loan after 6 months, you would have to paid 7,700; this is
money FROM you so it is negative
Looked at another way, it cannot be positive because you are not being given
it, rather it is a liability that you owe.
Formula is =RATE(6,-100, 8000, -7700)
The result is 0.635% but this is the monthly rate since we used 6 months and
100/month. So the ARP is 0.635 * 12 =7.62%
Hope this helps,
Bernard
www.stfx.ca/people/bliengme

I have a simple loan that I want to calculate the interest
rate for.
6 months ago I owed say £8,000. I've paid £100 per month,
Now I owe say £7,700.
I've tried rate(Nper=6,Pmt=100,PV=-8000,FV=-7700)
I've also tried reversing the PV and FV (Assuming it can't
work out a negative rate of interest??) and the Pmt
figure - all to no avail.
Anyone any suggestions?

 

[J.E. McGimpsey]

One correction. Twelve times the monthly rate is the Nominal
interest rate, not the APR. The APR by definition takes compounding
into effect:

APR = (1+PR)^nPer - 1

where PR is the periodic rate (monthly, quarterly, etc), and nPer is
the number of periods per year. so for a monthly rate of 0.635%, the
APR is

=(1 + .00635)^12 - 1 ===> 7.89%

You can also calculate this using the Analysis toolpak add-in's
EFFECTIVE() function:

=EFFECT(12*0.635%,12) ===> 7.89%