Calculating an interest rate

[Mike Shaw]

I am operating Excel 2000.

I am needing to calculate an interest rate based on the following:

Present Value
No. of periods in the annuity
Repayment Amount.

The problem is that the term of the amortisation is 30 years and as the repayments are weekly the total number of periods in the annuity is 1560.

I understand that the maximum periods in annuity on which Excel can calculate an interest rate is 1420.

As it is not inconceivable to make weekly repayments and as 30 years is not an extraordinarily long term to repay a debt (particularly long term commercial commitments)I would think there must be some way to work it out.

I would appreciate it if anyone could advise if there is a way to work around this limitation so as to achieve the calculation to determine the interest rate based on 1560 periods in the annuity.

Thank you in advance for any advice that may be offered.

[Dave Patton]

[quote]
On 2002-05-19 00:17, Mike Shaw wrote:
to calculate an interest rate based on the following:

The problem is that the term of the amortisation is 30 years and as the repayments are weekly the total number of periods in the annuity is 1560.

I understand that the maximum periods in annuity on which Excel can calculate an interest rate is 1420.

comments:
1. can you cite the reference for the max of 1420?

with my testing, =Rate works with 1560 payments
2. you could use =PMT and with a few guesses you could derive the interest rate
3. you could use Solver

Try rate again.
A better answer could be given if you provided a concise example and the result that you expected.

[ This Message was edited by: Dave Patton on 2002-05-19 11:48 ]

[Mike Shaw]

Dave,

Thank you for your reply.

I can't recall where i saw the reference about the maximum number of periods on which Excel can calculate an interest rate. It was about six months ago and my recent searches have been fruitless.

It was, I think, somewhere within the programs help function.

The exact figures of my calculation are:

Present Value -$121,503.38
Periods 1560
Repayment $218.91
Future Value 0
Payment at end of each period
Then multiplied by 52 to annualise the rate.

The result I get for any period greater than 1419 is #NUM!.

With 1419 periods the result is 8.427% per annum. For some reason if I increase the repayment amount to some ridiculous figure i.e. $1,439 per week (the lowest amount that would work) I can calculate the rate on 1560 periods. In that instance 61.585% per annum i.e. because the repayment amount is so high.

I know that the correct result should be 8.672% per annum. However, this calculation is both dependant on other calculations, a precedent for further calculations and just a part of a much larger workbook with many "what if" sensitivities built in and as such it is not practical to try to work the result backwards by guessing a rate using PMT =.

In addition as a matter of principle I dont want it to beat me.

Perhaps it has something to do with the Solver function you mentioned. I have not used this function before and for some reason when i try to add-in "Solver Add-In" I can't proceed further. Assuming thats what is needed.

Again thanks for your suggestions. Given the above further details if there is anything further you or anyone else could suggest I would appreciate it greatly.

Regards

Mike



[ This Message was edited by: Mike Shaw on 2002-05-20 00:55 ]

[Dave Patton]

try

=RATE(30*52,-218.91,121503.38,0,0,0.08/52)*52

I get 8.67261%

[Mike Shaw]

Dave,

Thanks again for your help. I changed the formula in the cell to include 0.08 and now it is allowing the calculation of the interest rate with the number of periods in the annuity exceeding 1420.

I did some testing with a number of scenarios and they all came up correctly.

I was wondering where you located the additional information to include in the calculation i.e. (0.08/52).

Initially I thought that the 0.08 was reflective of the particular example, however, with testing I could see that I was able to eliminate the /52 part you added to the formula and could vary the 0.08 between (from memory) 0.02 and 0.00009 and still come up with the correct results on several "what if's" so I am very happy with the results.

It seems that it just needed this additional info to allow the additional periods. As I said I'm not sure how you determined 0.08/52 was to be added or why this causes the correct result to be displayed. It would be interesting to know why.

Again thank you very much for help. It is greatly appreciated.

Regards


Mike

[Dave Patton]

The formula requires a guess of the interest rate. A rate of 8% or 10% could be a good guess for annual interest. Since the payments that you cited are weekly, I used
.08/52.

[Mike Shaw]

Thanks for the explanation.