Visual Basic for Excel help



  • I have never used VB before and for the life of me I can not figure out how to do this:

     I am going to start loaning money to people on prosper.com

    What I want is an excel spreadsheet that will calculate how much income I can expect from payments each month as well as how much I will need to put in(up to what I define as my max monthly investment) in order to create an even amount of loans(in $50 increments).

    The code I have so far is this:

     Sub Calculate_Click()

    '''''''''''''''''''''''''''''
    ''''''''User Variables'''''''
    'Initial Investment
    StartingInvestment = 1000
    'Max Investment per month
    MaxInvestment = 50
    'Average Payment per Fifty you expect each month
    AveragePaymentPerFifty = 1.94
    'Number of years you want to figure out
    YearsToCalculate = 4
    'Length of Loan in months
    LengthOfLoans = 36
    '''''''''''''''''''''''''''''

    '''''''''''''''''''''''''''''
    ''''''Non user Variables'''''
    Delta = 0
    OutOfPocketThisMonth = 0
    TotalInvestmentThisMonth = 0
    NumberOfFiftys = 0
    PaymentsLastMonth = 0
    PaymentsThisMonth = 0
    OpenFiftys = StartingInvestment / 50
    Dim LoansToDieThisMonth(1000000) As Integer
    '''''''''''''''''''''''''''''

    For X = 0 To 35
        LoansToDieThisMonth(X) = 0
    Next

    'LoansToDieThisMonth(x) = the number of loans that will die at month x
    LoansToDieThisMonth(36) = OpenFiftys

    'Format the sheet
    Call FormatSheet

    For Counter = 1 To (YearsToCalculate * 12)

    'Calculate the amount you will get paid this month from all your fiftys
    PaymentsThisMonth = _
    ((OpenFiftys - LoansToDieThisMonth(Counter)) * AveragePaymentPerFifty) + PaymentsLastMonth

    'How Much you will have to pay out of pocket this month
    OutOfPocketThisMonth = (50 + Delta) - PaymentsThisMonth

    'How Much the total investment this month will be
    TotalInvestmentThisMonth = PaymentsThisMonth + OutOfPocketThisMonth

    Cells(Counter + 1, 1) = "Month " & Counter
    Cells(Counter + 1, 2) = PaymentsThisMonth
    Cells(Counter + 1, 3) = OutOfPocketThisMonth
    Cells(Counter + 1, 4) = TotalInvestmentThisMonth
    Cells(Counter + 1, 5) = OpenFiftys
    'Shift the Payments this month to the payments last month
    PaymentsLastMonth = PaymentsThisMonth

    OpenFiftys = OpenFiftys - LoansToDieThisMonth(Counter) + (TotalInvestmentThisMonth / 50)
    LoansToDieThisMonth(Counter + 36) = TotalInvestmentThisMonth / 50
    Next


    'Set Sheet column sizes
    Columns("A:E").AutoFit

    End Sub


    Sub FormatSheet()

    'Format the Sheet
    Range("A:A,B:B,C:C,D:D,E:E,F:F,G:G,H:H") = ""
    Cells(1, 1) = "Month"
    Cells(1, 2) = "Payments This Month"
    Cells(1, 3) = "How Much you will pay out of pocket this month"
    Cells(1, 4) = "Total Investment This Month"
    Cells(1, 5) = "Open fiftys"

    End Sub

     

    The "Fiftys" I refer to are the fifty dollar increments of the loans. I realize the variable names aren't so good but I wanted to be able to pass this thing around to people that I knew afterwards so they could use it to forecast the amount they would make.



  • Here's a working(?) version of the code:

     

    Sub Calculate_Click()

    '''''''''''''''''''''''''''''
    ''''''''User Variables'''''''
    'Initial Investment
    StartingInvestment = 50
    'Max Investment per month
    MaxOutOfPocketEachMonth = 50
    'Average Payment per Fifty you expect each month
    AveragePaymentPerFifty = 1.94
    'Number of years you want to figure out
    YearsToCalculate = 20
    'Length of Loan in months
    LengthOfLoans = 36
    '''''''''''''''''''''''''''''

    '''''''''''''''''''''''''''''
    ''''''Non user Variables'''''
    Delta = 0
    OutOfPocketThisMonth = 0
    TotalInvestmentThisMonth = 0
    NumberOfFiftys = 0
    PaymentsLastMonth = 0
    PaymentsThisMonth = 0
    OpenFiftys = StartingInvestment / 50
    Dim LoansToDieThisMonth(1000000) As Integer
    '''''''''''''''''''''''''''''

    For X = 0 To 35
        LoansToDieThisMonth(X) = 0
    Next

    'LoansToDieThisMonth(x) = the number of loans that will die at month x
    LoansToDieThisMonth(36) = OpenFiftys

    'Format the sheet
    Call FormatSheet

    For Counter = 1 To (YearsToCalculate * 12)

    'Calculate the amount you will get paid this month from all your fiftys
    PaymentsThisMonth = _
    ((OpenFiftys - LoansToDieThisMonth(Counter)) * AveragePaymentPerFifty)

    'How Much you will have to pay out of pocket this month
    OutOfPocketThisMonth = (50 + Delta) - PaymentsThisMonth

    While (OutOfPocketThisMonth < 0)
        Delta = Delta + 50
        OutOfPocketThisMonth = (50 + Delta) - PaymentsThisMonth
    Wend

    'How Much the total investment this month will be
    TotalInvestmentThisMonth = PaymentsThisMonth + OutOfPocketThisMonth

    Cells(Counter + 5, 1) = "Month " & Counter
    Cells(Counter + 5, 2) = PaymentsThisMonth
    Cells(Counter + 5, 3) = OutOfPocketThisMonth
    Cells(Counter + 5, 4) = TotalInvestmentThisMonth
    Cells(Counter + 5, 5) = OpenFiftys
    Cells(Counter + 5, 6) = LoansToDieThisMonth(Counter)

    'Shift the Payments this month to the payments last month
    PaymentsLastMonth = PaymentsThisMonth

    OpenFiftys = OpenFiftys - LoansToDieThisMonth(Counter) + (TotalInvestmentThisMonth / 50)
    LoansToDieThisMonth(Counter + 36) = TotalInvestmentThisMonth / 50
    Next


    'Set Sheet column sizes
    Columns("A:F").AutoFit

    End Sub


    Sub FormatSheet()

    'Format the Sheet
    Range("A:A,B:B,C:C,D:D,E:E,F:F,G:G,H:H") = ""
    Cells(5, 1) = "Month"
    Cells(5, 2) = "Payments This Month"
    Cells(5, 3) = "How Much you will pay out of pocket this month"
    Cells(5, 4) = "Total Investment This Month"
    Cells(5, 5) = "Open fiftys"
    Cells(5, 6) = "Loans Maturing This Month)"
    End Sub

    Is this actually doing what it is supposed to be doing?



  • @michaeless said:

    Here's a working(?) version of the code:

     

    It is almost working, the problem right now is that it immediately counts the new loans that you are able to purchase that month instead of waiting one month like you would really have to do because you haven't recieved the payments yet. What's the best way to fix that?



  • Sorry to be a bastard -this might not be the answer you want-- but having worked professionally with VBA (Visual Basic for Applications, as in Excel) I have to say that the best way to fix it is to use another platform and language. I hate and loathe VBA and you may blast me for being biased or whatever, but it is the worst environment of all time, and using it must count as one of the most abysmal programming experiences for any sane developer ever.



  • @Mikademus said:

    Sorry to be a bastard -this might not be the answer you want-- but having worked professionally with VBA (Visual Basic for Applications, as in Excel) I have to say that the best way to fix it is to use another platform and language. I hate and loathe VBA and you may blast me for being biased or whatever, but it is the worst environment of all time, and using it must count as one of the most abysmal programming experiences for any sane developer ever.

    QQ



  • I'm a huge fan of Excel, it can do so much without any real code. This looks like something that is perfectly do-able without the need for VBA code.



  • @Mikademus said:

    Sorry to be a bastard -this might not be the answer you want-- but having worked professionally with VBA (Visual Basic for Applications, as in Excel) I have to say that the best way to fix it is to use another platform and language.

    I agree, VBA pretty much sucks. 

    @Mikademus said:

    I hate and loathe VBA and you may blast me for being biased or whatever, but it is the worst environment of all time,

    Not anywhere near it, VBA is so far away from the worst environment. Not because it particularly good, just that there are far worse environments out there.
     


Log in to reply