python fsolve says converge but excel says diverge case

Question

i tried finding solutions of the data with python scipy.fsolve

my input data is here (link removed)

but Excel's goal seek determines the data has no solution.

my entire code is below

import scipy as sco
from scipy.optimize import fsolve
import pandas as pd
import numpy as np
from functools import partial
import matplotlib.pyplot as plt

data = pd.read_csv(r'local/data.csv', dtype=float, header=0 )

def goal_seek_eir(cf_df, guess=0.01
                  , cash_flow_bom='cash_bom', cash_flow_mom='cash_mom'
                  , cash_flow_eom='cash_eom', target_bel='target_bel', date_column_name='t'):
    f = partial(npv, cf_df
                , cash_flow_bom=cash_flow_bom, cash_flow_mom=cash_flow_mom
                , cash_flow_eom=cash_flow_eom, target_bel=target_bel, date_column_name=date_column_name)
    result_fsolve = fsolve(f, guess, full_output=True)
    # result_fmin = sco.fmin(f, guess)
 
    return result_fsolve
 
def npv(cf_df,rate
        ,cash_flow_bom='cash_bom',cash_flow_mom='cash_mom'
        ,cash_flow_eom='cash_eom',target_bel='target_bel',date_column_name='t'):
 
    mapper = lambda x : _calculate_discounted(x[cash_flow_bom]
                                             ,x[cash_flow_mom]
                                             ,x[cash_flow_eom]
                                             ,round(x[target_bel])
                                             ,rate
                                             ,x[date_column_name])
 
    return cf_df[[cash_flow_bom,cash_flow_mom,cash_flow_eom
                ,target_bel,date_column_name]].apply(mapper, axis=1).sum()
 
def _calculate_discounted(cf_bom, cf_mom, cf_eom, target_bel, rate, t):
    return ( cf_bom / (1+rate)**(max(t-1,0)/12)
            + cf_mom / (1+rate)**(max(t-0.5,0)/12) + cf_eom / (1+rate)**(t/12) 
            -   target_bel  )
 
result_fsolve = goal_seek_eir(data)

result_fsolve

also, i drew the function of data and found this function is diverge.

(X axis is input rate, Y axis is result of cash_flow)

the red line is [terget_bel] of data(excel E2 cell)

and the blue line is [cash_flow_eom]+[cash_flow_mom]+ [cash_flow_eom]. (sum of excel columns F to H)

the target is [cash_flow_eom]+[cash_flow_mom]+[cash_flow_eom] - [terget_bel] (excel J2 cell)

i think this functions is diverge, because the red line do not cross the blue line.

drawing plot code

#function
def npv(cf_df,rate
        ,cash_flow_bom='cash_bom',cash_flow_mom='cash_mom'
        ,cash_flow_eom='cash_eom',target_bel='target_bel',date_column_name='t'):
 
    mapper = lambda x : _calculate_discounted(x[cash_flow_bom]
                                             ,x[cash_flow_mom]
                                             ,x[cash_flow_eom]
                                             ,round(x[target_bel])
                                             ,rate
                                             ,x[date_column_name])
    return cf_df[[cash_flow_bom,cash_flow_mom,cash_flow_eom
                ,target_bel,date_column_name]].apply(mapper, axis=1).sum()

f = partial(npv, data
            , cash_flow_bom='cash_bom', cash_flow_mom='cash_mom'
            , cash_flow_eom='cash_eom', target_bel='target_bel', date_column_name='t')
#plot
x = np.arange(-0.05,1,0.0002)
plt.plot(x, f(x))
plt.axhline(y=sum(data['target_bel']), color='r')
plt.xlabel('rate')
plt.show()

so this problem seems definitely diverge, BUT by fsolve function of scipy with python says this function is CONVERGE

and the solution is same with guess(initial value) and it changes by the guess value.(this is weird)

fsolve result when guess is 0.005. the solution is 0.005

fsolve result when guess is 0.01. the solution is 0.01

Q1. Can i ask why fsolve determines this function if converge? even though Excel says it's diverge.

Q2. why fsolve's solution is same with guess value?

The scales of your data are high - hundreds of millions; might need scaling down. Look at your `infodict` - `fsolve` is returning the initial guess and `r` is 2.7e+34! So it's almost certainly not a valid solution, despite `ier == 1`. — Reinderien, Aug 05 '23 at 20:44
Your graph already shows that no root is possible, so "what do you actually want"? — Reinderien, Aug 05 '23 at 20:46
This question has some serious formatting issues. Plus, I consider it bad practice to share Google drive links to data since clicking those can share personal information to OP and others. Please edit this question to include all the necessary information within the body of the question (simplify the problem if necessary). Please also spend some time formatting your code and improving the grammar and formatting of the body text; there is too much to fix. — jared, Aug 06 '23 at 06:40
@jared Thanks for your advice. Can i ask what is the fomatting issue? Can you give me an example? Does the formatting issue mean about data type? — 0in, Aug 06 '23 at 14:17
Grammar, spacing, capitalization, inline code formatting, photos of code instead of text. Just to name a few. — jared, Aug 06 '23 at 22:50
@Reinderien Thanks for you reply, i think fslove should say ‘The iteration is not making good progress, as measured by the improvement from the last ten iterations.' Because this case is a divergence case. And should be 5. I want to know why this case’s is 1. — 0in, Aug 07 '23 at 09:54
This problem doesn't seem possible to solve - f(x) doesn't have any value where f(x) == 0. Am I misunderstanding something? — Nick ODell, Aug 07 '23 at 18:25
@NickODell I agree. This functuon is diverge case. Then, i think fsolve function should have returned ‘The iteration is not making good progress, as measured by the improvement from the last ten iterations.' and ier =5 — 0in, Aug 08 '23 at 03:52

python fsolve says converge but excel says diverge case

0 Answers0