VBA Express : Excel - Simple Payback

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:

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

Test the code:

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