samedi 27 février 2016

Developing a general code that calculates the Future value for each cateogory and also one other future value

Here is the code:

import xlrd
import numpy

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

wb1 = xlrd.open_workbook(fileWorkspace + 'assign.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 category:              # 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 2016")
    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 2025")
    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. There is a lot of work to be done in the code.

Different conditions in different excel files will exist such as the following: 1) Having different compound period and pay period. There are 9 possible combinations. With that being, the IRR module that calculates IRR, how to use that?

2) There will be different categories in different excel files and also more or less than 10 years.

This code needs to be a general code. It should be able to do what we to do with any excel file. It just seems to be really difficult.

The answer for IRR is 6.29% and the net present worth is $1.64

Below is the excel file image:

Excel Image

Aucun commentaire:

Enregistrer un commentaire