Calculate the future value for only one category using the IRR (Python)

Question

import xlrd
import numpy

fileWorkspace = 'C://Users/jod/Desktop/'

wb1 = xlrd.open_workbook(fileWorkspace + 'assign2.xls')
sh1 = wb1.sheet_by_index(0)

time,amount,category = [],[],[]            
for a in range(2,sh1.nrows):
    time.append(int(sh1.cell(a,0).value))        # Pulling time from excel (column A)
    amount.append(float(sh1.cell(a,1).value))    # Pulling amount from excel (column B)
    category.append(str(sh1.cell(a,2).value))    # Pulling category from excel (column C)
#print(time)
#print(amount)
#print(category)
print('\n')

p_p2 = str(sh1.cell(0,1))
p_p1 = p_p2.replace("text:'","")
pp = p_p1.replace("'","")
print(pp)                            # Printing the type of pay period (Row 1, col B)
c_p2 = str(sh1.cell(1,1))
c_p1 = c_p2.replace("text:'","")
cp = c_p1.replace("'","")
print(cp)                            # Printing the type of compound period (Row 2, col B)

netflow = 0
outflow = 0
inflow = 0
flow = 0

cat = ["Sales", "Salvage", "Subsidy", "Redeemable", "Utility", "Labor", 
       "Testing", "Marketing", "Materials", "Logistics"]

if pp == "Years" and cp == "Years":   # if pay period and compound period are both in years

    IRR = numpy.irr(amount) * 100            # Calculates the internal rate of return (IRR)
    print ("IRR:", round(IRR, 2), '%', '\n') # prints (IRR)

    for i in time:              # for every value in time array
        if cat[5] in category:  # if "Labor" for cat array is in category array or not

            # calculates the present values using all the amount values (col B) instead of 
            # just using the ones that has "Labor" category label beside them
            # Need to make every other value 0, such as beside "Redeemable" and "Salvage"
            flow = amount[i] / numpy.power((1 + (IRR/100)), time[i])
            if flow>0:                      
                inflow = inflow + flow      
            if flow<0:                      
                outflow = outflow + flow  

            print ('Present Value (P) is:', round(flow,0), '\n')

    netflow = outflow + inflow
    print("In year 0 or current year")
    print("-------")
    print ('Outflow is: ', round(outflow,0))
    print ('Inflow is: ', round(inflow,0))
    print ('Netflow is: ', round(netflow,0), '\n')

    outflow2 = (round(outflow,0))*(1+(IRR/100))**(9)
    inflow2 = (round(inflow,0))*(1+(IRR/100))**(9)
    netflow2 = outflow2 + inflow2

    print("In year 9")
    print("-------")
    print ('Outflow is: ', round(outflow2,0))
    print ('Inflow is: ', round(inflow2,0))
    print ('Netflow is: ', round(netflow2,0), '\n')

I have commented important lines of code for clarification. Here is the original question:

illustrate the breakdown of major project revenues and expenses by category as a percentage of that project’s future value in year 9. The illustration must also clearly indicate the total future value of the project in year 9 as well as the IRR.

There will be a total of 10 revenue and cost categories that a project may be composed of. The categories are: Sales, salvage, subsidy, redeemable, utility, labor, testing, marketing, materials and logistics. All revenues and expenses will fall in one of these ten categories. The project pay period and compound period will be identified at the top of the Excel sheet. Pay period and compound period may be designated as any of the following: years, quarters, months.

---

I am getting confused because I am not able to pull the only values from beside the "Labor", "Redeemable", or "Salvage". I just don't know where I am making a mistake, or there is something that is incomplete. Below is the excel file image:

Excel File Image 2 Excel File Image 3

Answer 1

After revising, all cashflows are discounted at the irr. What is done is the following: i) determineAdjustments takes the pay period (column A) and adjusts if for the year ended (if it is a monthly amount it puts it in the proper year ended) and if its monthly puts in in the month ended (no adjustment necessary). This will divide the pay period by 12 if yearly cash flows are needed (yearly compounding)

ii) IRR is calculated, and the compounding period is used to adjust the monthly IRR for monthly pay periods

iii) all expenses are discounted at the IRR and input into a list for cat_contributions['category_name'] = [discounted period 1, discounted period 2 ... ]

iv) Then the net inflows and outflows are sums of these.

I can't type up data in the spreadsheets from the images as that would take a while, but maybe tinker with this and see if you can get it to work.

from __future__ import division
import xlrd
import numpy
import os
import math

