Financial Functions, in particular, the INTRATE function

[Ronald R. Dodge, Jr.]

Why did MS have the financial function of INTRATE basically use the
following formula when it's way too simplistic using the simple interest
rate rule that has no practical use in life given how money works or even
how anything else works in life:

(FV-PV)/PV/NP

FV = Redemption Amount
PV = Initial Investment Amount
NP = Number of Years (Normally thought of as number of periods during the
time period, which in this case, 1 year is one period for how the formula is
setup)

To come up with the actual interest rate using the compounding interest
method, one must use the following formula:

(FV/PV)^(1/NP)-1

Under the simple interest method the INTRATE formula uses, for cost of
living that is assumed to double every 10 years, it returns 10%

Obviously, things don't go up by 10% every year, which would mean after 10
years, things would cost 159.3742% more than what they had cost at first as
a result of PV*(1.1^10-1)

To use the formula that I have stated, MS has no financial function to use
that particular formula (at least not built into the Analysis Tookpak
Add-in).

FV/PV = 2
NP = 10

2^1/10-1 = 2^0.10-1=0.071773463

Hence the real annual effective rate for the cost of living to double every
10 years is 7.1773463%, which most people just round to 7.2%, which then has
led to the rule of 72 that says to divide 72 by the interest rate and divide
by 100.

Example:

Interest rate is 8.00%

In the computer form, it would show up as 72/.08/100

which then would say it would take 9 years to double.

Of course, rule of 72 isn't a perfect thing as it's only an estimate and
only works within a certain range. If one really want to know how many
years it would take for such investment to double at a such stated APR, then
they would need to use the following formula:

(LOG(FV)-LOG(PV))/LOG(1+R)=NP

Example
Find out how long it would take for an investment to double such as going
from 1 to 2 with a stated APR compounded only one time per year

(LOG(2)-LOG(1))/(LOG(1.08)=9.006468 years

I also have noticed Excel doesn't have this formula in it either.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Fred Smith]

Intrate is for simple interest.
Rate calculates compound interest
Nper calculates the term.
=nper(8%,0,-1,2) = 9.006468 years, just as you would expect.

If you need more help, just look up Financial Functions in help.

Regards,
Fred.

 

[Ronald R. Dodge, Jr.]

Rate and many other financial functions requires a Payment amount other than
0. INTRATE doesn't use a Payment amount.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Fred Smith]

You need to brush up on your financial functions. None of the functions you
mentioned require a payment amount. Just put in zero, as shown in my
example.

I know it's tempting to assume Excel is wrong, but that's not always the
case.

Regards,
Fred.

 

[Ronald R. Dodge, Jr.]

I know how to read the different arguments. Example with the RATE function

=RATE(nper,pmt,pv,[fv],[type],[guess])

When I put in the following:

=RATE(10,0,1000,2000)

It returns #NUM!

With a simple situation like what I mentioned, why would it need to go
through an iteration process when it can use a formula like below to avoid
the iteration issue?

(FV/PV)^(1/NP)-1



--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Ronald R. Dodge, Jr.]

I see the issue, was putting in the PV as a positive number rather than as a
negative number. However, it's not intuitive to be thinking of converting
something from positive to negative.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

I know how to read the different arguments. Example with the RATE function

=RATE(nper,pmt,pv,[fv],[type],[guess])

When I put in the following:

=RATE(10,0,1000,2000)

It returns #NUM!

With a simple situation like what I mentioned, why would it need to go
through an iteration process when it can use a formula like below to avoid
the iteration issue?

(FV/PV)^(1/NP)-1



--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

You need to brush up on your financial functions. None of the functions
you mentioned require a payment amount. Just put in zero, as shown in my
example.

I know it's tempting to assume Excel is wrong, but that's not always the
case.

Regards,
Fred.