Determining i or n for Known Cash Flow Values

When all the cash flow values are known or have been estimated, the   i  value (interest rate or rate of return) or   n  value (number of years) is often the unknown. An example for which   i  is sought  may be stated as follows: A company invested money to develop a new product. After the net  annual income series is known following several years on the market, determine the rate of return  i  on the investment. There are several ways to find an unknown i or n value, depending upon the  nature of the cash flow series and the method chosen to find the unknown. The simplest case involves only single amounts (  P  and   F ) and solution utilizing a spreadsheet function. The most difficult and complex involves finding   i  or   n  for irregular cash flows mixed with uniform and  gradient series utilizing solution by hand and calculator. The solution approaches are summa-rized below, followed by examples.

Single Amounts —  P  and   F  Only 
Hand or Calculator Solution     Set up the equivalence relation and (1) solve for the variable
using the factor formula, or (2) find the factor value and interpolate in the tables. Spreadsheet Solution      Use the IRR or RATE function to find   i  or the NPER function to find n . (See below and Appendix A for details.)  

Uniform Series — A  Series
Hand or Calculator Solution      Set up the equivalence relation using the appropriate factor
(  P/A ,    A/P ,    F/A , or   A/F ), and use the second method mentioned above. Spreadsheet Solution      Use the IRR or RATE function to find   i   or the NPER function to find   n .   

Mixed A Series, Gradients, and/or Isolated Values
Hand or Calculator Solution      Set up the equivalence relation and use (1) trial and error or (2) the calculator functions. 

Spreadsheet Solution      Use the IRR or RATE function to find   i  or the NPER function to find  n . (This is the recommended approach.)

Besides the PV, FV, and NPV functions, other spreadsheet functions useful in determining   i   are IRR (internal rate of return) and RATE, and NPER (number of periods) to find   n .  The  formats  are shown here and the inside front cover with a detailed explanation in Appendix A. In all three  of these functions, at least one cash flow entry must have a sign opposite that of others in order  to find a solution.

To use IRR to find   i , enter all cash flows into contiguous cells, including zero values.

The single-cell RATE function finds   i  when an   A  series and single   P  and/or   F  values are  involved.

 NPER is a single-cell function to find  n  for single   P  and   F  values, or with an   A   series.