Cumulative return function in VBA with user defined ranges

Christian

Christian

Joined
1/2/07
Messages
128
Points
28
Hello everyone,

I am trying to write a user defined function in Excel that will allow me to select a range of cells and calculate the annualized return from monthly returns. I can accomplish this using an array function: {=PRODUCT((1+ET17:HS17)^(12/COUNT(ET17:HS17)))-1}.


The problem, however is not all return streams have the same starting date. I want to write a function that will ignore all blank cells and only start calculating from the first cell with a value. Can anyone provide an example of how to do this? Thank you and as always, time is of the essence!
 
I don't believe your formula needs to be an array function; both PRODUCT and SUM expect arrays.

The functions you are using already ignore blanks. I am unclear on what your desired function would do that this does not. You appear to be assuming that your input is on a monthly basis from the formula you gave; is this supposed to be a variable timeframe? (You could just put an annualizing factor in a cell outside your returns range and put it in your formula.)

EDIT: Oh, I see -- your problem is the 1+ -- why don't you just add the 1 into each row (i.e. you're computing returns already and subtracting 100%; why not just change it back?)
 
The function does need to be in an array and it does not ignore blank values. Try it out with just a few random values and you see the problem that I am having.

Edit: The process of adding 1 to every data point will be very time consuming, I want this function to be useful for future use as well.
 
The function does need to be in an array and it does not ignore blank values.
Click to expand...
See my edit. I did use an example without an issue in which the product and count do not incorporate blanks.

Edit: The process of adding 1 to every data point will be very time consuming, I want this function to be useful for future use as well.
Click to expand...
There are two ways to do this easily, quickly, once.

Either:
1. Assuming your returns are in column A, insert a blank column B and fill (either highlighting and fill-down OR copy one cell, highlight all cells, paste) a formula with "=1+A1".

EDIT: To make it permanent, you can select those new cells, copy, and Edit->Past Special->Values. Now they have no formula any more, and you can get rid of the original range.

2. Write a 1 in a cell. Copy, highlight your column A and do Edit->Paste Special->"Add". Now all your values will have 1 added to them.
 
Thanks for the help Doug. A coworker here was able to help me out. I appreciate the quick replies though, Quantnet is the best!
 
I will post the code for the solution when I am at work next week.

Update: Doug - here is the code I used.

Public Function annualror(dataRange As Range)
Dim rngLength As Integer
Dim dataLength As Integer
Dim TempRange As Range
Dim ACReturn As Double
Dim i As Integer

Set TempRange = dataRange.Resize(1, 1)
ACReturn = 1

rngLength = dataRange.Count

dataLength = 0
For i = 1 To rngLength
If Len(TempRange) > 0 Then
ACReturn = ACReturn * (1 + TempRange.Value)
dataLength = dataLength + 1
End If
Set TempRange = TempRange.Offset(0, 1)
Next i
annualror = (ACReturn ^ (12 / dataLength)) - 1
End Function
 
Back
Top Bottom