Excel

Simple Payback

Ease of Use

Easy

Version tested with

97,2000 

Submitted by:

chitosunday

Description:

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) 

Discussion:

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) 

Code:

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 215 times.

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