+ Reply to Thread
Results 1 to 12 of 12

Standard Payback period with different starting dates of initial investmets

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    Riyadh
    MS-Off Ver
    Office 365 Subscription
    Posts
    47

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


    Thanks in advance
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

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

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

  3. #3
    Registered User
    Join Date
    06-03-2013
    Location
    Riyadh
    MS-Off Ver
    Office 365 Subscription
    Posts
    47

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

    The expected result is 6.76

  4. #4
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

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

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

  5. #5
    Registered User
    Join Date
    06-03-2013
    Location
    Riyadh
    MS-Off Ver
    Office 365 Subscription
    Posts
    47

    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. #6
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

    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))
    Last edited by AlanY; 08-31-2016 at 09:00 AM.

  7. #7
    Registered User
    Join Date
    06-03-2013
    Location
    Riyadh
    MS-Off Ver
    Office 365 Subscription
    Posts
    47

    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.
    Attached Files Attached Files
    Last edited by excobra; 09-01-2016 at 03:09 AM.

  8. #8
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

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

    Quote Originally Posted by excobra View Post
    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: copy to clipboard
    Please Login or Register  to view this content.


    Copy paste the same into cell B10
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  9. #9
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

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

    try this modified version

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    06-03-2013
    Location
    Riyadh
    MS-Off Ver
    Office 365 Subscription
    Posts
    47

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

    Quote Originally Posted by shukla.ankur281190 View Post
    Try

    B5
    Formula: copy to clipboard
    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. #11
    Registered User
    Join Date
    06-03-2013
    Location
    Riyadh
    MS-Off Ver
    Office 365 Subscription
    Posts
    47

    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. #12
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

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

    glad that worked out for you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to calculate Payback Period in Months
    By roofi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2017, 07:33 PM
  2. Payback period macro
    By lax2734 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-22-2014, 11:07 PM
  3. Help me with Cumulative formula and payback period VBA
    By anoy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-14-2014, 08:52 PM
  4. Replies: 1
    Last Post: 04-24-2013, 11:38 PM
  5. How to determine the payback period?
    By Parishboy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-15-2012, 04:55 PM
  6. Calculating Payback Period
    By deacs in forum Excel General
    Replies: 0
    Last Post: 09-23-2012, 06:56 PM
  7. payback period formula
    By deacs in forum Excel General
    Replies: 0
    Last Post: 05-24-2007, 01:02 PM

Tags for this Thread

Bookmarks

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