I'm looking for a way to use VBA to calculate the IRR of the cashflows of an amortization table without actually needing an amortization table in my workbook, but rather just the loan attributes. I currently use the following function to calculate the cumulative interest paid given the amortization month that can potentially be used for a starting point.
Anybody have any ideas? Let me know if I need to provide more information.
Anybody have any ideas? Let me know if I need to provide more information.
Code:
Function CumIntPSA(Starting_Balance, Original_Months, Loan_Age, Gross_Rate, Optional PSA = 0, Optional DelayDays = 30)
Dim CI As Variant
rm = Original_Months
gr = Gross_Rate / 12
'smm = -((1 - CPR / 100) ^ (1 / 12) - 1) 'Montly Prepayment
dd = (DelayDays - 30) / 30 'Default = 30, a 15 day grace period would be 44
cm = rm - Loan_Age
sb = Starting_Balance 'Starting Balance
CI = 0 ' Accumulate Interest
am = i + DL ' Month for prv
'Start Amortization
For i = 1 To cm
am = i + dd 'Adjusted month for prv
Interest = gr * sb
payment = Application.WorksheetFunction.Pmt(gr, cm, -sb) 'Calculate new payment
amort = payment - Interest 'Normal Amortization
'PSA Prepayments
prepay = ((1 - WorksheetFunction.Min(100, PSA * WorksheetFunction.Min(0.002 * (i + Loan_Age), 0.06)) / 100) ^ (1 / 12) - 1) * -1 * (sb - amort)
'Paydown
sb = sb - amort - prepay - delinquency
CI = CI + Interest
cm = cm - 1
Next i
CumIntPSA = CI
End Function