Cumulative return function in VBA with user defined ranges

[Christian]

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!

 

[doug reich]

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?)

 

[Christian]

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.

 

[doug reich]

The function does need to be in an array and it does not ignore blank values.

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.

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.

 

[Christian]

Thanks for the help Doug. A coworker here was able to help me out. I appreciate the quick replies though, Quantnet is the best!

 

[doug reich]

Curious what you did. something I suggested, or another option?

 

[Christian]

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