I wrote this Excel function as I sometimes use payback period in my work. I am a novice in Visual Basic, my knowledge entirely gained from Walkenbach’s book. Is there a more elegant & shorter way to write this, and could there be situations it doesn’t work properly?
Usage in Excel : =spayback(range)
Option Explicit
Function SPayback(InputRange As Range) As Double Dim rng As Range, Counter As Integer Dim Total As Double, TotalPlus As Double, TotalMinus As Double Counter = -1 If InputRange(1) >= 0 Then GoTo ErrorTrap 'No initial outlay For Each rng In InputRange Total = Total + rng.Value If Total >= 0 Then TotalPlus = Total TotalMinus = Total - rng.Value SPayback = Counter - TotalMinus / (TotalPlus - TotalMinus) Exit Function End If Counter = Counter + 1 Next 'Error Payback period exceeds investment horizon ErrorTrap: SPayback = CVErr(xlErrNA) End Function
I also did one for discounted payback, with an additional input for discount rate. It is the same, except that
Total = Total + rng.Value / (1 + Disc) ^ (Counter + 1)
I think your code is pretty good for someone who considers themselves a novice. There aren’t huge inneficiencies that really jump out at me as far as I can tell.
My only criticism would be clarity of code and commenting. When you look at your code in a month it won’t make any sense to you. Better to write it clearly now, you’ll thank yourself later when you try to ammend it.
I also personally would have stripped out the initial outlay from the range (i.e. dInitialOutlay, rngCashFlows). Also would avoid the confusion of having to assign -1 as an initial counter, it’s confusing.
Public Function CalculatePaybackPeriod(rngCashFlows As Range) As Variant Dim dPayBackPeriod As Double: dPayBackPeriod = -1 'set to -1 to account for outlay cash flow Dim dCashFlowSum As Double: dCashFlowSum = 0 Dim rng As Range Dim dOutstandingPayBack As Double If rngCashFlows(1, 1).Value >= 0 Then 'initial is positive, no payback period can be calculated CalculatePaybackPeriod = “Error: Initial Outlay is Positive” Else For Each rng In rngCashFlows dCashFlowSum = dCashFlowSum + rng.Value If dCashFlowSum >= 0 Then 'We have reached payback point, need to calculat residual dPayBackPeriod = dPayBackPeriod + (dOutstandingPayBack / rng.Value) 'no need to look at other cash flows, get out of here Exit For Else 'keep track of how much payback is left dOutstandingPayBack = Abs(dCashFlowSum) End If dPayBackPeriod = dPayBackPeriod + 1 Next rng CalculatePaybackPeriod = dPayBackPeriod End If End Function
Thanks Clever! You did that in record time. For me, Visual Basic coding is a painstaking process requiring frequent references to Walkenbach’s book.
Your advice on clarity of code & comments is noted - in a month’s time, I wouldn’t have a clue on the difference between Total, TotalPlus, TotalMinus, etc.
Will have a go at stripping out the initial outlay from the range as you suggest. Thanks again!