samedi 27 février 2016

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

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.

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:

Aucun commentaire:

Enregistrer un commentaire