CODEWITHSUNDEEP
pythonoptimizationscipysolver
TRex
TRex

Reputation: 495

replicating an excel solver function in python to get required output

assuming I got cashflows from year 1 through year 4 cf = [30,45,52,67]

and discount rates (zero coupon) rt = [.02,.03,.04,.05]

calculating PV is straight fwd in python

import numpy as np
import pandas as pd

cf = [30,45,52,67]
rt = [.02,.03,.04,.05]

sum([x[0]/(1+x[1])**(i+1) for i,x in enumerate(zip(cf,rt))])

gives me the output 173.1775

Now, if I want my NPV to be 180 (hypothetically), I will simply run a solve in excel and that will adjust my "rt" (by adding a spread across the board)

enter image description here How do I replicate the same in python? I have seen/ used SciPy optimize for other purpose, but unsure how do I use it here (or if there is any other solution)

Upvotes: 0

Views: 3084

Answers (1)

gboffi
gboffi

Reputation: 25023

You can solve your problem using newton (an implementation of the Newton-Raphson method) from scipy.optimize.

newton needs a starting point and a function of a single parameter that evaluates to zero when you reach your target (this is not truly true, newton can accept also functions of more than one variable, but…) so we write a function that accepts your arguments and returns the function needed by newton, and at last we call newtonwith the initial value of zero

In [25]: from scipy.optimize import newton
    ...: cf = [30,45,52,67]
    ...: rt = [.02,.03,.04,.05]
    ...: 
    ...: def make_fun(cf, ret, val):
    ...:     def fun(d):
    ...:         return val-sum([x[0]/(1+x[1]+d)**(i+1)for i,x in enumerate(zip(cf,rt))])
    ...:     return fun
    ...: 
    ...: newton(make_fun(cf, rt, 180), 0)
Out[25]: -0.014576385759418057

Edit: of course you can choose a more descriptive name for make_fun …

Upvotes: 1

Related Questions