PayBack macro sub or function ?

  • Thread starter Thread starter Imbecill
  • Start date Start date
I

Imbecill

Does anybody have a sub to calculate PayBack when taking in concern NPV and
handle varable years of income?

Making a chart with a payback line is easy and we all se where it cut
through the "0" line, but how about get a PayBack cell giving like "2 years
4 month" (or at least 2,3 years) ??

as an example: 6% rate,
Year 1: -3000
Y2: 1000
Y3:1000
Y4: 2500
Y5:2000

Regards
 
Hi!

There are different interpretations of "Payback".

One is that it is when the sum of the positive cash flows exceeds the sum of
the negative cash flows.

The other is that it is when the running balance adjusted for interest
exceeds 0.

In the case of your data, the payback is the same.

I've set up a workbook as follows:

A2:A6
1 through to 5
B2:B6
Cash flows -3000, 1000, 1000, 2500, 2000
C2:
=D1*6%
Copied down to C6
D2:
=D1+B2+C2
Copied down to D6
E2:
=E1+B2
Copied down to E6

Irrespective of the definition of payback, you'll find that it arises at
period 4. I don't think it is correct to say it arises between periods 3 and
4 because in both cases exceeding 0 requires the payment made at period 4.

One way of returning the period number would be:

=MIN(IF(E2:E6>0,A2:A6,FALSE))
Entered as an array formula by pressing and holding down Ctrl and Shift keys
and then pressing Enter.
Returns 4.

This meets the first definition, which is the one I prefer. If you want the
second definition use:
=MIN(IF(D2:D6>0,A2:A6,FALSE))
Entered as an array formula by pressing and holding down Ctrl and Shift keys
and then pressing Enter.
Returns 4

--
 
Back
Top