Creating a Sharpe Ratio function using VBA

Hi,

I'm fairly new to VBA and am trying to create some statistical functions in excel using VBA, but am having difficulty writing the code for one: the Sharpe ratio.

In Excel, I use the formula following: average(C:C)/stdev(C:C) * sqrt(12)
where column C is the excess return of a fund over a risk-free rate (i.e. column A minus column B or C1=A1-B1, C2=A2-B2, etc).

I can create a function that works off column C where I calculate the excess in Excel, but would prefer one that works off columns A and B alone with the excess calculated within the function. Unfortunately I've hit a bit of a block here.

Any ideas on how to write this would be greatly appreciated?

Many thanks!

Hi,

Thanks for the response and apologies on the vague thread title.

The reason that I want to create a function is so that users who do not know the Sharpe Ratio formula can simply type something along the lines of:

=SharpeRatio(A:A,B:B)

For info, SQRT(12) is to annualise the Sharpe Ratio, as the calculations will be based on monthly returns.

Thanks.

Hi,

Can someone please tell me where in the code in Shg's post is the instruction to look at columns A and B when calculating the ratio?

Thanks

There's an Excel spreadsheet for the Sharpe Ratio here. It uses VBA.