Microsoft Office Tutorials and References

In Depth Information

**Using RRI to Calculate the Investment Return After Many Years**

•
When money changes hands, consider the direction in which money

flows. In any transaction, some cash flows toward you (positive),

and some cash flows away from you (negative). If you try to enter all

terms as positive, you end up with a result that is not meaningful. For

example, if you do not make the payment or pv negative, you are saying

that you want a car loan where the bank gives $20,000 at the beginning

and then gives you another $377 per month. NPER(5%/12,377,20000)

would come up with an incorrect result for your problem because one

of the cash flows needs to be negative. If you consider the loan from

the point of view of the customer, the formula would be NPER(5%/

12,
–
377,20000). If you consider the loan from the point of view of the

bank, the formula would be NPER(5%/12,377,
–
20000).

Using

Using
RRI

RRI
to Calculate the Investment Return After Many Years

to Calculate the Investment Return After Many Years

You left your starter job a decade ago and rolled your 401K into a rollover

IRA. How well has that IRA performed? The new RRI function calculates an

average interest rate.

Caution

RRI is new in Excel 2013. This function returns a #NAME? error if you

open the workbook in Excel 2010 or earlier.

Enter the number of years the money was in the IRA, the starting amount, and

the ending amount. RRI calculates the result.

Syntax

RRI(nper,pv,fv)

The RRI function calculates the equivalent interest rate for the growth of

an investment. This function takes the following arguments:

•
nper

nper
—
This is the number of years that the money was invested.

•
ppv
—
This is the original amount invested.

•
ffv
—
This is the ending value of the investment account.

Figure 13.1
shows an example. If $150,000 grew to $175,000 in 12 years, the re-

turn was essentially 1.29% per year.