Year 0 1 2 3 4 5
-50000 15000 10000 5000 25000 16000
The above are cash flows. So in year zero, we buy a piece of machinery that
costs $50K. Years 1 thru 5 are cash inflows that we received from the
machine. What is the payback period?
So the first thing I need to do is discount each cash inflow. Then I need
to find out when the cumulative discounted cash flows are greater than the
initial cash outlay (year 0). The year in which this occurs is the payback
period. It would be extremely helpful if we can formulate this process in a
single cell. Is this possible? Thanks in advance!
Steve.
What you are referring to is the discounted payback period. The macro that
I provided previously will return the discounted payback if you feed it the
present values of the cash flows. There may be a non-vba way to do this,
but I'm not aware of it. If your cash flows were of the annuity variety you
could use the NPER function to find the discounted payback (or even regular
payback with a discount rate of 0). Uneven cash flows are much harder.
Tim
"Steve" <st...@hotmail.com> wrote in message
news:3c2b5b1e$0$19045$724e...@reader2.ash.ops.us.uu.net...
You could probably enhance the UDF to discount the cash flows
revise one line to
CumSum = CumSum + (Cashflows(c) / (1 + 0.08) ^ c)
Revise references as necessary and revise .08 8% to a reference
or revise to rate that you want to use.
Do you want the result to roundup?
Tim Mayes wrote in message ...
You are right. When I originally wrote that function I didn't want to have
another one specifically for the discounted payback. However, you could add
another argument to the function for the discount rate. Using a rate of 0
would give the regular payback and any other rate would return the
discounted payback. Thanks for giving me that idea. Here's the new
function which does both versions of payback:
Function FAME_Payback(Cashflows, Rate)
'Calculate the payback period if Rate = 0, discounted payback if Rate <> 0
'Note that the first cash flow must be negative
Dim PB As Single, UB As Integer
UB = Cashflows.Count 'Upper bound (i.e., number of cash flows)
CumSum = 0 'Cumulative sum of cash flows, stop when greater
than 0
i = 0 'Counter variable
If Rate >= 1 Then Rate = Rate / 100
Do While CumSum <= 0 And i < UB
i = i + 1
CumSum = CumSum + Cashflows(i) / (1 + Rate) ^ (i - 1)
Loop
If CumSum >= 0 Then
CumSum = CumSum - Cashflows(i) / (1 + Rate) ^ (i - 1)
PB = (i - 2) - CumSum / (Cashflows(i) / (1 + Rate) ^ (i - 1))
FAME_Payback = PB
Else: FAME_Payback = "Payback > Life" 'Report error
End If
End Function
Tim
"David G. Patton" <pat...@cadvision.com> wrote in message
news:OQ6sd2wjBHA.2436@tkmsftngp07...
Steve.
The User Defined Function (UDF) works like regular Excel Functions.
The necessary parameters are shown with the UDF.
FAME_Payback(Cashflows, Rate)
example Select a cell and enter the formula
=Fame_Payback2(A4:F4,0.08)
Note: Revise the references and rate to suit your data.
1. test with your data
2. review concepts of discounted cash flows and IRR.
See articles in the newgroup or
http://www.mvps.org/dmcritchie/excel/formula.htm#install
on installing UDFs or Sub routines.
Try the above and post if you require additional information.
Dave Patton
Steve Andruszka wrote in message <3C2BF06B...@mail.uscom.com>...
Make sure you get the revised version of the macro that I posted in response
to David. That said, once you copy the code to a module (you could even put
it in Personal.xls so that its always there when you need it) it will appear
in the Paste Function dialog under the User Defined category. Once its
installed it acts just like any other built-in Excel function (e.g. NPV).
There's no need to "run" it. Just enter the formula such as
=FAME_Payback(A5:A10).
Tim
"Steve Andruszka" <ste...@mail.uscom.com> wrote in message
news:3C2BF06B...@mail.uscom.com...
Steve.
"Tim Mayes" <may...@attbi.com> wrote in message
news:#6eQ731jBHA.2488@tkmsftngp05...
There are lots of books available, but the best I've seen "Microsoft Excel
2000 Power Programming with VBA" by John Walkenbach. I believe he's got a
newer version for Excel 2002. Otherwise, just writing UDFs is pretty simple
if you have a little bit of programming experience. You can learn a lot
just by reading this group and the programming group.
Tim
"Steve" <st...@hotmail.com> wrote in message
news:3c2c9cb9$0$24546$4c41...@reader1.ash.ops.us.uu.net...
Steve.
"Tim Mayes" <may...@attbi.com> wrote in message
news:OYHrCz8jBHA.2488@tkmsftngp05...