Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Context: Have a straight table that list the following cumulative return on daily basis:
MTD (Month to Date) needs to be calculated every month from the starting date (selected by user) as long it is from the first day of the month through the end of that month. When the new month starts then, the calculation starts again from the first day of that month till the last day of that following month. The subsequent months it keeps doing the calculation similarly (One month at a time) on the image above MTD starts showing 2.02% on 3/1/2016 because Feb is not a full month period. Calculations go as far as the “end Date” selected by the user.
QTD (Quarter to Date) needs to do the calculations based on full periods of Quarters, so the image below shows the first value on 4/1/2016 -0.20% (all numbers are not real returns! – the negative ones) and will do the calculation till 6/30/2016. And on Jul 1, 2016 the cumulative return calculation starts again to be computed. This repeats till the last date selected by the user where calculation is done quarter at a time.
YTD (Year to date) calculations are done the same way as MTD and QTD but now the period is a full year. If the start date is not the first of the year calculations start on Jan of the following year.
Constrains:
If the date range is less than a month then do not calculate the cumulative returns just place s dash “-“in the cell.
If the date range does not cover at least a full quarter place a dash on the cells.
If the date range I less than a year place a dash on the cells.
I have attached a QVW with sample data if you want to try to help me to resolve this so you do not have to make up data to try and test.
- Thanks.