Simple Payback

Ease of Use


Version tested with


Submitted by:



Determine the no. period it takes when future cash inflows (without interest) equals investment. Use by accountants and industrial engineers as one of the tools to determine ROI (return of investment) 


Suppose your investment is 100, your future cash inflow is 50 for year 1, 52 for year 2 then your payback should be 1.96 (1 and 50/52) 


instructions for use


Put this code In a module Function PAYBACK(invest, finflow) Dim x As Double, v As Double Dim c As Integer, i As Integer x = Abs(invest) i = 1 c = finflow.Count Do x = x - v v = finflow.Cells(i).Value If x = v Then PAYBACK = i Exit Function ElseIf x < v Then P = i - 1 Z = x / v PAYBACK = P + Z Exit Function End If i = i + 1 Loop Until i > c PAYBACK = "no payback" End Function

How to use:

  1. Function formula in excel sheet.
  2. =payback(your investment amount,range of your future cash inflows)

Test the code:

  1. Payback amount should always be equal to no. of periods it will take to equal investment without effect of interest or inflation.

Sample File:

PAYBACK.zip 9.6KB 

Approved by mdmackillop

This entry has been viewed 234 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express