compound interest

[geroneill]

Hi Ger here from Ireland.
Can you help me with following
I need to find a formula to calculate the compound
interest rate on an investment as follows
Initial investment year 1 = 33,663 which is invested for
11 years and has a value of 112,719 at end of year 11.
What function/formula should I use to determin what the
compound interest rate is?
Many thanks
Ger

 

[Bernard V Liengme]

Hi Ger,
If P is invest at an annualized rate R its value at the end of N years is
given by F =P(1+R)^n
However, this is a very naive way of looking at things! Is this money in the
bank? How often does the Bank compound the interest - monthly, quarterly,
annually? IF it is monthly then F =P(1 + R/12)^N*12
Simple algebra will let you rearrange these equations to get R = .....
Or read Help to see how the RATE function works (the trick is to get PV and
FV with the right signs)
By the way I get 12% for an annual compounding and 11% for a monthly one.
Bernard

 

[Ken Wright]

Whilst there are functions that will get you what you want, as long as you can
do it on paper you can do it on a spreadsheet:-

Try this which will give you 11.613% if you actually had 11 years worth of
escalation to get you to the 112,719, ie the 33663 value was the START of year
1, and the 112,719 figure was the END of year 11.

=(112719/33663)^(1/11)-1

As long as you can do it manually on a piece of paper, then you should be able
to put it on a spreadsheet:-

Had you started with 33663, then you would have done the following to escalate
it each year

Yr 1 =33663 * (1 + Int rate)
Yr 2 =33663 * (1 + Int rate) * (1 Int rate)
Yr 3 =33663 * (1 + Int rate) * (1 Int rate) * (1 Int rate)
Yr 4 =33663 * (1 + Int rate) * (1 Int rate) * (1 Int rate) * (1 Int rate) etc

which equates to the following:-

Yr 1 =33663 * (1 + Int rate) ^ 1
Yr 2 =33663 * (1 + Int rate) ^ 2
Yr 3 =33663 * (1 + Int rate) ^ 3
Yr 4 =33663 * (1 + Int rate) ^ 4 etc

so for 11 years worth, you would have

Yr 11 =33663 * (1 + Int rate) ^ 11 = 112,719

Now, in your case you already have Yr 11 (112,719), 33663 and the 11, so reverse
engineer those:-

112,719 = 33663 * (1 + Int Rate) ^ 11 =>
112,719 / 33,663 = (1 + Int Rate) ^ 11 =>
(112,719 / 33,663)^(1/11) = 1 + Int Rate =>
(112,719 / 33,663)^(1/11) - 1 = Int Rate <<Which is what you are after

You can also simply use the RATE function, eg:-

=RATE(11,0,-33663,112719)

The only non-intuitive bit is that you have to represent the starting amount as
a payment out of the account (ie negative), whilst the final result is Payment
into the account (ie positive)

 

[Norman Harker]

Hi Ger!

In formula terms using Excel's algorithm (summarised for a simple
accumulation) and thus using a signing of money flows convention:

FV + PV*(1+RATE)^NPER = 0

RATE = (FV/PV)^(1/NPER)-1

=(112719/33663)^(1/11)-1
Returns: 11.6125727637887%

In Function terms:

=RATE(11,0,-33663,112719,0,0)
Returns: 11.6125727637887%

Negative represents a payment out of the initial investment. Positive
represents the return of the investment. Since the number of periods
is expressed in years the rate returned is the annual effective rate.
If you want to compare this with the APR12 then you need to convert
using:

=((1+RATE(11,0,-33663,112719,0,0))^(1/12)-1)*12
Returns: 11.0367971435457%

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.