Internet-Defense-League

2013-02-16

Some of Excel's Finance Functions in R

Last year I took a free online class on finance by Gautam Kaul. I recommend it, although there are other classes I can not compare it to. The instructor took great efforts in motivating the concepts, structuring the material, and enable critical thinking / intuition. I believe this is an advantage of video lectures over books. Textbooks often cover a broader area and are more subtle when it comes to recommendations.

One fun excercise to me was porting the classic excel functions FV, PV, NPV, PMT and IRR to R. Partly I used the PHP class by Enrique Garcia M. You can find the R code at pastebin. By looking at the source code, you will understand how sensitive IRR to its start value is:

> source("http://pastebin.com/raw.php?i=q7tyiEmM")
> irr(c(-100, 230, -132), start=0.14)
[1] 0.09999995
> irr(c(-100, 230, -132), start=0.16)
[1] 0.1999999

I still do not understand the sign of the return values. This I have to figure out every time I use the function. If you have a memory hook for this, please leave a comment.

The class did of course not only cover the time value of money, it was also a non-rigorous introduction to bonds and perpetuities (which I found interesting, too), as well as to CAPM and portfolio theory.

2 comments:

dinre said...

Perhaps the easiest way to remember the meaning of the sign of the internal rate of return is to remember the basic, numerical form of the solution:

Sum { Cashflow(Period) / (1 + IRR) ^ Period }
=
Initial investment

We are trying to find the one rate of return value that will make all of the periods' cash flows add up to the initial investment.

Since we are dividing by the rate of return, positive IRR is another way of saying that our cash flows are bigger than the initial investment and need to be adjusted down to solve the equation. Negative IRR is saying that the cash flows are smaller than the initial investment and need to be inflated.

The overly-simple way of thinking about it:
IRR is (loosely) the change in your account balance after you run a project. Negative means you will lose money, and positive means you will gain money.

I've never seen a negative IRR in real life, because those projects don't make it past initial project screenings. Usually, IRR is used as one measure to see if the opportunity cost of doing the project is outweighed by the return, so IRR is somewhat meaningless without understanding the opportunity cost. Often, the opportunity cost is your company's projected borrowing rate, and when borrowing costs more than your project's return, you don't do the project.

Hope that helps.

Karsten W. said...

Thanks, dinre. I find the negative sign in the PV and PMT functions hard to track. It makes sense when I think about what you wrote, I hope it stays in my brain...