Different approach - A Payback formula

[Steve]

Hello everyone. I was hoping you could help me formulate a cell. Here is
the scenario:

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.

[Tim Mayes]

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

[David G. Patton]

Hello Tim and Steve

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

[Tim Mayes]

David,

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 Andruszka]

Hello Tim and David,
Tim - Thanks for your response this afternoon. I posted the second question
prior to receiving your response (I thought I'd try to clarify my ambiguity a
bit!) Anyway, thanks for the macro!
David - Thanks for modifying Tim's macro to better suit my needs!
I have one follow up question for both of you....If I understand you correctly,
the macro you wrote creates a User Defined Function?! I guess what I am trying
to understand is, when I want the macro to run and calculate the payback period,
do I have to manually run the macro, or does the macro create a function within
a cell that automatically calc's the payback?
Thanks for the clarification guys! And thanks for your help!!

Steve.

[David G. Patton]

Hello 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>...

[Tim Mayes]

Steve,

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]

Thanks guys! I really appreciate it! One more final follow up question not
specifically related to the payback macro - Do you guys know where I can
find some info regarding writing UDF's?? Thanks again for all your help!

Steve.

"Tim Mayes" <may...@attbi.com> wrote in message
news:#6eQ731jBHA.2488@tkmsftngp05...

[Tim Mayes]

Steve,

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]

Thanks Tim. Happy Holidays!

Steve.

"Tim Mayes" <may...@attbi.com> wrote in message

news:OYHrCz8jBHA.2488@tkmsftngp05...