def main(xls = 'xls_name.xlsx', sh = 0):
    #save script in same folder as the xls file
    os.chdir(  os.getcwd() )
    wb = xlrd.open_workbook(xls)
    sh = wb.sheet_by_index(0)

    pay_period = sh.cell_value(0,1)
    compounding_period = sh.cell_value(1,1)

    compounding_factor, pay_factor = determineAdjustments(
        pay_period, compounding_period)

    number_of_periods = max( sh.col_values(0, start_rowx = 2) )

    flow_per_period = [ 0*i for i in range( int( math.ceil( number_of_periods/pay_factor ) ) + 1 ) ]#list of length number of pay_periods
    for r in range(2,sh.nrows):
        pay_period = int( math.ceil( sh.cell_value(r,0) / pay_factor ) )
        flow_per_period[pay_period] += sh.cell_value(r,1) #unadjusted cash flows


    irr = calculateIRR(flow_per_period, compounding_factor)

    cat_contributions = sortExpenditures(sh, irr, pay_factor)
    total_cat_contributions, netflow, total_outflow, total_inflow = calculateFlows(cat_contributions)
    printStats(cat_contributions, irr, compounding_factor, pay_factor,
               total_cat_contributions, netflow, total_outflow, total_inflow)
    return

def determineAdjustments(pay_period, compounding_period):

    if compounding_period == 'years':
        compounding_factor = 1
        if pay_period == 'months':
            pay_factor = 12
        if pay_period == 'years':
            pay_factor = 1
        #assume no days pay periods

    if compounding_period == 'months':
        compounding_factor = 12
        #assume no yearly payouts and that the
        #all payments are in months
        pay_factor = 1


    return compounding_factor, pay_factor

def calculateIRR(cashflow, compounding_factor):

    irr = numpy.irr(cashflow)

    irr_comp = (1 + irr)**compounding_factor - 1

    #seems like in first example it uses rounded irr, can do something like:
    #irr_comp = round(irr_comp,4)
    return irr_comp

def sortExpenditures(sh, irr, pay_factor):
    #percentages and discounting occurs at the IRR caculated in the main
    #function

    cat = ["Sales", "Salvage", "Subsidy", "Redeemable", "Utility", "Labor", 
       "Testing", "Marketing", "Materials", "Logistics"]

    #python dictionary to sort contributions into categories
    cat_contributions = {}
    for c in cat:
        cat_contributions[c] = []

    # create list of contributions of each list item to FV in a dictionary
    for r in range(2,sh.nrows):
        try:
            #discounted cash flow of each expenditure
            #using formula FV = expenditure/(1+i)^n
            cat_contributions[sh.cell_value(r,2)].append(
                sh.cell_value(r,1) / ( (1 + irr) ** (sh.cell_value(r,0)/pay_factor) )
                )

        except KeyError:
            print "No category for type: " + sh.cell_value(r,2) +'\n'

    return cat_contributions

def calculateFlows(cat_contributions):

    total_outflow = 0
    total_inflow = 0

    total_cat_contributions = {}

    for cat in cat_contributions:
        total_cat_contributions[cat] = sum( cat_contributions[cat] )
        if total_cat_contributions[cat] < 0:
            total_outflow += total_cat_contributions[cat]
        else:
            total_inflow += total_cat_contributions[cat]

    netflow = total_inflow + total_outflow

    return total_cat_contributions, netflow, total_outflow, total_inflow

def printStats(cat_contributions, irr, compounding_factor, pay_period,
               total_cat_contributions, netflow, total_outflow, total_inflow):

    print "IRR: "+str(irr*100) +'  %'
    if compounding_factor == 1: print "Compounding: Yearly"
    if compounding_factor == 12: print "Compounding: Monthly"

    if pay_period == 1: "Cashflows: Year Ended"
    if pay_period == 12: "Cashflows: Month Ended"

    print "Future Value (Net Adjusted Cashflow): " +str(netflow) 
    print "Adjusted Inflows: " + str(total_inflow)
    print "Adjusted Outflows: " + str(total_outflow) +'\n'


    for cat in total_cat_contributions:
        if total_cat_contributions[cat] != 0:
            print '-----------------------------------------------------'
            print cat + '\n'
            print "Total Contribution to FV " + str( total_cat_contributions[cat] )
            if total_cat_contributions[cat] < 0:
                print "Contribution to Expenses: " + str ( abs(100 * total_cat_contributions[cat]/total_outflow) )
            else:
                print "Contribution to Revenues: " + str ( abs(100 * total_cat_contributions[cat]/total_inflow) ) +'\n'


main(xls='Book1.xlsx')