Cara menggunakan investment return python

Calculating Monthly IRR Using Python and the Binary Search Algorithm

Introduction

Have you ever stumbled upon an error in the calculation of IRR using Excel solvers? Luckily, programming can help us arrive at and calculate complex cash flows’ IRR and arrive at a solution. In this article, we will try to accomplish this through an algorithm called the Binary Search Algorithm.

The binary search algorithm is best used for finding a solution within a given array or range of possible solutions. Other terms for the binary search algorithm are the half-interval search, the logarithmic search and, the binary chop.

We will discuss the basics of algorithms. For those who are only interested in the codes, please skip to the “Codes” portion of this article.

Python vs Excel

It is important to note that the same IRR can be computed using Excel solvers but the use of Python (and other similar programming languages) does provide one specific advantage.

Python allows for the calculation of a huge number of cash flows where Excel solvers fail to arrive at a solution. This is particularly important for projects where the mode of calculation is monthly.

Excel users circumvent this problem by aggregating monthly cash flows to annual cash flows thereby shortening the total number of periods. With Python, however, we can proceed to calculate the IRR with the monthly cash flows.

Basics of Binary Search Algorithm

The binary search algorithm is a simple and efficient algorithm for solving the nonlinear equation:

Suppose, for example, we want to find the maximum value of an equation given

To solve this, we need to find the critical points of the function f(x), where

To solve this numerically through the binary search algorithm, we need to choose two points, a and b, where

We can set our a and b to be:

Since f’(x) is a continuous function, we are confident that because the sign of f’(x) changes from negative to positive from 0 to 1, a solution exists along that interval where f’(x) is 0.

After getting the initial midpoint, say point, c:

and we check the sign of the function at that point, f’(c ).

As we overwrite prior highs and lows, we proceed to calculate a new midpoint (new c).

We continue this process until the algorithm converges to a solution that satisfies our optimality condition, f’(x) = 0.

The binary search is efficient. It divides our confidence interval to two per iteration and converges to a solution quickly.

One disadvantage of the binary search algorithm is that it only finds one solution to the problem. So if our function above had local extrema, then binary search could converge to any one of them.

Coding — Internal Rate of Return

Most of the detailed projects by corporate usually involve a monthly cash flow projection. As we noted earlier, one approach to calculate an annual IRR is to aggregate the cash flows to form an annualized value.

With Python and NumPy, however, we can proceed to calculate the internal rate of return with the monthly cash flows and just annualize the resulting monthly IRR.

Let’s begin.

Preliminaries

import pandas as pd
import numpy as np

Loading the Dataset

Our Dataset is composed of a monthly cash flow (net):

cf = pd.read_csv('data/data.csv')

Preprocessing

For data preprocessing of financial projections or cash flows, the following are the most common problems that we need to address:

1. Incorrect data type — For our pandas methods to work, we need to ensure that our net cash flow variable is of the proper data type (float).
2. Cash flows are represented column-wise — Most cash flow projections have the cash flows aligned in a horizontal manner where the period number is a column attribute. Cash flows and time period observations should be arranged in such a way that one row represents one period.

#Ensure that periods and cash flows are features (columns)
cf = cf.Tcf.rename(columns={0: "NET CASH FLOW"}, inplace=True)
cf = cf.iloc[1:]#ensure cash flow data type is float
cf['NET CASH FLOW'] = cf['NET CASH FLOW'].str.replace(',', '')
cf['NET CASH FLOW'] = pd.to_numeric(cf['NET CASH FLOW'], errors='coerce').fillna(0).astype('float')

With all these efforts, NumPy has a built-in method to easily calculate IRR. After doing our preprocessing, we just call the NumPy.irr() method on our cash flows, and ‘poof’, we have our IRR.

m_irr = np.irr(cf['NET CASH FLOW'])
m_irr

One need not go through the trouble of writing an algorithm by scratch your monthly IRR. To annualize, simply code:

m_irr = np.irr(cf['NET CASH FLOW'])#Annualization
irr = ((1+m_irr)**12)-1
irr

If we are only concerned with getting the IRR, the built-in method is sufficient for this purpose. The next part, however, will show us how to get the same answer in action.

