CoC Calculator.xlsx
I’m trying to create a model that automatically smooths and centers annual returns at both an asset and fund level. I’m in over my head and would really appreciate some help figuring out the best way to apply my conditions.

In short, I would like the orange text in the +/- bp (basis point) sections to be automatically generated following a set of conditions.

Conditions needed

Primary Condition: All returns are positive tending (slope >= 0) after returns have been centered. This means year-by-year there cannot be a decrease in return for any one asset or the fund. The return can be the same as the year before it. [Y46] = [Z46]

Minimize Sum(Est. Reserve)[Q48] but still >= 0; same as Minimizing SUM(Real - Smooth) [O49]

Optional Conditions

+/- bp returns only come from 50 bp index (…-1.5,-1,-.5,0, .5, 1, 1.5, 2, 2.5 …)

Minimize total adjustments made [N19]. Truer to actual is better

Minimize Sum(Est. Reserve)[Q48]

Implemented Inputs

BP Index: The index used for smoothing returns Rows: 53, 54 & 55

Max Spread From Center: the constant used to determine if the whole year is to be centered or not.

Unimplemented Inputs (Inputs I have not used anywhere in the model and may not be needed)

Max CoC Return: the most any property can return on any given year

Min CoC Return: the least any property can return on any given year

Definitions

BP: Basis Point= .01%

Smoothing: rounding to nearest number on a set index

Centering: Rounding every property return to the smoothed fund average. This is done to smooth fund average.


Any help or advice would really be appreciated.