Results 1 to 10 of 10

Payback Period Calculation

This is a discussion on Payback Period Calculation within the Excel Questions forums, part of the Question Forums category; Is there a formula in excel that will calculate the exact payback period for an investment, and a series of ...

  1. #1
    Board Regular
    Join Date
    Jun 2004
    Posts
    158

    Default Payback Period Calculation

    Is there a formula in excel that will calculate the exact payback period for an investment, and a series of cash flows, for example:

    Year 0: -275,000 (initial investment)
    Year 1: 125,000
    Year 2: 125,000
    Year 3: 145,000
    Year 4: 145,000

    I know I will get the payback in year 3, but would like a more exact figure like 3.17 and/or in years and weeks/days...

    Appreciate your help! db

  2. #2
    Board Regular mortgageman's Avatar
    Join Date
    Jun 2005
    Posts
    2,015

    Default Re: Payback Period Calculation

    Quote Originally Posted by dentonblake View Post
    Is there a formula in excel that will calculate the exact payback period for an investment, and a series of cash flows, for example:

    Year 0: -275,000 (initial investment)
    Year 1: 125,000
    Year 2: 125,000
    Year 3: 145,000
    Year 4: 145,000

    I know I will get the payback in year 3, but would like a more exact figure like 3.17 and/or in years and weeks/days...

    Appreciate your help! db
    Do you know the month that each cashflow happens?
    Also, is there a rate somewhere here?

    Gene Klein
    Gene, "The Mortgage Man", Klein
    See all my Mishegas Videos
    NEW: MY INTERNET RADIO SHOW:
    http://www.blogtalkradio.com/mortgageman

  3. #3
    Board Regular
    Join Date
    Jun 2004
    Posts
    158

    Default Re: Payback Period Calculation

    No rate required, just looking for "simple" payback period -- e.g., not seeking NPV, IRR, etc... just how long it's taking me to recover the initial investment.

    Each year is comprised of 12 equal payments at the beg. of each month to arrive at the year end total outlined in the original post. (example: rent payments).

    Thank you, db

  4. #4
    Board Regular mortgageman's Avatar
    Join Date
    Jun 2005
    Posts
    2,015

    Default Re: Payback Period Calculation

    Quote Originally Posted by dentonblake View Post
    No rate required, just looking for "simple" payback period -- e.g., not seeking NPV, IRR, etc... just how long it's taking me to recover the initial investment.

    Each year is comprised of 12 equal payments at the beg. of each month to arrive at the year end total outlined in the original post. (example: rent payments).

    Thank you, db
    I can do this with "helper columns" - not a formula though
    The first column would be a running total (using sum($a$2..a2) copied down). The second column would be a "boolean" check to see if the rt is > then the absolute value of the investment (which I assume happens one month before the rent starts?) in cell a1. The first TRUE tells you the month.

    I'm sure some of the formula wizards here can do better - but at least that can get you started.

    Gene Klein
    Gene, "The Mortgage Man", Klein
    See all my Mishegas Videos
    NEW: MY INTERNET RADIO SHOW:
    http://www.blogtalkradio.com/mortgageman

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    2,714

    Default Re: Payback Period Calculation

    With your data in A1:B5
    C1 is blank

    In C2, enter the following and then copy or fill it down.

    =(SUM($B$1:B2)>0)*(C1=0)*(ROW()-2-SUM($B$1:B1)/B2)

  6. #6
    New Member
    Join Date
    Nov 2012
    Posts
    1

    Default Re: Payback Period Calculation

    Hi db,

    You can do that using COUNTIF and HLOOKUP functions. Here's how you do that:

    Step 1: Open a new spreadsheet
    Step 2: Enter the initial investment of 275000 in cell B1
    Step 3: Enter Year numbers 1 to 4 in cells B3 to E3
    Step 4: Enter the annual cash inflows 125k, 145k, 145k & 145k through years 1 to 4 in cells B4 to E4
    Step 5: Calculate the cumulative cash inflows through years 1 to 4 in cells B5 to E5. The cumulative cash inflows would be 125k, 290k, 395k & 540k in the respective years.
    Step 5: Enter (or copy and paste) the formula =COUNTIF(B5:E5,"<"&B1) in cell B7. This will return the value 2, indicating the year component of the payback period (not including Year 0, the year in which the initial investment was made)
    Step 6: Enter (or copy & paste) the following formula in cell D7:
    =(B1-HLOOKUP(COUNTIF(B5:E5,"<"&B1),B3:E5,3,0))/(HLOOKUP(COUNTIF(B5:E5,"<"&B1)+1,B3:E5,2,0))*12
    This will return the value 2.07, indicating the fraction of the 3rd year in months
    The payback period will be computed as 2 years & 2.07 months or 2.17 years (not including year 0). If you were to include year 0 then it would be 3.17 years. To get to this value, modify the formula in cell B7 to =COUNTIF(B5:E5,"<"&B1)+1


    Hope this helps.







    Quote Originally Posted by dentonblake View Post
    Is there a formula in excel that will calculate the exact payback period for an investment, and a series of cash flows, for example:

    Year 0: -275,000 (initial investment)
    Year 1: 125,000
    Year 2: 125,000
    Year 3: 145,000
    Year 4: 145,000

    I know I will get the payback in year 3, but would like a more exact figure like 3.17 and/or in years and weeks/days...

    Appreciate your help! db

  7. #7
    Board Regular
    Join Date
    Jun 2011
    Posts
    57

    Default Re: Payback Period Calculation

    Quote Originally Posted by murali_subramanian View Post
    Hi db,

    You can do that using COUNTIF and HLOOKUP functions. Here's how you do that:

    Step 1: Open a new spreadsheet
    Step 2: Enter the initial investment of 275000 in cell B1
    Step 3: Enter Year numbers 1 to 4 in cells B3 to E3
    Step 4: Enter the annual cash inflows 125k, 145k, 145k & 145k through years 1 to 4 in cells B4 to E4
    Step 5: Calculate the cumulative cash inflows through years 1 to 4 in cells B5 to E5. The cumulative cash inflows would be 125k, 290k, 395k & 540k in the respective years.
    Step 5: Enter (or copy and paste) the formula =COUNTIF(B5:E5,"<"&B1) in cell B7. This will return the value 2, indicating the year component of the payback period (not including Year 0, the year in which the initial investment was made)
    Step 6: Enter (or copy & paste) the following formula in cell D7:
    =(B1-HLOOKUP(COUNTIF(B5:E5,"<"&B1),B3:E5,3,0))/(HLOOKUP(COUNTIF(B5:E5,"<"&B1)+1,B3:E5,2,0))*12
    This will return the value 2.07, indicating the fraction of the 3rd year in months
    The payback period will be computed as 2 years & 2.07 months or 2.17 years (not including year 0). If you were to include year 0 then it would be 3.17 years. To get to this value, modify the formula in cell B7 to =COUNTIF(B5:E5,"<"&B1)+1


    Hope this helps.
    Sorry to bring up an old thread, but I thought it would be easier than creating a new one.

    I'm using a slight adaptation of the above, but keeping the same formulas. I have my initial investment, then the 'saving' over each year (rather than cash inflows), but I'm finding that when the saving is made in the first year, e.g., after 0 years and 5 months I get an error in D7 as the months are 0.00 also.

    Is there a way around this. I also have a text formula that reads:

    ="Initial investment of "&TEXT(B1,"0,000")&" paid back after "&PaybackCalculation!B6&" year(s) and "&TEXT(PaybackCalculation!C6,0)&" months."

    Ideally where payback is in the first year I'd like it to say 0 years and 2 months.
    Last edited by adamash; Feb 16th, 2016 at 12:09 PM.

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    2,714

    Default Re: Payback Period Calculation

    Hello Adamash

    Try converting suggested solution to months instead of years. The answer would be in months and fraction of month.
    You could convert the months to the format that you require.

    You could also post a sample of your data with the result that you require.
    Please ensure that your post can be extracted by people interested in providing a solution.

    Since your challenge may be quite different, you could start a new thread.

  9. #9
    Board Regular
    Join Date
    Jun 2011
    Posts
    57

    Default Re: Payback Period Calculation

    Quote Originally Posted by Dave Patton View Post
    Hello Adamash

    Try converting suggested solution to months instead of years. The answer would be in months and fraction of month.
    You could convert the months to the format that you require.

    You could also post a sample of your data with the result that you require.
    Please ensure that your post can be extracted by people interested in providing a solution.

    Since your challenge may be quite different, you could start a new thread.
    Hi Dave, thank you for the reply. I'm not sure how I'd convert the suggested solution to months. I use this to calculate the months as suggested above:

    =(B1-HLOOKUP(COUNTIF(B5:E5,"<"&B1),B3:E5,3,0))/(HLOOKUP(COUNTIF(B5:E5,"<"&B1)+1,B3:E5,2,0))*12

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    2,714

    Default Re: Payback Period Calculation

    See post #5

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com