Excel Formula Example in Plugin

A) Suppose Input Date and Input Gross are given as input values which are imported into Reportwa database and Gross perf need to be evaluated then it can be done using the below expression in plugin:

Input Date	Input Gross	Gross Perf
Jan-15	0.00%	$10000 (Initial Value)
Feb-15	4.03%	$10403 (10000*(1+4.03))
Mar-15	-1.30%	$10268 (10403*(1-1.30))
Apr-15	0.84%	$10354 (10268*(1+0.84))

GrossPerf=excelformulae(=IF((ROW()=1),(10000*(1+[InputGross])),(INDIRECT("C"&ROW()-1))*(1+[InputGross])))orderBy([Input.asOfDate])

The above formula will evaluate the value 10000*(1+0) if the row index is 1. To achieve this the following condition of the expression will work 10000*(1+[InputGross])

 If the row index is other than 1 the evaluation will be using the previous row value. i.e. 10000*(1+4.03) =10403 and 10403(1-1.30) =10268.

To achieve this following expression will work INDIRECT ("C"&ROW ()-1)) *(1+[InputGross])), where Indirect function of excel is used to get the value from previous row.

Steps to evaluate the above expression:

1) Enter the [InputGross] tag in the Excel formula and click on Edit in excel link

2) File will be downloaded from server. Type the formula in the excel in blue colored cell as in the screenshot below:

3) Copy the formula and close the excel.

4) Paste the formula in the “Paste excel formula” text box and press Enter Key. The expression will be added in the expression text box as in the screenshot below:

5) Save the expression.

B) 5 Year Gross Return:

5-year Gross return= (1+ (Gross Return at Report period/End Date- Gross Return at 60 months before Report Period/ End Date)/Gross Return at 60 months before Report Period/End Date) ^ (1/5)-1

Expression Used in Plugin is as below:

excelformulae(=IF(([5YearPerformanceGross] =0),"“, ([5YearPerformanceGross] ^ (1/5))-1))

Where, [5YearPerformanceGross] = [ (1+(([PerformanceGross_ReportPeriod]- [5YearPerformanceGross_LastDate])/[5YearPerformanceGross_LastDate])) ^ (1/5)-1

[PerformanceGross_ReportPeriod] = sum([GrossPerf]where ([Input.asOfDate] = [InputParameter.endDate]))

And [5YearPerformanceGross_LastDate] = sum([GrossPerf]where ([Input.asOfDate] =[Last5YearDate]))

[Last5YearDate] = excelformulae(=TEXT(DATE(YEAR ([InputParameter.endDate])-5, MONTH([InputParameter.endDate])+1,0),"mm/dd/yyyy"))

 If the 5 years has not been completed, then the above expression will return as blank value.