Automate PIK Tracking Spreadsheet
Budget: $25 – $50 USD
I have a CSV model named “LX222379 – CIFC” that needs two new, fully-functional columns:
1. Outstanding Commitment Amount – a simple sum of the existing PAR/Quantity and PIK figures.
2. Outstanding PIK Amount – a live balance that increases whenever the Description or Notes fields contain “Capitalized”, “PIK”, “Increase”, or the exact strings shown below, and that automatically decreases pro-rata whenever a Sale is posted.
• Description triggers:
“PIK 6.30.25”, “PIK Increase 5.30.25”, “Capitalized Interest”, “CME Term SOFR Capitalized Interest”
• Notes triggers: any use of “Capitalized”, “PIK”, or “Increase”
Please mirror the logic already working in the “LX222379 – BNY Mellon (example)” file so that the PIK balance adjusts correctly for every sale transaction.
Key requirements
• Work directly in the CSV format (no conversion to Google Sheets or Excel files).
• Lock both the PIK and PAR columns so they cannot be edited accidentally while the rest of the sheet stays fully editable.
• Keep all existing data and formulas intact, simply extend the model with the two new columns and the locking rules.
I’ll consider the job complete once the updated CSV:
• Recalculates Outstanding Commitment and Outstanding PIK accurately for every historical row.
• Shows the PIK balance falling in exact proportion to any sales lines, identical to the BNY Mellon example.
• Opens without warnings and with PIK and PAR columns protected from direct editing.
Please deliver the revised CSV and a brief note confirming the protection method you applied.
1. Outstanding Commitment Amount – a simple sum of the existing PAR/Quantity and PIK figures.
2. Outstanding PIK Amount – a live balance that increases whenever the Description or Notes fields contain “Capitalized”, “PIK”, “Increase”, or the exact strings shown below, and that automatically decreases pro-rata whenever a Sale is posted.
• Description triggers:
“PIK 6.30.25”, “PIK Increase 5.30.25”, “Capitalized Interest”, “CME Term SOFR Capitalized Interest”
• Notes triggers: any use of “Capitalized”, “PIK”, or “Increase”
Please mirror the logic already working in the “LX222379 – BNY Mellon (example)” file so that the PIK balance adjusts correctly for every sale transaction.
Key requirements
• Work directly in the CSV format (no conversion to Google Sheets or Excel files).
• Lock both the PIK and PAR columns so they cannot be edited accidentally while the rest of the sheet stays fully editable.
• Keep all existing data and formulas intact, simply extend the model with the two new columns and the locking rules.
I’ll consider the job complete once the updated CSV:
• Recalculates Outstanding Commitment and Outstanding PIK accurately for every historical row.
• Shows the PIK balance falling in exact proportion to any sales lines, identical to the BNY Mellon example.
• Opens without warnings and with PIK and PAR columns protected from direct editing.
Please deliver the revised CSV and a brief note confirming the protection method you applied.