Reference Array in VBA Code for Calculating Beta

[ttbuson]

I'm trying to write a function to calculate the beta between a stock and an index. I have three ranges, the stock returns, the index returns, and the risk free rates.

For the stock returns and the index returns I need to subtract out the risk free rates from each observation. I then calculate the slope of the line that fits the data.

Right now, my function looks like:

Function Beta(StockReturns As Range, IndexReturns As Range, RiskFreeRates As Range) As Double

Dim ExcessStockReturns As Range
Dim ExcessIndexReturns As Range
Dim Start As Integer

For Start = 1 to StockReturns.Count
     ExcessStockReturns = StockReturn.Value - RiskFreeRate.Value
Next Start

For Start = 1 to IndexReturns.Count
     ExcessIndexReturns = IndexReturn.Value - RiskFreeRate.Value
Next Start

Beta = WorksheetFunction.Slope(ExcessStockReturns,ExcessIndexReturns)
End Function

I know I need to store Excess Stock Returns and Excess Index Returns as arrays in the function, but I don't know how. Any help would be appreciated.

Thanks!

 

[Captain Smith]

See this thread

http://www.mrexcel.com/forum/showthread.php?t=536

 

[ttbuson]

Thanks for the response, but I had already seen that link. I know how to calculate the beta. I'm looking for a vba function to automate the task of subtracting out the risk free rate from each stock return value and index return value.

All the original ranges are the same length. I need to subtract out each risk free rate from the corresponding stock return and index return. I know I need to store the resulting data as an array in the function, but I'm not sure what the correct syntax for that is.

Thanks.