*= Compounding assignment

[iam]

Hello there, Happy New Year!

I am trying to replicate my Excel calculation of the Net Asset Value (NAV) but the results I am getting from AFL is totally off.

I am sure I am making some mistake somewhere and been trying to make it work for the past of couple of days. Any help will be greatly appreciated. Thank you.

In excel: let say you have the daily returns in column C and NAV in column D.
If data starts at row #2. and D2 = 100. Then NAV at cell D3 = D2 * ( 1 + C3 ) where C3 = C3/C2-1

NAV = 100;
Returns = 0;

Returns =  close/Ref(Close, -1) -1;

NAV *=  (1 + Returns); 

Filter = 1;

AddColumn( Close, "Close" );
AddColumn( Returns, "Returns", 1.4 );
AddColumn( NAV, "NAV" );

[Tomasz]

Your code is incorrect. Your formula is self-referencing (you are referencing PREVIOUS bar.

NAV *= (1+Returns);

does not reference previous bar, it just multiples NAV times (1+Returns). If your NAV was 100, it will be just 100 * (1+Returns).

Read the manual:

Understanding how AFL works

To calculate cumulative product you must use CumProd() function:

AFL Function Reference - CUMPROD

[iam]

Thank you Sir. Will take a look at the manual at the link you provided.

[fxshrat]

a[0] = 1;
a[1] = 3;
a[2] = 2;
a[3] = 5;
a[4] = 1;
a[5] = 4;
a[6] = 4;
a[7] = 6;
a[8] = 7;
a[9] = 2;
a[10] = 3;
a[11] = 5;
a[12] = 1;

x = ROC(a, 1) / 100 + 1;
start = 100;
y = CumProd(x)*start;
y[0] = start;

SetOption("NoDefaultColumns",1);

Filter = a > 0;
AddColumn(a,"A");
AddColumn(x-1,"B");
AddColumn(y,"C");

CumProd(x) gets the same result as Prod(x,Cum(1)-1)

[iam]

Thank you very much fxshrat. That worked great.

I am still learning this fantastic platform and assistance from just wonderful people like you and Tomasz are truly invaluable. So I cannot thank you enough.

The other thing I have been struggling with as a newbie, is why the code do not adhere to the date range I specified in the exploration window? I am sure it's my bad and there must be a way to make the exploration results started calculating from the start date I specified. So what is that way, I don't seem to find out how? any pointer to this would be much appreciated. Thank you a million!

[fxshrat]

//https://forum.amibroker.com/t/compounding-assignment/23373/6
bir = Status("barinrange");
fbr = Status("firstbarinrange");

a = C;
x = IIf(fbr, 1, ROC(a, 1) / 100 + 1);
start = 100;
y = CumProd(IIf(bir,x,1))*start;

SetOption("NoDefaultColumns",1);

Filter = 1;
AddColumn(a,"A");
AddColumn(100*(x-1),"B");
AddColumn(y,"C");

[iam]

I figured out the range question and here is the revised code based on @fxshrat solution and guidance from the manual provided by @Tomasz. I figured out the rest, which is how to get the calculation starts and adheres to my data range. Thank you both so very much.

Hopefully this helps another newbie like me

NAV = 100;
Returns = 0;

Returns = IIf(  Status( "BarInRange"),  Close/Ref(Close, -1) - 1, 0);
NAV  *= CumProd(1 + Returns); 


Filter = 1;

AddColumn( Close, "Close" );
AddColumn( Returns, "Returns", 1.4 );
AddColumn( NAV, "NAV" );

[iam]

Oh. I posted my response before I saw @fxshrat response to my range question.

@fxshrat your range solution is so elegant. Thank you so very much for your very fast and kind contribution. You are truly an asset to this community.

[iam]

Here is the final solution based on the awesome input from @fxshrat (Thanks again @fxshrat) :

bir = Status("barinrange");
fbr = Status("firstbarinrange");

NAV = 100; 
Returns = 0;


Returns = IIf(fbr, 1, ROC(Close, 1) / 100 + 1 );
NAV = CumProd(IIf(bir,Returns,1))*NAV;

SetOption("NoDefaultColumns",1);

Filter = 1;
AddColumn(Close , "Close");
AddColumn(100*(Returns-1),"Returns");
AddColumn(NAV,"NAV");

[Tomasz]

FWIW: I would argue that your previous code was cleaner and shorter.

[fxshrat]

@iam

Returns = 0;

You do not need to initialize.

Here is another version without IIf() functions.

//https://forum.amibroker.com/t/compounding-assignment/23373/11
bir = Status("barinrange");
fbr = Status("firstbarinrange");

start = 100;
ret = (NOT fbr)*Nz(ROC(C,1));
nav = CumProd(bir*ret/100+1)*start;

Filter = 1;
AddColumn(C,"Value");
AddColumn(ret,"Returns");
AddColumn(nav,"NAV");

Note: the reason of using fbr variable is to start at zero and 100 when there is from-to range setting.

Otherwise it would do this.

[iam]

Got it. And I also like the alternative to the IIF function. This is very neat! Thank you very much @fxshrat

[iam]

Thank you very much @Tomasz for the creation of this fantastic platform; and concise and efficient programming language. I love it