How to sum when it exceeds limits and calculate the period (payback period)

Please help me to setup a formula to calculate Payback Period.

For example,
Investment ($1,000)
Year 1 $300
Year 2 $400
Year 3 $500
year 4 $600
Year 5
Year 6
Year 7
So the return period is 2+((1,000-700)/500) = 2.6 Years

I think I need to set a formula the SUM(Investment:Year 7) and it can automatically detect when to stop and calculate the period.

Please help if anyone knows.

Thank you.

Originally Posted by jihyeu

Please help me to setup a formula to calculate Payback Period.

For example,
Investment ($1,000)
Year 1 $300
Year 2 $400
Year 3 $500
year 4 $600
Year 5
Year 6
Year 7
So the return period is 2+((1,000-700)/500) = 2.6 Years

I think I need to set a formula the SUM(Investment:Year 7) and it can automatically detect when to stop and calculate the period.

Please help if anyone knows.

Thank you.

You would need to type in Excel payback period formula to find the answer to your question. tadPP is one of 95 different financial functions found in tadXL add-in. Most of the functions in this add-in are not found elsewhere. You will also find that it extends many of the existing function of Excel by offering many new features that are desired yet are not there in Excel's own financial functions

Using tadPP function, you will simply pass in the cash flows and by pressing the ENTER key you will get your payback period as illustrated below

Investment	0	-1000
Year	1	300
Year	2	400
Year	3	500
year	4	600
Payback Period		2.6
tadPP		=tadPP(C1:C5)
tadPP		tadPP({-1000,300,400,500,600})