Lessons in FM: Part VI - Rate of Return

Note: This is a continuation of the series Lessons in Financial Mathematics. Reading previous posts about this topic may aid in your understanding of this article. Note that this article is a direct continuation of Part V, and so reading that one would probably be a good idea.

Last week, I discussed my pseudo-accounting method of keeping track of the (Linden) dollar value of your gains and losses in an SL capital market. Now we turn to the topic of finding the rate of return earned over that month. I'll be using the same example as I came up with last time.

To be mathematically precise, the rate of return can become a very ugly equation very quickly. The reason is because you have to take into account all the cash flows in and out of your account at the times that they were taken. Basically, you're going to know your cash flows at time t (C_t), as well as the initial and final balances, and you have to solve something like this for i (and this is only for regular investment intervals!):

Final = Initial * (1+i)^t + C₁ * (1+i)^(n-1)/t - C₂ * (1+i)^(n-2)/t + ... + C_n-1 * (1+i)^1/t

This gets really ugly really fast. Normally even financial calculators wind up using a numerical method like Newton's Method to figure this one out. (You can tell this is a hard calculation on a financial calculator because often the calculator will pause for a few seconds before spitting out the answer.)

That being said, there are several things which can make this calculation easier:

1. Having no cash flows. That chops that ugly polynomial down to a simple exponential problem rather quickly.


2. Assume all cash flows occur at a certain time (such as at the middle of the month). This, combined with a simple interest assumption, results in a very compact formula which can be very close to the real rate of return, or very far off (if you're unlucky).


3. Put your cash flows at fixed intervals. This makes it more like an annuity calculation, discussed in Part II of my Lessons in FM.
   


4. Harness the power of Excel.

My First Life job requires me to be a venerable Excel ninja, so if you all ever need Excel lessons, just ask away. Google Spreadsheets have most of the Excel functions as well, but I may try to find some hosting space for good ol' honest-to-goodness Excel files as well if need be.

It is time to introduce you all to the XIRR function. IRR stands for "internal rate of return," and is used to calculate that ugly polynomial I referred to up there. XIRR takes the form XIRR(values,dates,[guess]). "Values" are the cash flows (positive for coming in, negative for going out), "dates" are the dates that correspond to the cash flows, and "guess" is where the iterative method starts from (just use .10 or leave it blank). XIRR returns the decimal of your return. For example, a 15% return is expressed as .15, not 15% or 1.15.

However, XIRR is based on a 365 day period, and we were dealing with a one-month period. That means that it isn't discounting quite correctly. Therefore, I suggest adjusting (mathematically: transforming) the date values so that they correspond to a year-long period, rather than a month. To do this, we'll use the YEARFRAC function, which takes a start date and end date and produces the fraction of a (365-day) year that occurs between those two dates. The syntax is YEARFRAC(start_date,end_date). To complete the transformation we want to take that fraction of the year between our start date and our (1-month) dates, multiply that by 365 (number of days different), multiply that by 12 (to stretch it to 1 year instead of 1 month), and then add it to our original starting date (to transform it). The formula looks like this:

YEARFRAC(start_date,end_date) * 12 * 365 + start_date = transformed_date

We know it works because our last day of the month transforms to the day before 1 year after the first day. We started at 12/1/2007, and the last date is 11/30/2008. We win.

The only other adjustment is a small annoyance with XIRR, and that is that our cash flows need to have their signs reversed. Also, one of the balances (preferably the beginning one) needs to be the opposing sign of the other one. I've made the beginning one negative.

XIRR turns out negative for our example because we've only included the realized gains. To get a more complete picture, I've made another couple columns with L$1,500 in unrealized gains included in the ending balance (completely arbitrary, as all unrealized gains calculations are). This results in a much nicer-looking positive rate of return.

You can find the completed spreadsheet here. It's the same as last time, but with a new tab marked "Rate of Return" where you can find these calculations. Once again, I'll happily pass out free copies of the spreadsheet so that you all can read the formulas if you like. Just email me at guardian.market@gmail.com.

I think that takes care of my first reader request. I love to answer questions and help people understand topics, so keep 'em coming. Anyone else want to provide a challenge?