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).
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.
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.
The RRI function calculates the equivalent interest rate for the growth of
an investment. This function takes the following arguments:
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.