Using Awk to Estimate Internal Rate of Return

On my math and logic blog, I have a post explaining a quick and dirty method of calculating the approximate rate of return for a investment where additional funds were added during the year.

In this post, I will present an Awk script for implementing this method.

Basically, we want to read in a file with three fields: the starting value, ending value, and amount added during the time period. Each line could be a different year, month, etc.

For example, a line of:

10000 15000 2000

means that our investment started the time period with a value of $10,000, finished with a value of $15,000, and we added an additional $2,000 into the investment during the course of the time period.

We can't simply say that we converted $12,000 into $15,000 because we did not have $12,000 invested throughout the time period.

The quick and dirty method is to add 1/2 the additional amount to the starting balance and subtract 1/2 the additional amount from the ending balance. This method works best when the additional money was contributed in regular deposits across the time period.

So, for the example above, the return would be 14000/11000 = a return of 27.27%.

Finally, the awk script outputs the total return from all entries of the file. So, if the file represented the year, and each line represented a month, the program will calculate 12 monthly returns and the yearly return.

Here is the awk script:

BEGIN {tret = 1}

{

start = $1

end = $2

start += $3/2

end -= $3/2

ret = end/start

tret *= ret

print $0" "ret

}

END {print "return = "tret }