Excel

Simple Payback

Ease of Use

Easy

Version tested with

97,2000

Submitted by:

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:

```			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.

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:

Approved by mdmackillop

This entry has been viewed 234 times.

Copyright @2004 - 2014 VBA Express