0

I am trying to create a linear programming model that will find the optimal mix of bonds to sell that maximizes book yield, exceed a certain market value, do not fall below a certain gain/loss amount, and are limited to the maximum amount that is actually owned of the bond.

Here is a sample of data from a bond portfolio that I am working with:

min   max     market_value    gain_loss    book_yield
0    0.192    5793070.000    -2096660.217   1.564
0    0.332    10013973.531   -257093.942    5.304
0    0.357    10741519.024   -3612018.833   1.614
0    0.118    3552909.914    -127308.087    4.218
0   0.00006   1834.918       -4.967         5.023

Here is my current code:

library(lpSolve)
library(lpSolveAPI)
library(readxl)

setwd(directory)


bond_data2 <- read_xlsx("Transaction Model2.xlsx")
View(bond_data2)

gain_loss2 <- bond_data2$`Gain/Loss`
market_value2 <- bond_data2$`Market Value`
book_yield2 <- bond_data2$`Book Yield`
max_owned2 <- bond_data2$Max
 
lp_model2 <- make.lp(0, ncol=length(max_owned2))  

set.objfn(lp_model2, book_yield2)

for (i in seq_along(max_owned2)) { 
  set.bounds(lp_model2, columns = i, upper = max_owned2[i], lower = 0) 
}

add.constraint(lp_model2, c(market_value2), ">=", 15000000)

add.constraint(lp_model2, c(gain_loss2), ">=", -2000000) 

solve(lp_model2) 
 
solution <- get.variables(lp_model2) 
solution
 
print(get.objective(lp_model2))

print(get.constraints(lp_model2))

bonds_to_sell2 <- bond_data2[solution > 0, ]
print(bonds_to_sell2$CUSIP)

print.lpExtPtr(lp_model2)

Here are the results I receive:

>solution
[1] 6.952180e-310 3.799365e-321  0.000000e+00  0.000000e+00  0.000000e+00

Which clearly are unfeasible and make no sense, however, when I print the properties of the mode, they all look how I want them to:

>C1                    C2                    C3                    C4                    C5  
           
>Minimize                 1.564                 5.304                 1.614                 4.218                 5.023     
        
>R1                     5793070       10013973.530993      10741519.024      3552909.91400      1834.918000  >=  1.5e+07

>R2           -2096660.21700      -257093.94199     -3612018.83300     -127308.087000                -4.967  >=   -2e+06

>Kind                       Std                   Std                   Std                   Std                   Std 
            
>Type                      Real                  Real                  Real                  Real                  Real 
            
>Upper        0.192439652079616     0.332653598565203     0.356821889565486     0.118023905756426  6.09540332698593e-05   
          
>Lower                        0                     0                     0                     0                     0

To simplify: What is the best mix of bonds to sell that:

  1. Minimize book yield
  2. do not exceed a 2,000,000 loss
  3. Provide 15,000,000 in market value.
  4. maximum amount sold does not exceed what is owned (found in the max column which is that bonds ratio of the total sum of the market values)

Any help would be much appreciated.

Improve this question
2
  • The market value minimum of 15M is infeasible. The highest this can go given your other constraints is 8.7M. Commented Jul 21, 2024 at 13:25
  • In fact, the problem is trivial: for the provided input, the solution is to take the maximum for each bond amount (disregarding the market value constraint). Commented Jul 21, 2024 at 13:33

1 Answer 1

Reset to default
0

As in the comments, the problem is infeasible. Either:

  • Add more bond data rows, or
  • Dramatically increase the bond maxima, or
  • Dramatically decrease the $15M market value minimum.

Demonstrating in Python,

import io
import pandas as pd
import pulp

with io.StringIO(
'''
min  max  market_value    gain_loss     book_yield
0    1    5793070.000    -2096660.217   1.564
0    1    10013973.531   -257093.942    5.304
0    1    10741519.024   -3612018.833   1.614
0    1    3552909.914    -127308.087    4.218
0    1    1834.918       -4.967         5.023
''') as f:
    bond_data2 = pd.read_fwf(f)

bond_data2['bonds'] = pulp.LpVariable.matrix(name='bonds', indices=bond_data2.index)
for idx, row in bond_data2.iterrows():
    row['bonds'].bounds(low=row['min'], up=row['max'])

book_yield = pulp.lpDot(bond_data2['book_yield'], bond_data2['bonds'])
market_value = pulp.lpDot(bond_data2['market_value'], bond_data2['bonds'])
gain = pulp.lpDot(bond_data2['gain_loss'], bond_data2['bonds'])
lp_model2 = pulp.LpProblem(name='bond_portfolio', sense=pulp.LpMaximize)
lp_model2.setObjective(book_yield)
lp_model2.addConstraint(name='min_market_value', constraint=market_value >= 15e6)
lp_model2.addConstraint(name='min_gain', constraint=gain >= -2e6)
lp_model2.solve()
assert lp_model2.status == pulp.LpStatusOptimal

bond_data2 = bond_data2.map(pulp.value)
print(bond_data2)
print(f'  Book yield: {book_yield.value():.3f}')
print(f'Market value: ${market_value.value():,.0f}')
print(f'        Gain: ${gain.value():,.0f}')

   min  max  market_value    gain_loss  book_yield     bonds
0    0    1  5.793070e+06 -2096660.217       1.564  0.770555
1    0    1  1.001397e+07  -257093.942       5.304  1.000000
2    0    1  1.074152e+07 -3612018.833       1.614  0.000000
3    0    1  3.552910e+06  -127308.087       4.218  1.000000
4    0    1  1.834918e+03       -4.967       5.023  1.000000
  Book yield: 15.750
Market value: $18,032,600
        Gain: $-2,000,000
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.