Hello and thank you for your question.
There is actually no single algebraic formula that will calculate the
interest rate that you are looking for in one cycle. As Isaac Newton
originally discovered, you need iteration to calculate the rate.
The clearest explanation of this that I've found on the Web starts
with the four formulas found at
http://oakroadsystems.com/math/loan.htm#Eq1
As the web page explains,
"[Y]ou have to use iterative methods, a fancy name for repeated
intelligent guessing. Successive guesses get closer and closer to the
true answer. Here's how.
Pick any of the first four equations. You'll get the same answer for i
no matter which one you choose, so pick the one that looks like the
least work. I'll use Equation 2:
http://oakroadsystems.com/math/pics/loaneq2.gif
Guess at an interest rate i.
Take the known values of A (principal amount) and N (number of
payments), and plug them into equation 2 with your guess for i.
Compare the result with the stated payment for the loan. OR, if using
equation 3, plug in the known A and P with your guess at i, and
compare the result with the states number of payments for the loan.
If you come up with the correct payment amount or number of payments,
STOP. You have the actual interest rate of the loan.
If your computed payment amount or number of payments is less than the
actual value, your guess at an interest rate was too small. Increase
your guess a bit and go to step 3. On the other hand, if your computed
P or N was too large, reduce your guess for i and go to step 3."
Loan or Investment Formulas
http://oakroadsystems.com/math/loan.htm#LoanInterest
I'm glad you also asked for the Excel approach, because that is much
easier to explain. The Excel formula that gives the instant answer
(the iteration happens invisibly inside the computer) is
=12*RATE(12*7,-200,0,25000,0)
Which will return an annual rate of 10.8564841699866% or
.90470701416555% per month.
In the above formula, 12*7 is the number of periods, 200 is the
payment [you need to enter it as a negative], 0 is the starting
[present] value, 25000 is the future value, and 0 means that the
payment is applied at the end of the period [or you can put 1 instead
of 0 for the last term, and the RATE will come out slightly
different].
I put this formula into an Excel spreadsheet.
Here are the values for the 84 months, with the payment being made at
the end of each month.
[Google Answers does funny things to tabular values; if these appear
run-on on this page, the first row is
1 - 200
the second row is
2 201.81 401.81
and the last row is
84 24,800 25,000]
Begin End
1 - 200.00
2 201.81 401.81
3 405.44 605.44
4 610.92 810.92
5 818.26 1,018.26
6 1,027.47 1,227.47
7 1,238.58 1,438.58
8 1,451.59 1,651.59
9 1,666.53 1,866.53
10 1,883.42 2,083.42
11 2,102.27 2,302.27
12 2,323.10 2,523.10
13 2,545.92 2,745.92
14 2,770.77 2,970.77
15 2,997.64 3,197.64
16 3,226.57 3,426.57
17 3,457.57 3,657.57
18 3,690.66 3,890.66
19 3,925.86 4,125.86
20 4,163.19 4,363.19
21 4,402.66 4,602.66
22 4,644.30 4,844.30
23 4,888.13 5,088.13
24 5,134.16 5,334.16
25 5,382.42 5,582.42
26 5,632.93 5,832.93
27 5,885.70 6,085.70
28 6,140.76 6,340.76
29 6,398.12 6,598.12
30 6,657.81 6,857.81
31 6,919.86 7,119.86
32 7,184.27 7,384.27
33 7,451.08 7,651.08
34 7,720.30 7,920.30
35 7,991.95 8,191.95
36 8,266.07 8,466.07
37 8,542.66 8,742.66
38 8,821.75 9,021.75
39 9,103.37 9,303.37
40 9,387.54 9,587.54
41 9,674.28 9,874.28
42 9,963.62 10,163.62
43 10,255.57 10,455.57
44 10,550.16 10,750.16
45 10,847.42 11,047.42
46 11,147.36 11,347.36
47 11,450.02 11,650.02
48 11,755.42 11,955.42
49 12,063.58 12,263.58
50 12,374.53 12,574.53
51 12,688.30 12,888.30
52 13,004.90 13,204.90
53 13,324.36 13,524.36
54 13,646.72 13,846.72
55 13,971.99 14,171.99
56 14,300.21 14,500.21
57 14,631.39 14,831.39
58 14,965.57 15,165.57
59 15,302.77 15,502.77
60 15,643.03 15,843.03
61 15,986.36 16,186.36
62 16,332.80 16,532.80
63 16,682.37 16,882.37
64 17,035.11 17,235.11
65 17,391.04 17,591.04
66 17,750.18 17,950.18
67 18,112.58 18,312.58
68 18,478.26 18,678.26
69 18,847.24 19,047.24
70 19,219.56 19,419.56
71 19,595.25 19,795.25
72 19,974.34 20,174.34
73 20,356.86 20,556.86
74 20,742.84 20,942.84
75 21,132.31 21,332.31
76 21,525.31 21,725.31
77 21,921.86 22,121.86
78 22,321.99 22,521.99
79 22,725.75 22,925.75
80 23,133.16 23,333.16
81 23,544.26 23,744.26
82 23,959.08 24,159.08
83 24,377.64 24,577.64
84 24,800.00 25,000.00
Search terms used:
iteration newton interest rate per period of an annuity
Thanks again for bringing us your question. If you find any my
answer, please request clarification. I would appreciate it if you
would hold off on rating my answer until I have a chance to respond.
Sincerely,
Google Answers Researcher
Richard-ga |
,
0 Comments
)
