Aggregation of Variable for multiple fund in a policy

[DanielBayo]

Using the data policy in the tutorial,

main
id,age,sex,premium
X149,45,F,100
A192,57,M,130
D32,18,F,50

fund
id,fund_code,fund_value
X149,10,15000
A192,10,3000
A192,12,9000
D32,8,12500
D32,14,12500

@variable()
def cal_xyz(t):
...

I need the cashflow for each fund in a policy, given that I have a time dependent variable cal_xyz that needs to be calculated for each data fund in the policy and cal_xyz needs to be aggregated and then used for other variables in the model to calculate the cashflow for each fund. I will really appreciate your help with this.

[zchmielewska]

Hi @DanielBayo,

you can use the following approach:

input.py

import pandas as pd
from cashflower import ModelPointSet

main = ModelPointSet(data=pd.DataFrame({
    "id": ["X149", "A192", "D32"],
    "age": [45, 57, 18],
    "sex": ["F", "M", "F"],
    "premium": [100, 130, 50],
}))

fund = ModelPointSet(data=pd.DataFrame({
    "id": ["X149", "A192", "A192", "D32", "D32"],
    "fund_code": [10, 10, 12, 8, 14],
    "fund_value": [15_000, 3_000, 9_000, 12_500, 12_500],
}))

Let's say your variable is management_fee. The fee is calculated as 1% of the fund value in the first year (t<=12) and 0.5% afterwards.

model.py

@variable()
def management_fee(t):  # cal_xyz
    if t <= 12:
        fee = 0.01
    else:
        fee = 0.005

    total = 0
    for i in range(0, fund.model_point_data.shape[0]):
        total += fund.get("fund_value", i) * fee

    return total

In this approach:

• The for-loop iterates over all the funds and sums the total fee.
• fund.model_point_data.shape[0] returns the number of funds each policyholder has (e.g., X149 has one fund, while A192 and D32 each have two funds).
• fund.get("fund_value", i) retrieves the fund value of the specified fund (e.g., the first for X149 and the first or second for A192 and D32).

Please let me know if this solves your problem.

[zchmielewska]

Alternatively, if your calculations are more complex and you wish to have each fund in a separate variable, you can preprocess the data in the input. For example:

input.py

import pandas as pd
from cashflower import ModelPointSet


policy = pd.DataFrame({
    "id": ["X149", "A192", "D32"],
    "age": [45, 57, 18],
    "sex": ["F", "M", "F"],
    "premium": [100, 130, 50],
})

fund = pd.DataFrame({
    "id": ["X149", "A192", "A192", "D32", "D32"],
    "fund_code": [10, 10, 12, 8, 14],
    "fund_value": [15_000, 3_000, 9_000, 12_500, 12_500],
})

# Pivot fund DataFrame and merge with policy DataFrame
fund_pivot = fund.pivot(index="id", columns="fund_code", values="fund_value")
fund_pivot.columns = [f"fund_{col}" for col in fund_pivot.columns]
data = policy.merge(fund_pivot, on="id", how="left")
data = data.fillna(0)

main = ModelPointSet(data=data)

Here, we create only one model point set with additional columns named fund_8, fund_10, etc. The columns contain fund values.

model.py

from cashflower import variable
from input import main


@variable()
def fund_8_fee(t):
    fee = 0.03 if t <= 3 else 0.01
    return main.get("fund_8") * fee


@variable()
def fund_10_fee(t):
    fee = 0.05 if t <= 6 else 0.02
    return main.get("fund_10") * fee

Now you can have separate logic for each fund.