Calculating IRR on an Array within VBA code

Question

VBA beginner here. I'm working with code (shown below) that essentially creates a mortgage amortization table, then pastes the results into my worksheet and then calculates an IRR on one of the ranges. Rather than referencing range "B35:HR35" for the IRR, is there a way to reference the 'myarray(8, i)' that is the 'cf' field? I'd like to do this so I can avoid having to paste a bunch of data into my worksheet and just have the IRR. I've looked around about defining arrays and using that in my IRR calc, but I haven't found anything that works yet.


Application.ScreenUpdating = False

Dim myarray(1 To 15, 0 To 500)
Dim amort As Double
Dim sb As Double
Dim defb As Double
Dim payment As Double
Dim cf As Double

gr = Range("B3") / 12 'Gross Coupon
rm = Range("B4") 'Remaining Months
sb = Range("B2") 'Starting Balance
SMM = Range("B5") 'Single Month Mortality Rate
Term = Range("B4") 'Loan Term
defb = Range("B6") 'Deferral Balance
cf = -(sb + defb) ' Cashflow
myarray(8, 0) = cf 'Place initial cashflow in eighth row, first column
myarray(9, 0) = sb 'Place starting balance in ninth row, first column
TotalMonths = rm 'Set to remaining months
rm = rm + 1 ' Add one to remaining months (needed because the amortization starts out subtracting one month for each row)

For i = 1 To TotalMonths
ShowMonth = i
MOB = MOB + 1
rm = rm - 1

payment = -Pmt(gr, rm, sb, 0)
interest = gr * sb
principal = payment - interest
prepayment = SMM * (sb - principal)
amort = principal + prepayment
sb = sb - amort
cf = amort + interest

myarray(1, i) = ShowMonth
myarray(2, i) = sb + amort
myarray(3, i) = payment
myarray(4, i) = interest
myarray(5, i) = principal
myarray(6, i) = prepayment
myarray(7, i) = amort
myarray(8, i) = cf
myarray(9, i) = sb

Next i

1000 Range("b28").Resize(9, 500).Value = myarray


Range("B7") = (1 + (Application.WorksheetFunction.IRR(Range("B35:HR35")))) ^ 12 - 1


Application.ScreenUpdating = True

End Sub

You can use `myarray(8, i)` but after the array has been loaded or to load that specific element. Which range should be in that specific array? If it is about a cells range, things are much more simpler... — FaneDuru, Feb 20 '20 at 14:34
I think you dont have to populate the excel sheet with array values. Instead you may use `Range("B7") = (1 + (Application.WorksheetFunction.IRR(myarray(8,i)))) ^ 12 - 1` and you can avoid `1000 Range("b28").Resize(9, 500).Value = myarray` — Naresh, Feb 20 '20 at 14:51
@Naresh if I do as you say, I get an error: "Run-time error '1004': Unable to get the Irr property of the WorksheetFunction class" — BTFinance, Feb 20 '20 at 16:47
@FaneDuru I'm not sure what you're asking. What I'm trying to do is calculate the IRR of a series of cashflows (which appears as myarray(8, i) in my code) without having to first paste the cashflows into my spreadsheet. Rather than do this: Calculate cashflows->paste cashflows->calculate IRR; I'd like to do this: Calculate cashflows->calculate IRR. But when trying to insert the defined array into my IRR calc, I get errors "Unable to get the Irr property of the WorksheetFunction class" — BTFinance, Feb 20 '20 at 16:54
refer to https://www.excelfunctions.net/vba-irr-function.html and https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/irr-function ... to see how you can input an array to IRR function.. what you need is add myarray(8, i) to a new array (double) and then input that array to IRR function — Naresh, Feb 20 '20 at 17:11
@Naresh Using gr = 4.5%/12; sb=100,000; rm/term=240; and SMM = 1.8%; the IRR should return 4.59% (you can make defb=0 for simplicity). I can try to post a link to sample spreadsheet later tonight if that's more helpful. — BTFinance, Feb 20 '20 at 20:19

Naresh · Accepted Answer · 2020-02-21T04:56:54.263

1

Replace your code with the following code after next i see if this works

RetRate = (1 + (Application.WorksheetFunction.IRR(Application.WorksheetFunction.Index(myarray, 8)))) ^ 12 - 1
' as mentioned in your formula


Range("B7") = RetRate * 100


Application.ScreenUpdating = True

with you code image look like

with new code

edited Feb 21 '20 at 04:56

answered Feb 20 '20 at 16:43

Naresh

2,984
2
9
15

Does this answer your question? If yes, please upvote/ accept the answer and close the question – Naresh Feb 23 '20 at 19:51
for reference, https://stackoverflow.com/questions/175170/how-do-i-slice-an-array-in-excel-vba – Naresh Feb 24 '20 at 07:00

Calculating IRR on an Array within VBA code

1 Answers1