Results 1 to 6 of 6

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
    154

    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
    154

    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,382

    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

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