Payback UDF

This page shows you how to make a payback function in excel with at UDF.  With the UDF, you can simply enter a function on a series of cash flows and find the payback with percent of month or percent of year with =PAYBACK(cash flows).  Technical details demonstrate how to create the UDF function with VBA code by accumulating cash flow and then computing the percent of the period once the accumulated cash flow changes from negative to positive.

The payback function is heavily critiqued by business schools.  This is snobbish as so many real world decisions are still made by assessing how many years an investment takes to payback.  You can use Match and Index along with adding another line (or column) to find an integer that gives you an approximate payback.  But there is no detailed payback function or discounted payback function in excel, which is amazing.  The file below has a payback function.  You can also just copy the code below into your excel file.  Finally, there is a video that explains how to make a payback function.  The file that contains the payback functions is available for download below.

Excel File with User Defined Functions for Payback Period and Discounted Payback Period

 

Using the Payback UDF

Once you have created the payback UDF function, you can use the PAYBACK function and the DPAYBACK function as shown in the screenshot below. In the example, I have made a simple cash flow and you can demonstrate that if the cash flow after the outflow is 10, the payback period is 10 years.  The example shows that you can click on the entire line to get the payback period.  The discounted payback period counts how many period it takes to repay the cash flow when a discount rate is included for the cash flow.

 

Inserting the Payback Function into a Your Workbook

After you open the file that is available for download above you can do a few things to get the function into your sheet.  One is to press the button that will produce a user form.  Then, in the text that comes up, just select the VBA code and copy it to your sheet (as shown in the screenshot below the file).  Alternatively, you can press the ALT and F8 key sequence, edit the VBA code, press CNTL, C and then copy it to your file.

 

 

Once you have copied the VBA code (either from the userform, from pressing ALT, F8 and copying the entire sheet or from copying the code below), you can copy it into your file.  To do this, you can press ALT, F8 to get the VBA screen.  Then put in any name (e.g. Stormy).  Then go to the top of the file and copy the code (CNTL, V).  Some of this is demonstrated in the couple of  screenshots below.  The first screen shot illustrates how to create a new VBA page after you press ALT, F8; the second shows how you should insert a line to put the code at the top of the page; the third shows the results after you copy.

After making the new page with you blank macro named Stormy, go above the new macro and enter a couple of blank lines.  When you have entered the blank lines you will press the CNTL, V and copy the code to the top of the page.

 

 

After you have copied the code into your sheet, you should see OPTION BASE 1 at the top of the page.  Now you are ready to go and the function should work in your workbook.

 

 

 

Creating the Payback Function and Discounted Payback Function

The user defined functions for creating a payback and the discounted payback functions are presented below.  You can see the adjustment required so that an entire row can be used and you can see how the loop works with counting the number of cash flows.

.

Function payback(series)

Dim cum_series(1000), adj_series(1000) As Single ' dimesion of cumulative cash

Count = 0

For i = 1 To 1000 ' Adjustment for entire row
 If WorksheetFunction.IsNumber(series(i)) = True Then ' Only include numbers in new array
 Count = Count + 1
 adj_series(Count) = series(i)
 End If
Next i
 
tot_number = Count

counter = 0
For i = 1 To tot_number ' loop around cash flows
 
 If (i = 1) Then cum_series(i) = adj_series(i) ' cumulative cash flow
 
 counter = counter + 1 ' count if the cash is positive
 If (i > 1) Then
 cum_series(i) = cum_series(i - 1) + adj_series(i) ' cumulative cash flow
 End If
 
 If (cum_series(i) > 0) Then ' test when turns to positive
 GoTo finished:
 End If
Next i
num = i

finished: ' compute payback
If ((cum_series(i) - cum_series(i - 1) <> 0)) Then
 factor = -cum_series(i - 1) / (cum_series(i) - cum_series(i - 1)) ' Compute the factor to add to the payback for pct of yr
Else
 factor = 0
End If
If (i < series.Count) Then
 payback = factor + counter - 2
Else
 payback = num
End If

End Function

.

The function below is very similar, but it includes discounting the cash flow. This discounting can be adjusted if you do not want the initial outflow discounted (in this case you could raise the discount factor to the count minus 1).

.

Function dpayback(d_rate, series)

Dim cum_series(1000), adj_series(1000), dfactor(1000) As Single ' dimesion of cumulative cash

Count = 0

For i = 1 To 1000 ' Adjustment for entire row
 If WorksheetFunction.IsNumber(series(i)) = True Then ' Only include numbers in new array
 Count = Count + 1
 adj_series(Count) = series(i)
 End If
Next i
 
tot_number = Count

counter = 0
For i = 1 To tot_number ' loop around cash flows
 
 dfactor(i) = 1 / ((1 + d_rate) ^ counter)
 
 adj_series(i) = adj_series(i) * dfactor(i)
 
 If (i = 1) Then cum_series(i) = adj_series(i) ' cumulative cash flow
 
 counter = counter + 1 ' count if the cash is positive
 If (i > 1) Then
 cum_series(i) = cum_series(i - 1) + adj_series(i) ' cumulative cash flow
 End If
 
 If (cum_series(i) > 0) Then ' test when turns to positive
 GoTo finished:
 End If
Next i
i = num

finished: ' compute payback
If ((cum_series(i) - cum_series(i - 1) <> 0)) Then
 factor = -cum_series(i - 1) / (cum_series(i) - cum_series(i - 1)) ' Compute the factor to add to the payback for pct of yr
Else
 factor = 0
End If
If (i < series.Count) Then
 dpayback = factor + counter - 2
Else
 dpayback = num
End If

End Function

.

 

 

Video Describing How to Create Payback Functions

If all of the above explanation does not do enough, then you can watch a video on how to create a payback function.