Financial Function

  • Thread starter Thread starter gilbert
  • Start date Start date
G

gilbert

Hi,

It's me again.....the freshie

I know in Excel, we hv formulae to calculate NPV (net present value
and IRR (Internal Rate of Return)....but is there any formulae t
calculate Payback Period? That is, the number of years or months t
recover back out invested money given a series of subsequent in flows


Please help
 
Hi Gilbert!

For NPV the easiest way would be to set up a parallel column that
tracks the sum of the payments.

I have a flow in A1:A15 and an NPV in A18
In B1 I have =A1
In B2:
=IF(OR(B1="",B1="Paid Back"),"",IF(SUM($A$1:A2)>$A$18,"Paid
Back",SUM($A$1:A2)))
Copied down to B15

That gives me a running aggregate of payments until that aggregate
exceed the NPV.

I can use conditional formatting to hide the output other than "Paid
Back"

I could use a similar approach to IRR only this time I track the
aggregate of payments equalling 0. Regular investments assumed here.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Gilbert,

Norman Harker has provided one approach, and I will provide a slightly
different approach. I interpreted the question slightly differently than
did Norman. I am assuming you want "simple" payback where the cumulative
cash flow is positive. I am not making any comparison to NPV values, though
it would be easy to modify to incorporate that aspect. You can review our
solutions and let us know we have addressed your question.

A1:A50 are your cash flows.

B1:B50 are your cumulative cash flows. (B1=A1; B2=B1+A2; B3=B2+A3 and so
on)

C1: =MATCH(0,B1:B50,1)+1

The plus ONE at the end might be included or excluded, depending on whether
your first cash flow occurs at time 0 or at end of period 1.

This solution assumes a simple cash expenditure and then positive cash flow
thereafter.

Hope this information is helpful.

Best regards,
Kevin
 
Well, thanks for the 2 different approaches in getting the payback
period...but can it be done if I need the payback period to be defined
into years and months assuming the cash inflow in proportionate in a
year? Using Kevin approach, it shows only years....

Please tell me how if there is a way to do that.
 
Hi Gilbert!

With annual income in A1 and Value in A2:

=INT(A2/A1)&" years "&ROUNDUP(MOD(A2/A1,1)*12,0)&" months."
Returns payback period in years and months rounded up to the nearest
month.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top