Closed Thread
Results 1 to 4 of 4

Payback period macro

  1. #1
    Registered User
    Join Date
    07-16-2012
    Location
    boston, ma
    MS-Off Ver
    Excel 2010
    Posts
    25

    Payback period macro

    Hi All,

    I am trying to figure out how I would assign a value to calculations within an array for payback period formula.
    For those unaware of the formula, essentially you have an initial investment followed by periodic cash flows which overtime hopefully offset your initial investment. Since there is no excel formula for this and I would like it to work as a macro, I have started this function which users input the initial investment followed by the range of cash inflows. What I am trying to do is subtract each of the cash flows from the initial (absolute) investment, for each cash flow that doesn't equal the initial, i would like to assign a value of 1 (year), then go onto the next value in the array and do the same until the cash inflows have equaled or just exceeded the initial investment.

    when it has exceeded the initial investment, then the formula would take the difference of that cash flow from the running total which would be ((initial investment-sum of cashflows)/ that cash flow)

    It gets sloppy at the end where I cannot figure out what I am doing wrong to get this, it needs to be some fraction of years

    Thank you in advance for your help

    Cheers....Lax2734
    Last edited by lax2734; 04-21-2014 at 10:38 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Payback period macro

    Hi Lax2734,

    It would be a great idea to post a sample workbook, helps us to try to assist you

    Jmac

  3. #3
    Registered User
    Join Date
    07-16-2012
    Location
    boston, ma
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Payback period macro

    JMAC, thank you. The project actually has changed so i no longer need assistance with this. thank you though!
    Attached Files Attached Files
    Last edited by lax2734; 04-22-2014 at 10:59 AM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Payback period macro

    Thread closed at OP's request
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. 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
  2. Replies: 1
    Last Post: 04-24-2013, 11:38 PM
  3. How to determine the payback period?
    By Parishboy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-15-2012, 04:55 PM
  4. Calculating Payback Period
    By deacs in forum Excel General
    Replies: 0
    Last Post: 09-23-2012, 06:56 PM
  5. payback period formula
    By deacs in forum Excel General
    Replies: 0
    Last Post: 05-24-2007, 01:02 PM

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