IRR From Scratch: Binomial Search

We can use the techniques we have learned from the binomial search algorithm to calculate the IRR of a series of cash flows.

Note that the IRR is the discount rate that sets the Net Present Value of a project to 0.

To accomplish this, therefore, we need to write a function for NPV calculation:

def NPV_calculator(array, rate):
    npv = 0
    for i in range(len(array)):
        npv += array[i] / (1+rate)**i
    return npv

Next, we need to use this function and set it to 0. This is similar to the problem we posed on the binomial search algorithm basic explanation.

To begin, let us first initiate our variables:

#Initiate the values
high = high
low = low
mid = 0
npv = -1 #Do not initiate npv to 0, as our condition involves finding the rate that will make it to 0(the while loop won't run)
    #Calulate the mid and initial npv
mid = (high + low)/2
npv =  NPV_calculator(array, mid)

It’s very easy to initiate variables for IRR as most IRR, assuming the project cash flows are realistic and do not exceed 1.

Likewise, calculated IRRs do not usually become negative unless the aggregated cash flows are less than the initial investment. At this point, projects would usually be eliminated prior to the calculation of metrics.

After this, we initiate the while loop that will terminate upon the completion of one or more conditions:

1. NPV equals 0 — By definition, this is the required NPV calculated using the IRR. While this is a theoretical possibility, NPV may never be zero, practically, for certain projects. This is one reason why Excel solvers may fail, particularly the “Simplex” method. As proof, you may try to solve the IRR from the streams of cash flows above using an Excel simplex solver.
2. The rounded value of our high parameter is equal to the rounded value for our low parameter — This condition is placed for practical purposes. If the rounded value of our high and low parameters are almost identical, there is little value added in the midpoint of the two points. Economically, some values, particularly monetary or financial ones, do not have any economic significance beyond certain decimal places (e.g. nothing is lower than a cent).

Here, we implement the core of the problem:

while not (npv == 0) and not (round(high,6) == round(low,6)):
        if npv < 0:
            high = mid
            mid = (high + low)/2
            npv = NPV_calculator(array, mid)
        else:
            low = mid
            mid = (high + low)/2
            npv = NPV_calculator(array, mid)

Now, we just need to piece it all together:

def binary_search(array, high, low):
    #Initiate the values
    high = high
    low = low
    mid = 0
    npv = -1
        #Calulate the mid and initial npv
    mid = (high + low)/2
    npv =  NPV_calculator(array, mid)
            while not (npv == 0) and not (round(high,6) == round(low,6)):
        if npv < 0:
            high = mid
            mid = (high + low)/2
            npv = NPV_calculator(array, mid)else:
            low = mid
            mid = (high + low)/2
            npv = NPV_calculator(array, mid)return mid

The resulting function would then give us:

This is very close to the one we got using the NumPy.irr() method. The difference is brought about by the second condition we placed regarding the rounded value of high and low. If we remove that condition, you will see that the algorithm gives the exact value as the NumPy.irr() method.

Finally, you can add some modifications to this basic code such as ensuring that the high parameter is always greater than the low parameter and even creating a dataframe that records the values of the iterations.

def binary_search(array, high, low):
    df = pd.DataFrame(columns=["Low", "Mid", "High", "NPV"],)
    high = high
    low = low
    mid = 0
    npv = -1
    #Check initial case:
    if high > low:
        mid = (high + low)/2
        npv =  NPV_calculator(array, mid)
        while not (npv == 0) and not (round(high,6) == round(low,6)):
            if npv < 0:
                high = mid
                mid = (high + low)/2
                npv = NPV_calculator(array, mid)
                df.loc[len(df)] = [low, mid, high, round(NPV_calculator(array, mid),4)]
            else:
                low = mid
                mid = (high + low)/2
                npv = NPV_calculator(array, mid)
                df.loc[len(df)] = [low, mid, high, round(NPV_calculator(array, mid),4)]                        
            #encode the last value
        df.loc[len(df)] = [low, mid, high, round(NPV_calculator(array, mid),4)]else:
        return "'High' must be greater than the 'low' parameter"
    return df

Let me guys know what you think.

Full code is available on my Github page.