# Standard Payback period with different starting dates of initial investmets

1. ## Standard Payback period with different starting dates of initial investmets

Guys

I want a standard formula for payback period calculation even if the the project is delayed and initial outflows occur in year 2 instead of year 0.
I have attached the spreadsheet. Like for example from B2 to K2 are year 1 to 10. This is constant line and i cant change it

Project one was started in year 1 so we calculate the payback perido in b5.

Whereas project 2 was started in year 2 for which i want to calculate the payback period using the same formula which i used in B5. Its because my projects gets delayed quite often and i just want to copy paste formula in B5 regardless of when the project ws started. it should calculate correct payback period.

2. ## Re: Standard Payback period with different starting dates of initial investmets

what's the expected result for project 1 in B5?

3. ## Re: Standard Payback period with different starting dates of initial investmets

The expected result is 6.76

4. ## Re: Standard Payback period with different starting dates of initial investmets

now you got me! how's the 6.76 worked out?

5. ## Re: Standard Payback period with different starting dates of initial investmets

=IF(SUM(C3:J3)<-B3,"Never",LOOKUP(0,B4:J4,B2:J2-B4:J4/C3:J3))

6. ## Re: Standard Payback period with different starting dates of initial investmets

check if this mod works for you or not

=IF(SUM(B3:K3)<0,"Never",LOOKUP(0,B4:K4,\$B\$2:\$K\$2-B4:K4/C3:K3))

7. ## Re: Standard Payback period with different starting dates of initial investmets

Thanks for the help.

It works fine as long as the project starts in year 1. If cash flows starts in year 2 it inflates the payback period by the number of years project delayed for example in the attached example it gives the payback period 7.76 if i delayed the cash-flows by one year which is wrong. The correct answer is still 6.76
What i am trying to say is the formula should ignore the no of years before actual project starts while calculating payback period.

I have attached the updated file with example.

Thanks

Update- It worked i have updated the
``Please Login or Register  to view this content.``

8. ## Re: Standard Payback period with different starting dates of initial investmets

Originally Posted by excobra
Thanks for the help.

It works fine as long as the project starts in year 1. If cash flows starts in year 2 it inflates the payback period by the number of years project delayed for example in the attached example it gives the payback period 7.76 if i delayed the cash-flows by one year which is wrong. The correct answer is still 6.76
What i am trying to say is the formula should ignore the no of years before actual project starts while calculating payback period.

I have attached the updated file with example.

Thanks
Try

B5
Formula:
`Please Login or Register  to view this content.`

Copy paste the same into cell B10

9. ## Re: Standard Payback period with different starting dates of initial investmets

try this modified version

``Please Login or Register  to view this content.``

10. ## Re: Standard Payback period with different starting dates of initial investmets

Originally Posted by shukla.ankur281190
Try

B5
Formula:
`Please Login or Register  to view this content.`

Copy paste the same into cell B10
Bro it doesn't work correctly. Thanks for the effort though

11. ## Re: Standard Payback period with different starting dates of initial investmets

Alan, this does work but it isnt robust enough for example if cash flows are delayed more than one year it breaks . I used your original forumla and modified it which seems to work in every scenario i have.
``Please Login or Register  to view this content.``
Thanks for all the help

12. ## Re: Standard Payback period with different starting dates of initial investmets

glad that worked out for you

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1