# 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.

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.

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.