+ Reply to Thread
Results 1 to 14 of 14

Stuck: Need help with original vba code for irr & payback function

  1. #1
    Registered User
    Join Date
    04-24-2014
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Stuck: Need help with original vba code for irr & payback function

    Hi,

    I am new to the site but I thought i'd give it a try to see if I can get some help. I have a project where we need to write vba code for an IRR and Payback function. Can anyone help as I am new to this.

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Stuck: Need help with original vba code for irr & payback function

    Unless I am missing something the only question in your post was "Can anyone help". Yes, most people can help. Now the question becomes to what degree do you need help. If you have specific questions about specific operations you are trying to perform you have come to the right place (We just need to know what they are). If you are looking for the creation of an entire workbook I would suggest you consult the proffesional services (for a fee) section of this forum.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Stuck: Need help with original vba code for irr & payback function

    Hi,

    Why is your first port of call a VBA macro when there is a standard IRR function?
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    04-24-2014
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Stuck: Need help with original vba code for irr & payback function

    Hi,

    I apologize for the vague question. I am in a financial modeling class where we need to write our own IRR VBA for a project. We have had very minimal class time to learn VBA so my knowledge is very minimal.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Stuck: Need help with original vba code for irr & payback function

    I find that I first need to understand the math before I can worry about programming something like this. Since this is for a financial modeling class, can I assume you understand the mathematical function(s) behind an IRR function? Since many of us are not knowledgeable about financial modeling, a brief math lesson would help us help you.

    From what I know of these kind of functions, they tend to require iterative numerical methods to solve. Are you familiar with basic root finding algorithms like the Newton-Raphson method or the bisection method? http://en.wikipedia.org/wiki/Root-finding_algorithm

    How much of your question is specifically about VBA ("I know how to write this in C, but I'm not familiar with VBA's syntax for the same code block") and how much of your question is more general ("How does one code an IRR type function in any language?")

    As this is homework, we don't want to short-circuit your education by doing it for you, but we would be happy to coach you through the parts you are not understanding. For this to work well, show us what you have done, what is working, and specifically what you are having trouble understanding.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Stuck: Need help with original vba code for irr & payback function

    I agree with Richard. I don't think you need to use VBA at all. Do a google search on Excel IRR. There are a lot of step by step instructions.

  7. #7
    Registered User
    Join Date
    09-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: Stuck: Need help with original vba code for irr & payback function

    This should at least get you off and running:

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    04-24-2014
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Stuck: Need help with original vba code for irr & payback function

    Thanks for reaching out. Basically, IRR is an interest rate that will bring cash flows to an Net Present Value of zero. My question is how specifically can you program this function into VBA without sourcing the actual IRR excel function. I'll be completely honest I have only gotten as far as to dim some of the variables(which im not sure is right.)

    The example can be used as such. Given cash flows
    -100
    10
    30
    50
    80

    we are tasked with writing an original code (for a project) without using excel's IRR function.

    I found this on an additional site but it seems very complex.

    Code:
    Function MyIRR(Rng As Range)
    Dim Dn As Range, Rng2 As Range
    Dim x, P, Rs As Double
    Set Rng2 = Rng.Offset(1).Resize(Rng.Count - 1)
    x = -1
    Do Until Rs <> 0 And Rs > 0 - (Rng(Rng.Count) * 0.05) And Rs < 0 + (Rng(Rng.Count) * 1.01)
    P = Abs(Rng(1))
    Rs = 0
    x = x + 0.01
    For Each Dn In Rng2
    Rs = (P * (1 + x)) - Dn
    P = Rs
    Next Dn
    P = 0
    Loop
    x = Format(x, "0.00%")
    MyIRR = x
    End Function

    If it was up to me I would obviously just use excel's IRR but we are not allowed to reference it in our code.

    Thanks!

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Stuck: Need help with original vba code for irr & payback function

    This forum is pretty strict about putting code inside of code tags (see rule 3 of the forum rules link at the top of the page for help in using code tags).

    Observation: if you are not allowed to use Excel's IRR function, what is substantially different about getting a block of code from the internet?

    I don't know if this 18 lines of code is really complex. It looks like someone is using a basic (but inefficient) "brute force" type algorithm to find the value for x that makes Rs fit the desired convergence criteria. A quick look through the code:
    Please Login or Register  to view this content.
    What are you supposed to be learning from this exercise?

  10. #10
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Stuck: Need help with original vba code for irr & payback function

    Quote Originally Posted by MrShorty View Post

    What are you supposed to be learning from this exercise?
    Must admit that had crossed my mind. At face value it seems a somewhat unusual Financial Modelling class that is at the same time expecting students to come to grips with a completely different discipline - to wit VBA.

    Perhaps the OP will comment and satisfy our curiosity.

  11. #11
    Registered User
    Join Date
    09-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: Stuck: Need help with original vba code for irr & payback function

    Disclaimer: This is likely not a very efficient way of doing things, but it's one way of getting to the solution. It's not a UDF but it gives the OP an idea of how to go about creating something.

    Please Login or Register  to view this content.
    Last edited by Telperion; 04-25-2014 at 01:24 PM.

  12. #12
    Registered User
    Join Date
    04-24-2014
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Stuck: Need help with original vba code for irr & payback function

    Hey I really appreciate all the feedback. I agree it is odd he has sprung VBA on us after a semester full of modeling financial statements. The professor has a technology background and loves VBA but during the class it is difficult to follow given his extensive knowledge of programming. Telperion thank you for the code I will look at it and attempt to make some sense of it . What do you guys think is the best way to learn VBA from scratch (VBA for dummies)?

  13. #13
    Registered User
    Join Date
    09-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: Stuck: Need help with original vba code for irr & payback function

    I added comments to all the code, paste it into the VBA editor in Excel and it'll be easier to read with color and syntax formatting.

    I'm no expert, but I do a lot of the simple stuff. The easiest thing for me was to learn the basics (variables, arrays, loops, find, ranges, etc) in small pieces then incorporate them into larger code. Lots of Googling.

  14. #14
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Stuck: Need help with original vba code for irr & payback function

    What do you guys think is the best way to learn VBA from scratch (VBA for dummies)?
    I still think it somewhat depends on what you are expected to learn. I would half expect most "intro to VBA" courses to focus early on on VBA's object model, which will be important, but I think, secondary. For a financial modeling class, computational algorithms might be more important. For example, I notice that Telperion's code, as well as the sample you got from the internet, are both using an inefficient "brute force" kind of algorithm. Which is fine if the instructor does not care what kind of algorithm you use. I work in the physical sciences, so I really don't know what is expected for a "financial modeling" class. That said, part of me would expect that part of learning these things would be to learn different numerical methods (including but not limited to brute force type methods -- bisection and/or Newton Raphson type methods as well) for solving such a problem and be able to talk about the advantages and disadvantages of each algorithm for the given problem. I don't know -- maybe I expect too much.

    As I see it, so much depends on what your instructor/course material is expecting you to learn. I would echo what Telperion suggested, learn some of the basics (variables, arrays, loops), figure out what kinds of algorithms you are expected to learn, then work on applying those basic principles to those algorithms.

+ 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. Function that calculates payback period won't work
    By smoothopia in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2014, 03:19 PM
  2. Stuck at work and stuck on a count function
    By gregfetzer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-24-2012, 12:47 AM
  3. [SOLVED] Payback function/formula with a growth rate
    By jetablack4 in forum Excel General
    Replies: 19
    Last Post: 10-15-2012, 10:31 AM
  4. Reflections Code is Stuck!
    By erlnmo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-26-2009, 05:50 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