VBA Express : Excel - UDF for Simple Payback, expressed in Years and Months

Excel

UDF for Simple Payback, expressed in Years and Months

Ease of Use

Easy

Version tested with

97, 2k

Submitted by:

dcardno

Description:

Text description of simple payback, in the form "2 years, 3 months"

Discussion:

Expresses simple payback (rough estimate of profitability of an investment) in years and months. This UDF was developed for people who did not like a payback calculation that resulted in "1.568" years, and were asking "how LONG is that?" Function requires either annual cash flows or cumulatve cash flows at annual intervals, with positive values representing investments (or losses) and negative values representing profits. Selection of either option is by an optional argument to the function

Code:

instructions for use

			Function Crossover(Values As Range, Optional blAnn As Boolean = True) As String 
    Dim dValArray() As Double 
    Dim dMo        As Double 
    Dim iMo        As Integer 
    Dim i          As Integer 
     
    ReDim dValArray(0 To Values.Columns.Count - 1) 
     
     
     ' ValArray takes cumulative invested amounts from initial investment dValArray(0) to
     ' end of term in dValArray(n), with positive investments (cash out) and negative
     ' returns (cash in).  We calculate a cross-over point when cumulative investment
     ' turns from positive to negative.  We test for the common cases where payback is
     ' from 1 to n years, and then for a no-net-investment case
     ' June 2004
     
     ' added optional blAnn argument; sets values to represent
     ' either Annual cash flows (if TRUE), or Cumulative cash flows (if FALSE).
     ' Default value is TRUE, as annual cash flows are more common
     ' Updated Dec 2004
     
    If blAnn = False Then 
        For i = 0 To UBound(dValArray) 
            dValArray(i) = [Values].Cells(i + 1) 
        Next 
    Else 
        dValArray(0) = [Values].Cells(1) 
        For i = 1 To UBound(dValArray) 
            dValArray(i) = dValArray(i - 1) + [Values].Cells(i + 1) 
        Next 
    End If 
    For i = 1 To UBound(dValArray) 
        If dValArray(i) <= 0 Then 
            dMo = 12 * (dValArray(i - 1) / (dValArray(i - 1) - dValArray(i))) 
            iMo = WorksheetFunction.RoundDown(dMo, 0) 
            If iMo <> 12 Then 
                Crossover = CStr(i - 1) & " years, " & CStr(iMo) & " months" 
            Else 
                Crossover = CStr(i) & " years" 
            End If 
            Exit For 
        End If 
        Crossover = "Project does not Repay Investment" 
    Next 
    If dValArray(0) <= 0 Then 'if no net investment, then payback is instant
        Crossover = "No Net Investment - Immediate Payback!" 
    End If 
     
End Function

How to use:

In the workbook where you want to use the function, open the VBE either from the toolbar (Tools | Macros | Visual Basic Editor) or by typing ctrl-F11
Copy the code, as is, into the VBE editing window
Close the VBE
In a worksheet enter the function:
=crossover([range reference],TRUE/FALSE), where the range reference is the ROW of cells that contain your cash flow values, including the initial invetment in the first cell. Positive amounts represent cash invested, negative amounts represent cash profits. If your range represents annual cash flows you can ignore the "TRUE/FALSE" entry (or enter "TRUE," if you like) - if your range represents cumulative cash flows, enter "FALSE" (in both cases, without the quotation marks).

Test the code:

The function should return a value like "4 years, 6 months" - simple calculation should indicate whether that is a plausible result.
If the first number in the range is negative, the function assumes that there was no net investment, and returns a string to indicate that.
If the cumulative cash flows are positive then the project has not paid for itself over the term you have included in the range, and the function will return a string to indicate that, as well. You might have to extend the range you include (or conclude that the investment is not very attractive...)

Sample File:

Crossover.zip 10.16KB

Approved by mdmackillop

This entry has been viewed 82 times.