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 FormatSheetFor 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 + OutOfPocketThisMonthCells(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 = PaymentsThisMonthOpenFiftys = OpenFiftys - LoansToDieThisMonth(Counter) + (TotalInvestmentThisMonth / 50)
LoansToDieThisMonth(Counter + 36) = TotalInvestmentThisMonth / 50
Next
'Set Sheet column sizes
Columns("A:E").AutoFitEnd 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 FormatSheetFor 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) - PaymentsThisMonthWhile (OutOfPocketThisMonth < 0)
Delta = Delta + 50
OutOfPocketThisMonth = (50 + Delta) - PaymentsThisMonth
Wend'How Much the total investment this month will be
TotalInvestmentThisMonth = PaymentsThisMonth + OutOfPocketThisMonthCells(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 = PaymentsThisMonthOpenFiftys = OpenFiftys - LoansToDieThisMonth(Counter) + (TotalInvestmentThisMonth / 50)
LoansToDieThisMonth(Counter + 36) = TotalInvestmentThisMonth / 50
Next
'Set Sheet column sizes
Columns("A:F").AutoFitEnd 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 SubIs 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.