+ Reply to Thread
Results 1 to 3 of 3

Function that calculates payback period won't work

  1. #1
    Registered User
    Join Date
    01-12-2014
    Location
    Espaņa
    MS-Off Ver
    Excel 2007
    Posts
    6

    Function that calculates payback period won't work

    Hey guys, I've been struggling with a function that I'm writing to compute the payback period.

    The payback period is the amount of years it takes you to recover an investment or to pay back a loan. If the first year I invest 100 bucks and the next two years I get back 50 and 100, the payback period will be 1.5 years.

    This year I get -100 (I pay 100) -100 in profit
    Year 1 I get 50 -50 in profit
    Year 2 I get 100 50 in profit

    Therefore, it took me one year and the first half of the 2nd one (50 $ remaining / 100$ I get).

    My code is this:

    Please Login or Register  to view this content.
    I want it to check if carry + cvec(i) is greater/equal to 0. If it is I want the payback value to equal the next statement and then leave the function. If it's not, I want it to go to the next "i" and try again.

    For some reason, I get #value error in the cell where I input the formula.

    Thank you so much in advance.

  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: Function that calculates payback period won't work

    smoothopia

    for me, the code you included is at best marginally interesting, I work best if you post a spreadsheet (for preference in .xslm format so I can see what you have already tried to solve your problem) so we can try to assist you

    jmac

  3. #3
    Registered User
    Join Date
    01-12-2014
    Location
    Espaņa
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Function that calculates payback period won't work

    VBFns con funciones.xlsm

    Here's the workbook. It's in the last worksheet. Column B shows each year's cash flow. Column C lists the accumulated cash flows (The variable I called "carry"). My intention is to find out how long it takes us to break even. We count full years and the portion of the current year necessary to break even.

    Hope this gives you a hint.

    Thank you so much for your help, I'm learning but slowly :P

+ 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. Replies: 1
    Last Post: 04-24-2013, 11:38 PM
  2. How to determine the payback period?
    By Parishboy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-15-2012, 04:55 PM
  3. Vba Script That Calculates The Payback Period - Excel
    By FSJ in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-24-2012, 02:05 AM
  4. payback period formula
    By deacs in forum Excel General
    Replies: 0
    Last Post: 05-24-2007, 01:02 PM
  5. How to create data tables and is there a payback period function
    By Austen T in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-18-2006, 12:10 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