Appendix B: Return on Investment (ROI) Calculator
                          This worksheet explains formulas usually used  in Payback Period, IRR, and NPV calculations.
                                Example 1                                 Annual         Year 1       Year 2       Year 3       Year 4       Year 5
                                                                         Amount
         Annual benefits from the project                                $300,000        $150,000     $240,000     $300,000     $300,000     $300,000
         Annual expenses for the project                                     $50,000      $25,000      $40,000      $50,000      $50,000       $50,000
          Impact on annual cash flow = Annual benefits – Annual            $250,000      $125,000     $200,000     $250,000     $250,000     $250,000
                                                          expenses
                             Total onetime startup investments           $500,000
                                      IRR (5 years)            28%      -$500,000      $125,000     $200,000     $250,000     $250,000      $250,000
                                      Payback period (          2.7     Cumulative     -$375,000   -$175,000      $75,000     $325,000      $575,000
                                                                        Cash Flow
         NPV discount ra   5.0%       NPV (5 years)       $417,970
         Payback Period1
         The payback period of a given investment or project is an important determinant of whether to undertake a project. Longer payback periods are 
         typically not desirable. The payback period is the time it takes to recover (i.e., pay back) the cash invested in a project. If it takes three years for 
         the annual cash flows generated by a project to earn back the initial startup costs, the payback period is three years. Note that some companies 
         may prefer expressing the payback period in months rather that years. This approach requires a more detailed monthly cashflow rather than the 
         annual cashflow used in the examples in this explanation. The logic of the following formula would work, though, with adjustments made to 
         include the greater number of time periods in the cash flow. The rest of this explanation assumes that an annual cash flow is used.
         In Example 1, above, the initial capital investment is $500,000 and annual cash flows received from the project in Years 1, 2, 3, 4 and 5 are 
         shown in cells H10 to L10. The cumulative cash flows in cells H13 to L13 show how the annual cash flows gradually pay back the initial 
         investment. Since payback period calculations usually don’t consider the time value of money, a quick look at the cumulative cash flow row tells 
         us that the payback period is between two to three years, which is when the cumulative cash flow exceeds the initial investment and becomes 
         positive. The challenge is for Excel to figure this out without human prompting — that is, for Excel to figure out the payback period as if it were 
         blindfolded. The generic formula for payback period is:
             Payback Period = Number of years with a negative cumulative cash flow 
                                          + Fraction of the next year when the cumulative cash flow goes from negative to positive
                                      = Count of years with a negative cumulative cash flow 
                                          + (Absolute value of the last negative cumulative cash flow) ÷ (Cash flow in the year of first positive 
         cumulative cash flow)
                                                                                    1
         The one-cell formula for payback period was built on existing guidance  and enhanced to cover possible error conditions that might arise. The 
         explanation for each factor in the formula is below.
     The "Count of years with a negative cumulative cash flow" is determined by using the COUNTIF function in Excel. It counts the number of 
     negative cumulative cash flow values in row 13 between Year 1 and Year 5.
           =COUNTIF(H13:L13,"<0")
     That is, the COUNTIF function searches the range of cells between H13 and L13 looking for how many values in those cells are negative (i.e. <0). 
     In our example, the count of negative values is 2, so the number of years is 2. 
     Now we need to add the fraction of the next year that it took to pay back the last of the initial investment with the cash flow for that year, 
     allowing the cumulative cash flow to become positive. 
     The fraction is expressed as:
        (Absolute value of the last negative cumulative cash flow) ÷ (Cash flow in the year of first positive cumulative cash flow)
     The value of the "Absolute value of the last negative cumulative cash flow" numerator is determined by the INDEX function in Excel. It 
     finds the last negative value in the cumulative cash flow row.
        ABS(INDEX(H13:L13,1,COUNTIF(H13:L13,"<0")))
     That is, the INDEX function references the range of cumulative cash flow cells between H13 and L13. It uses the COUNTIF function to find how 
     many columns in the sequence have negative values (i.e. 2). The INDEX function now knows that it should pull the value from the cell in the 
     second column in row 1 of the H13 to L13 sequence (i.e. from I13 which has a value of -$175,000). The fraction needs to be a positive number, 
     so the ABSolute value (i.e. $175,000) is used as the numerator in the fraction. 
     The value of the "Cash flow in the year of first positive cumulative cash flow" denominator is also determined by the INDEX function in 
     Excel. It finds the first positive value in the cumulative cash flow row and captures the value of the cash flow in that year that caused the 
     cumulative cash flow to turn positive.
           = INDEX(H12:L12,1,COUNTIF(H13:L13,"<0")+1)
     That is, the INDEX function references the range of annual cash flow cells between H12 and L12. It uses the COUNTIF function to find how many 
     columns in the cumulative cash flow sequence have negative values (i.e. 2) and adds 1 to that number to identify the next column in the 
     sequence (i.e., the third column). COUNTIF passes this information to INDEX which pulls the value from the cell in third column in row 1 of the 
     H12 to L12 sequence (i.e. from J12 which has a value of $250,000). Since it will always be a positive number, the ABSolute function is not 
     required. So now we have the denominator of the fraction (i.e., $250,000).
     To summarize so far:
        Payback Period = Number of years with a negative cumulative cash flow + Fraction of the year when the cumulative cash flow 
     goes from negative to positive
                                 = Count of years with a negative cumulative cash flow 
                                    + (Absolute value of the last negative cumulative cash flow) ÷ (Cash flow in the year of first positive 
     cumulative cash flow)  
                                 = (COUNTIF(H13:L13,"<0")
     +ABS(INDEX(H13:L13,1,COUNTIF(H13:L13,"<0")))/INDEX(H12:L12,1,COUNTIF(H13:L13,"<0")+1)
                                 = 2 + ($175,000 / $250,000) in Example 1
                                 = 2 + 0.7
                                 = 2.7
     This formula works well if the payback period is greater than one year. If the payback period is less than a year, the formula returns an error 
     code because the COUNTIF function can't find any negative cumulative cash flows to count. Even the first year has a positive cumulative cash 
     flow because the initial investment was entirely paid back during that year. This situation is illustrated in Example 2.