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.
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.
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.
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.
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.
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.
Originally Posted by shg
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.
This should at least get you off and running:
Please Login or Register to view this content.
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!
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:What are you supposed to be learning from this exercise?Please Login or Register to view this content.
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.
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.
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)?
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.
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.What do you guys think is the best way to learn VBA from scratch (VBA for dummies)?
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks