Off the cuff: Bottlenecks to model's VBA array code?

[Silverjman]

Hello,

So I have taken a model that runs slowly on a 16-core machine, and didn't even open on a normal laptop and with VBA arrays gotten it to now run and recalc in ~18 seconds on a laptop, but I have very little knowledge of VBA and know I have made it a lot slower than it probably needs to be. There are more formulas I could code, but first wanted to see if folks could point out glaring bottlenecks in the code so far.

1) I have labelled everything as a Variant and when I try to go back and swap them to Long debugger tells me my arrays are expecting Variant. I know Variant is larger, does > size = slower as well. See anything that can for sure be a Long?

2) I have one instance where I am writing a SUMIF row to a page and then sucking it back up into the code that I want to fix. VBA SUMIF on Array

3) I could speed it up by never writing all the grids to the excel sheet but the users like to see the data so I paste it values effectively, QUESTION if I put all of these

Range("Income_Disposal_Repossessed_Assets") = arrIncome__Disposal_Repossessed_Assets_Grid

at the very end instead of Sub() would that make any difference?

4) Also I can't import arrays from one Sub to another so I have tried to solve this with one giant sub Why is the macro window opening when I try to pass an array from one sub to another?

OK, thanks for giving this a look.

Option Explicit


Function firstMatchInCol(ByVal val, Arr, ByVal rw As Variant) As Variant
    Dim J As Variant
    For J = LBound(Arr, 2) To UBound(Arr, 2)
        If Arr(rw, J) = val Then firstMatchInCol = J: Exit Function
    Next J
End Function
Function firstMatchInRow(ByVal val, Arr, ByVal col As Variant) As Variant
    Dim I As Variant
    For I = LBound(Arr, 1) To UBound(Arr, 1)
        If Arr(I, col) = val Then firstMatchInRow = I: Exit Function
    Next I
End Function
Function Min(ParamArray values() As Variant) As Variant
   Dim minValue, Value As Variant
   minValue = values(0)
   For Each Value In values
       If Value < minValue Then minValue = Value
   Next
   Min = minValue
End Function

Function Max(ParamArray values() As Variant) As Variant
   Dim maxValue, Value As Variant
   maxValue = values(0)
   For Each Value In values
       If Value > maxValue Then maxValue = Value
   Next
   Max = maxValue
End Function

Sub Master_N_Macro()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False


'START Costs Asset Recovered Grid
Dim arrQuarters, arrNumber_of_Assets, arrN_Expenses_To_Quarter, arrAsset_Recovered As Variant

Dim I As Long, J As Long, InflationRateMatch As Long

arrNumber_of_Assets = Range("Costs_Number_of_Assets")
arrQuarters = Range("Quarters_1to40")
arrN_Expenses_To_Quarter = Range("Nexpenses_To")

ReDim arrAsset_Recovered(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
    For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
        arrAsset_Recovered(I, J) = (arrQuarters(1, J) = arrN_Expenses_To_Quarter(I, 1)) * -1
   
    Next J
Next I

Range("Costs_PasteValues_Asset_Recovered") = arrAsset_Recovered
'END Costs Asset Recovered Grid

'START Inflation Multiplier Index(Match())
Dim arrInflation_Bucket, arrInflation_Bucket_Label, arrInflation_Cumulative, arrApplicable_Inflation_Multiplier

arrInflation_Bucket = Range("Costs_Inflation_Bucket").Value
arrInflation_Bucket_Label = Range("Inflation.Inflation_Bucket_Label").Value
arrInflation_Cumulative = Range("Inflation.Cumulative").Value ' + add values
   
ReDim arrApplicable_Inflation_Multiplier(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)

            InflationRateMatch = firstMatchInRow(arrInflation_Bucket(I, 1), arrInflation_Bucket_Label, 1)
            For J = LBound(arrInflation_Cumulative, 2) To UBound(arrInflation_Cumulative, 2)
            arrApplicable_Inflation_Multiplier(I, J) = (1 + arrInflation_Cumulative(InflationRateMatch, J))
       
        Next J
    Next I
'END Inflation Multiplier Index(Match())

'START Resolution Total Owned L Value Grid
Dim arrReal_Estate_Value_In_Use As Variant

arrReal_Estate_Value_In_Use = Range("CollateralValue.Real_Estate_Value_In_Use")

ReDim arrResolution__Total_Owned_L_Value_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrResolution__Total_Owned_L_Value_Grid(I, J) = arrReal_Estate_Value_In_Use(I, 1) * arrApplicable_Inflation_Multiplier(I, J)
           
            Next J
       Next I

Range("Resolution_PV_Total_Owned_L_Value") = arrResolution__Total_Owned_L_Value_Grid
'END Resolution Total Owned L Value Grid

'START Resolution Ln Balance Grid
Dim arrDefault_Interest_Rate, arrLn_Balance_Multiplied, arrAttributable_OPB As Variant

arrAttributable_OPB = Range("Resolution_Attributable_OPB")
arrDefault_Interest_Rate = Range("Resolution_Default_Interest_Rate")

ReDim arrResolution__Ln_Balance_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrResolution__Ln_Balance_Grid(I, J) = arrAttributable_OPB(I, 1) * (1 + arrDefault_Interest_Rate(I, 1)) ^ 0.25 ^ J
           
            Next J
       Next I
      
Range("Resolution_PV_Ln_Balance") = arrResolution__Ln_Balance_Grid
'END Resolution Total Owned L Value Grid

'START Resolution Max Recovery Grid
Dim arrAll_Assets_SubPortfolio, arrResolution__Ln_Balance, Max_Recovery_result As Variant

arrAll_Assets_SubPortfolio = Range("All_Assets_SubPortfolio")
arrResolution__Ln_Balance = Range("Resolution_PV_Ln_Balance")

ReDim arrResolution__Max_Recovery_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)

             If arrAll_Assets_SubPortfolio(I, 1) = "REO" Then
             Max_Recovery_result = arrResolution__Total_Owned_L_Value_Grid(I, J)
                Else: Max_Recovery_result = Min(arrResolution__Total_Owned_L_Value_Grid(I, J), arrResolution__Ln_Balance_Grid(I, J))
                End If
            
             arrResolution__Max_Recovery_Grid(I, J) = Max_Recovery_result
           
            Next J
       Next I

Range("Resolution_PV_Max_Recovery_Amount") = arrResolution__Max_Recovery_Grid
'END Resolution Max Recovery Grid

'START Income Disposal Repossessed Assets Grid
Dim arrQuarter_of_Collection, arrCollection_Amount, arrTime_to_Cash, Time2Cash_or_QofCollection_result, Collection_Amount_or_Max_Recovery_result, Disposal_Repossessed_Assets_Result As Variant

arrQuarter_of_Collection = Range("Income_Quarter_of_Collection")
arrCollection_Amount = Range("Income_Collection_Amount")
arrTime_to_Cash = Range("Income_Time_to_Cash")

ReDim arrIncome__Disposal_Repossessed_Assets_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)

             If arrQuarter_of_Collection(I, 1) > 0 Then
             Time2Cash_or_QofCollection_result = arrQuarter_of_Collection(I, 1)
                Else: Time2Cash_or_QofCollection_result = arrTime_to_Cash(I, 1)
                End If
               
             If arrQuarter_of_Collection(I, 1) > 0 Then
             Collection_Amount_or_Max_Recovery_result = arrCollection_Amount(I, 1)
                Else: Collection_Amount_or_Max_Recovery_result = arrResolution__Max_Recovery_Grid(I, J)
                End If
       
             If Time2Cash_or_QofCollection_result = arrQuarters(1, J) Then
                Disposal_Repossessed_Assets_Result = Collection_Amount_or_Max_Recovery_result
                    Else: Disposal_Repossessed_Assets_Result = 0
                    End If
           
             arrIncome__Disposal_Repossessed_Assets_Grid(I, J) = Disposal_Repossessed_Assets_Result
                     
            Next J
       Next I

Range("Income_Disposal_Repossessed_Assets") = arrIncome__Disposal_Repossessed_Assets_Grid
'END Income Disposal Repossessed Assets Grid

'START Costs Physical Possession Grid
Dim arrCosts__Physical_Possession, arrPhysical_Possession_Expenses_From_Quarter, arrPhysical_Possession_Expenses_To_Quarter As Variant

arrQuarters = Range("Quarters_1to40")
arrPhysical_Possession_Expenses_From_Quarter = Range("Resolution_Physical_Possession_Expenses_From_Quarter")
arrPhysical_Possession_Expenses_To_Quarter = Range("Resolution_Physical_Possession_Expenses_To_Quarter")

ReDim arrCosts__Physical_Possession(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
    For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
        arrCosts__Physical_Possession(I, J) = (arrQuarters(1, J) >= arrPhysical_Possession_Expenses_From_Quarter(I, 1)) * (arrQuarters(1, J) <= arrPhysical_Possession_Expenses_To_Quarter(I, 1)) * 1
   
    Next J
Next I

Range("Costs_PasteValues_Physical_Possession") = arrCosts__Physical_Possession
'END Costs Physical Possession Grid

'START Income Rental Income Grid
Dim arrTime_to_Expiry, arrReceive_Rent, REO_or_N_Exp_To_result, arrAnnual_Rent As Variant

arrTime_to_Expiry = Range("Income_Time_to_Expiry")
arrReceive_Rent = Range("Income_Receive_Rent")
arrAnnual_Rent = Range("Collateral_Value_Annual_Rent")

ReDim arrRental_Income_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
  
   If arrAll_Assets_SubPortfolio(I, 1) = "REO" Then
                REO_or_N_Exp_To_result = 1
                    Else: REO_or_N_Exp_To_result = (arrQuarters(1, J) > arrN_Expenses_To_Quarter(I, 1))
                    End If
                arrRental_Income_Grid(I, J) = arrAnnual_Rent(I, 1) / 4 * arrReceive_Rent(I, 1) * REO_or_N_Exp_To_result * (arrQuarters(1, J) <= arrPhysical_Possession_Expenses_To_Quarter(I, 1)) * (arrQuarters(1, J) <= arrTime_to_Expiry(I, 1))
           
          Next J
       Next I

Range("Income_PasteValues_Rental_Income") = arrRental_Income_Grid
'END Income Rental Income Grid

'START Income Total Income Grid
ReDim arrTotal_Income_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                    arrTotal_Income_Grid(I, J) = arrRental_Income_Grid(I, J) + arrIncome__Disposal_Repossessed_Assets_Grid(I, J)
           
          Next J
       Next I
      
Range("Income_PasteValues_Total_Income") = arrTotal_Income_Grid
'END Income Total Income Grid

'START Costs Legal Possession Grid
Dim arrLegal_Possession_Expenses_From_Quarter, arrLegal_Possession_Expenses_To_Quarter, arrCosts__Legal_Possession As Variant

arrLegal_Possession_Expenses_From_Quarter = Range("Resolution_Legal_Possession_Expenses_From_Quarter")
arrLegal_Possession_Expenses_To_Quarter = Range("Resolution_Legal_Possession_Expenses_To_Quarter")

ReDim arrCosts__Legal_Possession(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
    For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
        For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
            arrCosts__Legal_Possession(I, J) = (arrQuarters(1, J) >= arrLegal_Possession_Expenses_From_Quarter(I, 1)) * (arrQuarters(1, J) <= arrLegal_Possession_Expenses_To_Quarter(I, 1)) * 1
   
    Next J
Next I

Range("Costs_PasteValues_Legal_Possession") = arrCosts__Legal_Possession
'END Costs Legal Possession Grid

'START Legal/Physical Possession Period Grid
ReDim arrCosts__Max_Legal_Possession(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
    For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
        For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
            arrCosts__Max_Legal_Possession(I, J) = WorksheetFunction.Max(arrCosts__Physical_Possession(I, J), arrCosts__Legal_Possession(I, J))

    Next J
Next I

Range("Costs_PasteValues_Max_Legal_Physical_Possession") = arrCosts__Max_Legal_Possession
'END Legal/Physical Possession Period Grid

'START Costs Disposal Schedule Grid
Dim arrCosts__Disposal_Schedule, arrTaxes_at_Repossession_Total_Cashout

arrTaxes_at_Repossession_Total_Cashout = Range("Costs_Taxes_at_Repossession_Total_Cashout")

ReDim arrCosts__Disposal_Schedule(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
    For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
        For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
            arrCosts__Disposal_Schedule(I, J) = (arrQuarters(1, J) = arrPhysical_Possession_Expenses_To_Quarter(I, 1)) * -1
   
    Next J
Next I

Range("Costs_PasteValues_Disposal_Schedule") = arrCosts__Disposal_Schedule
'END Costs Disposal Schedule Grid

'START Costs Taxes at Repossession Grid
Dim arrTaxes_at_Repossession_Refundable_VAT_at_REO, arrVAT_at_Repo_Refund As Variant

arrTaxes_at_Repossession_Refundable_VAT_at_REO = Range("Costs_Taxes_at_Repossession_Refundable_VAT_at_REO")
arrVAT_at_Repo_Refund = Range("Costs_VAT_at_Repo_Refund")
      
ReDim arrCosts__Taxes_at_Repossession_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
       For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrCosts__Taxes_at_Repossession_Grid(I, J) = (-arrTaxes_at_Repossession_Total_Cashout(I, 1) * (arrN_Expenses_To_Quarter(I, 1) = arrQuarters(1, J)) + arrTaxes_at_Repossession_Refundable_VAT_at_REO(I, 1) * (arrVAT_at_Repo_Refund(I, 1) = arrQuarters(1, J)))
           
            Next J
       Next I

Range("Costs_PasteValues_Taxes_at_Repossession") = arrCosts__Taxes_at_Repossession_Grid
'END Costs Taxes at Repossession Grid

'START Costs Other Costs at Repossession Grid
Dim arrOther_Costs_At_Repossession_Total As Variant

arrOther_Costs_At_Repossession_Total = Range("Costs_Other_Costs_at_Repossession_Total")

ReDim arrCosts__Other_Repossession_Costs(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
       For I = LBound(arrInflation_Bucket, 1) To UBound(arrInflation_Bucket, 1)
            For J = LBound(arrInflation_Cumulative, 2) To UBound(arrInflation_Cumulative, 2)
                arrCosts__Other_Repossession_Costs(I, J) = arrApplicable_Inflation_Multiplier(I, J) * arrCosts__Disposal_Schedule(I, J) * arrOther_Costs_At_Repossession_Total(I, 1)
           
            Next J
       Next I
      
Range("Costs_PasteValues_Other_Costs_at_Repossession") = arrCosts__Other_Repossession_Costs
'END Costs Other Costs at Repossession Grid
   
'START Costs Notary Costs Grid
Dim arrNotary_Costs As Variant
arrNotary_Costs = Range("Costs_Notary_Costs")

ReDim arrNotary_Costs_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
       For I = LBound(arrInflation_Bucket, 1) To UBound(arrInflation_Bucket, 1)
            For J = LBound(arrInflation_Cumulative, 2) To UBound(arrInflation_Cumulative, 2)
                arrNotary_Costs_Grid(I, J) = arrApplicable_Inflation_Multiplier(I, J) * arrAsset_Recovered(I, J) * arrNotary_Costs(I, 1)
           
            Next J
       Next I
Range("Costs_PastesValues_Notary_Costs") = arrNotary_Costs_Grid
'END Costs Notary Costs Grid

'START bro Fee Grid
Dim arrbro_Fee, arrbro_Fee_Floor As Variant

arrbro_Fee = Range("Costs_bro_Fee")
arrbro_Fee_Floor = Range("Costs_bro_Fee_Floor")


ReDim arrbro_Fee_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
       For I = LBound(arrInflation_Bucket, 1) To UBound(arrInflation_Bucket, 1)
            For J = LBound(arrInflation_Cumulative, 2) To UBound(arrInflation_Cumulative, 2)
                arrbro_Fee_Grid(I, J) = WorksheetFunction.Min(-arrIncome__Disposal_Repossessed_Assets_Grid(I, J) * arrbro_Fee(I, 1), arrbro_Fee_Floor(I, 1) * arrCosts__Disposal_Schedule(I, J) * arrApplicable_Inflation_Multiplier(I, J))
           
            Next J
       Next I

Range("Costs_PasteValues_bro_Fee") = arrbro_Fee_Grid
'END bro Fee Grid

'Cash for Keys START
Dim arrCash_for_Keys, arrCash_for_Keys_Floor As Variant

arrCash_for_Keys = Range("Costs_Cash_for_Keys")
arrCash_for_Keys_Floor = Range("Costs_Cash_for_Keys_Floor")


ReDim arrCash_for_Keys_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
       For I = LBound(arrInflation_Bucket, 1) To UBound(arrInflation_Bucket, 1)
            For J = LBound(arrInflation_Cumulative, 2) To UBound(arrInflation_Cumulative, 2)
                arrCash_for_Keys_Grid(I, J) = Min(arrCash_for_Keys_Floor(I, 1), -arrCash_for_Keys(I, 1) * arrCosts__Disposal_Schedule(I, J) * arrAnnual_Rent(I, 1))
           
            Next J
       Next I
Range("Costs_PasteValues_Cash_for_Keys") = arrCash_for_Keys_Grid
'Cash for Keys END

'START IIVTNU at Sale Grid
Dim arrIIVTNU_at_Sale, arrcad_Value_Multiple, arrTax_Rate, arrAssumed_cad_Value_of_Land, arrAverage_Years_Until_Sold As Variant, Years_result As String

arrAssumed_cad_Value_of_Land = Range("Costs_Assumed_cad_Value_of_Land")
arrcad_Value_Multiple = Range("Costs_cad_Value_Multiple")
arrTax_Rate = Range("Costs_Tax_Rate")
arrIIVTNU_at_Sale = Range("Costs_IIVTNU_at_Sale")
arrAverage_Years_Until_Sold = Range("Costs_Average_Years_Until_Sold")

ReDim arrIIVTNU_at_Sale_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
       For I = LBound(arrInflation_Bucket, 1) To UBound(arrInflation_Bucket, 1)
            For J = LBound(arrInflation_Cumulative, 2) To UBound(arrInflation_Cumulative, 2)
             If arrAverage_Years_Until_Sold(I, 1) < 1 Then
             Years_result = 0
             Else: Years_result = arrAverage_Years_Until_Sold(I, 1)
             End If
                arrIIVTNU_at_Sale_Grid(I, J) = arrCosts__Disposal_Schedule(I, J) * arrTax_Rate(I, 1) * arrcad_Value_Multiple(I, 1) * _
                arrAssumed_cad_Value_of_Land(I, 1) * Years_result * (arrIIVTNU_at_Sale(I, 1) = "Y") * arrApplicable_Inflation_Multiplier(I, J)
            Next J
       Next I
Range("Costs_PasteValues_IIVTNU_at_Sale") = arrIIVTNU_at_Sale_Grid
'END  IIVTNU at Sale Grid

'START SUM() of Physical Possession Quarters
Dim PhysicalSum, arrPhysical_Possession_Qrts_Sum As Variant

ReDim arrPhysical_Possession_Qrts_Sum(1 To UBound(arrNumber_of_Assets, 1), 1 To 1)

PhysicalSum = 0
For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
PhysicalSum = PhysicalSum + arrCosts__Physical_Possession(I, J)
arrPhysical_Possession_Qrts_Sum(I, 1) = PhysicalSum

Next J
PhysicalSum = 0
Next I
'END SUM() of Physical Possession Quarters

'START Outstanding Capex for WIP Grid
Dim arrOutstanding_Capex_for_WIP  As Variant

arrOutstanding_Capex_for_WIP = Range("Costs_Outstanding_Capex_for_WIP")

ReDim arrOutstanding_Capex_for_WIP_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
       For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrOutstanding_Capex_for_WIP_Grid(I, J) = arrOutstanding_Capex_for_WIP(I, 1) / arrPhysical_Possession_Qrts_Sum(I, 1) * arrCosts__Physical_Possession(I, J) * arrApplicable_Inflation_Multiplier(I, J)
           
            Next J
       Next I
Range("Costs_PasteValues_Outstanding_Capex_for_WIP") = arrOutstanding_Capex_for_WIP_Grid
'END Outstanding Capex for WIP Grid

'START Soft Refurbishment Costs Grid
Dim arrSoft_Refurb_Costs  As Variant

arrSoft_Refurb_Costs = Range("Costs_Soft_Refurb_Costs")

ReDim arrSoft_Refurb_Costs_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
       For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrSoft_Refurb_Costs_Grid(I, J) = arrCosts__Physical_Possession(I, J) * arrSoft_Refurb_Costs(I, 1) * arrApplicable_Inflation_Multiplier(I, J) / arrPhysical_Possession_Qrts_Sum(I, 1)
           
            Next J
       Next I
      
Range("Costs_PasteValues_Soft_Refurbishment_Costs") = arrSoft_Refurb_Costs_Grid
'END Soft Refurbishment Costs Grid

'START SUM() of Legal/Physical Possession Period
Dim intSum, arrMax_Legal_Possession_Qrts_Sum As Variant

ReDim arrMax_Legal_Possession_Qrts_Sum(1 To UBound(arrNumber_of_Assets, 1), 1 To 1)

intSum = 0

For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
    For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
        intSum = intSum + arrCosts__Max_Legal_Possession(I, J)
            arrMax_Legal_Possession_Qrts_Sum(I, 1) = intSum

        Next J
    intSum = 0
Next I
'END  SUM() of Legal/Physical Possession Period

'START Marketing Costs Grid
Dim arrMarketing_Costs  As Variant

arrMarketing_Costs = Range("Costs_Marketing_Costs")

ReDim arrMarketing_Costs_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
       For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrMarketing_Costs_Grid(I, J) = arrMarketing_Costs(I, 1) / arrMax_Legal_Possession_Qrts_Sum(I, 1) * arrCosts__Max_Legal_Possession(I, J) * arrApplicable_Inflation_Multiplier(I, J)
           
            Next J
       Next I
Range("Costs_PasteValues_Marketing") = arrMarketing_Costs_Grid
'END Marketing Costs Grid

'START Insurance Grid
Dim arrInsurance_Costs, arrMax_Recovery  As Variant

arrInsurance_Costs = Range("Costs_Insurance_Costs")
arrMax_Recovery = Range("Resolution_PV_Max_Recovery_Amount")

ReDim arrInsurance_Costs_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
       For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrInsurance_Costs_Grid(I, J) = -arrInsurance_Costs(I, 1) * arrResolution__Max_Recovery_Grid(I, J) * arrCosts__Max_Legal_Possession(I, J) / 4
           
            Next J
       Next I
Range("Costs_PasteValues_Insurance") = arrInsurance_Costs_Grid
'END Insurance Grid

'START C Fees Grid
Dim arrC_Fees As Variant

arrC_Fees = Range("Costs_C_Fees")

ReDim arrC_Fees_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
       For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrC_Fees_Grid(I, J) = arrC_Fees(I, 1) * arrResolution__Max_Recovery_Grid(I, J) * arrCosts__Max_Legal_Possession(I, J) / 4
           
            Next J
       Next I
Range("Costs_PasteValues_C_Fees") = arrC_Fees_Grid
'END C Fees Grid


'START Maitenance Grid
Dim arrMaintenance_Costs As Variant

arrMaintenance_Costs = Range("Costs_Maintenance_Costs")

ReDim arrMaintenance_Costs_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
       For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrMaintenance_Costs_Grid(I, J) = arrMaintenance_Costs(I, 1) * arrResolution__Max_Recovery_Grid(I, J) * arrCosts__Physical_Possession(I, J) / 4
           
            Next J
       Next I
Range("Costs_PasteValues_Maitenance") = arrMaintenance_Costs_Grid
'END Maitenance Grid

'START Local Tax Grid
Dim arrLocal_Tax, arrUsed_cad_Value, arrUrban_Tax As Variant

arrLocal_Tax = Range("Costs_Local_Tax")
arrUsed_cad_Value = Range("Costs_Used_cad_Value")
arrUrban_Tax = Range("Costs_Urban_Tax")

ReDim arrLocal_Tax_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
    For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrLocal_Tax_Grid(I, J) = arrLocal_Tax(I, 1) * arrUsed_cad_Value(I, 1) * arrCosts__Max_Legal_Possession(I, J) * (arrUrban_Tax(I, 1) = "Y") / 4 * arrApplicable_Inflation_Multiplier(I, J)
           
            Next J
       Next I
Range("Costs_PasteValues_Local_Tax") = arrLocal_Tax_Grid
'END Local Tax Grid

'START REO S Costs Grid
Dim arrS_Costs As Variant

arrS_Costs = Range("Costs_S_Costs")

ReDim arrS_Costs_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
    For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrS_Costs_Grid(I, J) = arrS_Costs(I, 1) / 4 * arrCosts__Max_Legal_Possession(I, J) * arrApplicable_Inflation_Multiplier(I, J)
           
            Next J
       Next I
Range("Costs_PasteValues_REO_S_Cost") = arrS_Costs_Grid
'END REO S Costs Grid

'START Suc Fee Grid
Dim arrSuc_Fee As Variant

arrSuc_Fee = Range("Costs_Suc_Fee")


ReDim arrSuc_Fee_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrSuc_Fee_Grid(I, J) = -arrSuc_Fee(I, 1) * arrTotal_Income_Grid(I, J)
           
            Next J
       Next I
Range("Costs_PasteValues_Suc_Fee") = arrSuc_Fee_Grid
'END Suc Fee Grid

'START Legal S Grid
Dim arrLegal_S, arrGross_Total_Income

arrLegal_S = Range("Costs_Legal_S")
arrGross_Total_Income = Range("Costs_Gross_Total_Income")

ReDim arrLegal_S_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
    For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrLegal_S_Grid(I, J) = arrLegal_S(I, 1) / 4 * arrGross_Total_Income(I, 1) * (arrQuarters(1, J) <= arrN_Expenses_To_Quarter(I, 1))
           
            Next J
       Next I
Range("Costs_PasteValues_Legal_S") = arrLegal_S_Grid
'END Legal S Grid

'START Sol S Grid
Dim arrSol_S, arrN_Expenses_From_Quarter As Variant

arrSol_S = Range("Costs_Sol_S")
'arrAttributable_OPB = Range("Resolution_Attributable_OPB")
arrN_Expenses_From_Quarter = Range("Nexpenses_From")

ReDim arrSol_S_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrSol_S_Grid(I, J) = -arrSol_S(I, 1) / 4 * arrAttributable_OPB(I, 1) * (arrQuarters(1, J) >= arrN_Expenses_From_Quarter(I, 1)) * (arrQuarters(1, J) <= arrN_Expenses_To_Quarter(I, 1))
           
            Next J
       Next I
Range("Costs_PasteValues_Sol_S") = arrSol_S_Grid
'END Sol S Grid


'START Boarding Fee Grid
Dim arrBoarding_Fee As Variant

arrBoarding_Fee = Range("Costs_Boarding_Fee")

ReDim arrBoarding_Fee_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrBoarding_Fee_Grid(I, J) = arrBoarding_Fee(I, 1) * arrAsset_Recovered(I, J) * arrApplicable_Inflation_Multiplier(I, J)
           
            Next J
       Next I
Range("Costs_PasteValues_Boarding_Fee") = arrBoarding_Fee_Grid
'END Boarding Fee Grid

'START VAT Recoverable Amount Grid AVQ:AXD
Dim Notary_Costs_Flag, bro_Fee_Flag, Outstanding_Capex_for_WIP_Flag, Soft_Refurb_Costs_Flag, Marketing_Costs_Flag, Insurance_Flag, C_Fees_Flag, Maintenance_Flag, S_Costs_Flag, Suc_Fee_Flag, Legal_S_Flag, Sol_S_Flag, Boarding_Fee_Flag As Long, VAT_Sp_Lux, arrVAT_Recovery_Flag As Variant

Notary_Costs_Flag = Range("Costs_Notary_Costs_Flag")
bro_Fee_Flag = Range("Costs_bro_Fee_Flag")
Outstanding_Capex_for_WIP_Flag = Range("Costs_Outstanding_Capex_for_WIP_Flag")
Soft_Refurb_Costs_Flag = Range("Costs_Soft_Refurb_Costs_Flag")
Marketing_Costs_Flag = Range("Costs_Marketing_Costs_Flag")
Insurance_Flag = Range("Costs_Insurance_Flag")
C_Fees_Flag = Range("Costs_C_Fees_Flag")
Maintenance_Flag = Range("Costs_Maintenance_Flag")
S_Costs_Flag = Range("Costs_S_Costs_Flag")
Suc_Fee_Flag = Range("Costs_Suc_Fee_Flag")
Legal_S_Flag = Range("Costs_Legal_S_Flag")
Sol_S_Flag = Range("Costs_Sol_S_Flag")
Boarding_Fee_Flag = Range("Costs_Boarding_Fee_Flag")
VAT_Sp_Lux = Range("N_CF_VAT_Sp_Lux")
arrVAT_Recovery_Flag = Range("Costs_VAT_Recovery_Flag")

ReDim arrVAT_Recoverable_Amount_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrVAT_Recoverable_Amount_Grid(I, J) = -(arrNotary_Costs_Grid(I, J) * Notary_Costs_Flag + arrbro_Fee_Grid(I, J) * bro_Fee_Flag + arrOutstanding_Capex_for_WIP_Grid(I, J) * Outstanding_Capex_for_WIP_Flag + arrSoft_Refurb_Costs_Grid(I, J) * Soft_Refurb_Costs_Flag + arrMarketing_Costs_Grid(I, J) * Marketing_Costs_Flag + arrInsurance_Costs_Grid(I, J) * Insurance_Flag + arrC_Fees_Grid(I, J) * C_Fees_Flag + arrMaintenance_Costs_Grid(I, J) * Maintenance_Flag + arrS_Costs_Grid(I, J) * S_Costs_Flag + arrSuc_Fee_Grid(I, J) * Suc_Fee_Flag + arrLegal_S_Grid(I, J) * Legal_S_Flag + arrSol_S_Grid(I, J) * Sol_S_Flag + arrBoarding_Fee_Grid(I, J) * Boarding_Fee_Flag) / (1 + VAT_Sp_Lux) * VAT_Sp_Lux * arrVAT_Recovery_Flag(I, 1)
           
            Next J
       Next I
Range("Costs_PasteValues_VAT_Recoverable_Amount") = arrVAT_Recoverable_Amount_Grid
'END VAT Recoverable Amount Grid

'START VAT Recovery Grid FIRST AVQ:AXD
Dim VAT_Recovery_Qrts As Variant

VAT_Recovery_Qrts = Range("N_CF_VAT_Recovery_Quarters")

ReDim arrVAT_Recovery_Grid_FIRST(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
   
            If J - Min(VAT_Recovery_Qrts, arrQuarters(1, J)) > LBound(arrQuarters, 2) Then
                arrVAT_Recovery_Grid_FIRST(I, J) = arrVAT_Recoverable_Amount_Grid(I, J - Min(VAT_Recovery_Qrts, arrQuarters(1, J)))
                    Else
                        arrVAT_Recovery_Grid_FIRST(I, J) = 0
                    End If
            Next J
       Next I
Range("Costs_PasteValues_VAT_Recovery_FIRST") = arrVAT_Recovery_Grid_FIRST
'END VAT Recovery Grid AVQ:AXD

'START Postponement bro Fee Savings Grid
Dim arrPostponement_bro_Fee As Variant

arrPostponement_bro_Fee = Range("Costs_Postponement_bro_Fee")

ReDim arrPostponement_bro_Fee_Savings_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrPostponement_bro_Fee_Savings_Grid(I, J) = arrIncome__Disposal_Repossessed_Assets_Grid(I, J) * arrPostponement_bro_Fee(I, 1)
         
          Next J
       Next I
Range("Costs_PasteValues_Postponement_bro_Fee_Savings") = arrPostponement_bro_Fee_Savings_Grid
'END Postponement bro Fee Savings Grid


'START Postponement Suc Fee Savings Grid
Dim arrPostponement_Liquidation_Fee As Variant

arrPostponement_Liquidation_Fee = Range("Costs_Postponement_Liquidation_Fee")

ReDim arrPostponement_Suc_Fee_Savings_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrPostponement_Suc_Fee_Savings_Grid(I, J) = arrTotal_Income_Grid(I, J) * arrPostponement_Liquidation_Fee(I, 1)
         
          Next J
       Next I
Range("Costs_PasteValues_Postponement_Suc_Fee_Savings") = arrPostponement_Suc_Fee_Savings_Grid
'END Postponement Suc Fee Savings Grid


'START Postponement bro Fee Disburse Grid
Dim arrStandardised_Status, IRR_Unlev_CF, Postponement_bro_Fee_IRR_Threshold As Variant

arrStandardised_Status = Range("All_Assets_Standardised_Status")
IRR_Unlev_CF = Range("N_CF_IRR_Unlev_CF")
Postponement_bro_Fee_IRR_Threshold = Range("N_CF_Postponement_bro_Fee_IRR_Threshold")


'summing Postponement Suc fee savings - START
ReDim arrPostponement_Suc_Fee_Savings_Sum(1 To UBound(arrNumber_of_Assets, 1), 1 To 1)
intSum = 0
For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
intSum = intSum + arrPostponement_Suc_Fee_Savings_Grid(I, J)
arrPostponement_Suc_Fee_Savings_Sum(I, 1) = intSum
Next J
intSum = 0
Next I
'summing Postponement Suc fee savings - END

'summing Postponement bro fee savings - START
ReDim arrPostponement_bro_Fee_Savings_Sum(1 To UBound(arrNumber_of_Assets, 1), 1 To 1)
intSum = 0
For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
intSum = intSum + arrPostponement_bro_Fee_Savings_Grid(I, J)
arrPostponement_bro_Fee_Savings_Sum(I, 1) = intSum
Next J
intSum = 0
Next I
'summing Postponement bro fee savings - END

Dim Max_Ifs_for_PB_Fee As Variant

Max_Ifs_for_PB_Fee = Range("Max_Ifs_for_PB_Fee")

ReDim arrPostponement_bro_Fee_Disburse_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
  
        If IRR_Unlev_CF >= Postponement_bro_Fee_IRR_Threshold Then
                arrPostponement_bro_Fee_Disburse_Grid(I, J) = arrPostponement_bro_Fee_Savings_Sum(I, 1) * (arrQuarters(1, J) = Max_Ifs_for_PB_Fee)
            Else
                arrPostponement_bro_Fee_Disburse_Grid(I, J) = 0
            End If
           
            Next J
       Next I

Range("Costs_PasteValues_Postponement_bro_Fee_Disburse") = arrPostponement_bro_Fee_Disburse_Grid
'END Postponement bro Fee Disburse Grid

'summing Postponement Suc fee savings - START
ReDim arrPostponement_Suc_Fee_Savings_Sum(1 To UBound(arrNumber_of_Assets, 1), 1 To 1)
intSum = 0
For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
intSum = intSum + arrPostponement_Suc_Fee_Savings_Grid(I, J)
arrPostponement_Suc_Fee_Savings_Sum(I, 1) = intSum
Next J
intSum = 0
Next I
'summing Postponement Suc fee savings - END

'START Postponement Suc Fee Disburse Grid
Dim Postponement_Suc_Fee_IRR_Threshold As Variant
Postponement_Suc_Fee_IRR_Threshold = Range("N_CF_Postponement_Suc_Fee_IRR_Threshold")

ReDim arrPostponement_Suc_Fee_Disburse_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
  
        If IRR_Unlev_CF >= Postponement_Suc_Fee_IRR_Threshold Then
                arrPostponement_Suc_Fee_Disburse_Grid(I, J) = arrPostponement_Suc_Fee_Savings_Sum(I, 1) * (arrQuarters(1, J) = Max_Ifs_for_PB_Fee)
                    Else
                        arrPostponement_Suc_Fee_Disburse_Grid(I, J) = 0
                    End If
           
            Next J
       Next I
Range("Costs_PasteValues_Postponement_Suc_Fee_Disburse") = arrPostponement_Suc_Fee_Disburse_Grid
'END  Postponement Suc Fee Disburse Grid

'START Additional Suc Fee Disbursement
Dim Postponement_Additional_Suc_Fee_IRR_Threshold, Additional_Suc_Fee As Variant
Postponement_Additional_Suc_Fee_IRR_Threshold = Range("N_CF_Postponement_Additional_Suc_Fee_IRR_Threshold")
Additional_Suc_Fee = Range("Costs_Additional_Suc_Fee")

ReDim arrAdditional_Suc_Fee_Disburse_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
  
        If IRR_Unlev_CF >= Postponement_Additional_Suc_Fee_IRR_Threshold Then
                arrAdditional_Suc_Fee_Disburse_Grid(I, J) = arrGross_Total_Income(I, 1) * Additional_Suc_Fee(I, 1) * (arrQuarters(1, J) = Max_Ifs_for_PB_Fee)
            Else
                arrAdditional_Suc_Fee_Disburse_Grid(I, J) = 0
            End If
           
            Next J
       Next I
      
Range("Costs_PasteValues_Additional_Suc_Fee_Disbursement") = arrAdditional_Suc_Fee_Disburse_Grid
'END

'START
Dim Additional_Suc_Fee_Flag As Variant
Additional_Suc_Fee_Flag = Range("Costs_Additional_Suc_Fee_Flag")

ReDim arrVAT_Recoverable_Amount_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrVAT_Recoverable_Amount_Grid(I, J) = -(arrPostponement_bro_Fee_Savings_Grid(I, J) * bro_Fee_Flag + arrPostponement_Suc_Fee_Savings_Grid(I, J) * Suc_Fee_Flag + _
                 arrPostponement_bro_Fee_Disburse_Grid(I, J) * bro_Fee_Flag + arrPostponement_Suc_Fee_Disburse_Grid(I, J) * Suc_Fee_Flag + arrAdditional_Suc_Fee_Disburse_Grid(I, J) * Additional_Suc_Fee_Flag) _
                 / (1 + VAT_Sp_Lux) * VAT_Sp_Lux * arrVAT_Recovery_Flag(I, 1)
            Next J
       Next I
      
Range("Costs_PasteValues_VAT_Recoverable_Amount") = arrVAT_Recoverable_Amount_Grid
'END

'START VAT Recovery Grid


ReDim arrVAT_Recovery_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
    If J - Min(VAT_Recovery_Qrts, arrQuarters(1, J)) > LBound(arrQuarters, 2) Then
        arrVAT_Recovery_Grid(I, J) = arrVAT_Recoverable_Amount_Grid(I, J - Min(VAT_Recovery_Qrts, arrQuarters(1, J)))
    Else
        arrVAT_Recovery_Grid(I, J) = 0
    End If
          Next J
       Next I
               
Range("Costs_PasteValues_VAT_Recovery") = arrVAT_Recovery_Grid
      
'END

'START Operational Costs (excl acquisition or repossession costs) Grid

ReDim arrOperational_Costs_Excluding_Acq_or_Reposs_Costs(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
  
                arrOperational_Costs_Excluding_Acq_or_Reposs_Costs(I, J) = arrbro_Fee_Grid(I, J) + arrCash_for_Keys_Grid(I, J) + arrIIVTNU_at_Sale_Grid(I, J) + arrOutstanding_Capex_for_WIP_Grid(I, J) + arrSoft_Refurb_Costs_Grid(I, J) + arrMarketing_Costs_Grid(I, J) + arrInsurance_Costs_Grid(I, J) + arrC_Fees_Grid(I, J) + arrMaintenance_Costs_Grid(I, J) + arrLocal_Tax_Grid(I, J) + arrS_Costs_Grid(I, J) + arrSuc_Fee_Grid(I, J) + arrLegal_S_Grid(I, J) + arrSol_S_Grid(I, J) + arrBoarding_Fee_Grid(I, J) + arrVAT_Recovery_Grid(I, J)
         
          Next J
       Next I
Range("Costs_PasteValues_Operational_Costs_Excl") = arrOperational_Costs_Excluding_Acq_or_Reposs_Costs
'END Operational Costs (excl acquisition or repossession costs) Grid


Range("_11toLastOperational_Costs_Excluding_Acq_or_Reposs_Costs") = arrOperational_Costs_Excluding_Acq_or_Reposs_Costs

'(All assets SHEET) START
ReDim arrUnlevered_Cashflow_Excluding_PP(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
  
                arrUnlevered_Cashflow_Excluding_PP(I, J) = arrTotal_Income_Grid(I, J) + arrCosts__Taxes_at_Repossession_Grid(I, J) + arrCosts__Other_Repossession_Costs(I, J) + arrNotary_Costs_Grid(I, J) + arrOperational_Costs_Excluding_Acq_or_Reposs_Costs(I, J)
          Next J
       Next I
Range("All_Assets_PasteValues_Net_Cash_Flow_Before_Interest") = arrUnlevered_Cashflow_Excluding_PP
Range("_11toLast_Unlevered_Cashflow_Excluding_PP") = arrUnlevered_Cashflow_Excluding_PP


'Sumif_Max_Recovery_Amount - START

'Interest START
Dim arrSUMIF_Max_Recovery_Amount, arrN_CF_Interest, arrPasteValues_Interest As Variant
arrSUMIF_Max_Recovery_Amount = Range("Resolution_SUMIF_Max_Recovery_Amount")
arrN_CF_Interest = Range("N_CF_Interest")
arrPasteValues_Interest = Range("All_Assets_PasteValues_Interest")

ReDim arrAll_Assets_Interest_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
  
                arrAll_Assets_Interest_Grid(I, J) = -arrN_CF_Interest(1, J) * arrResolution__Max_Recovery_Grid(I, J) * (arrQuarters(1, J) <= arrPhysical_Possession_Expenses_To_Quarter(I, 1)) / Max(arrSUMIF_Max_Recovery_Amount(1, J), 1)
          Next J
       Next I
      
Range("All_Assets_PasteValues_Interest") = arrAll_Assets_Interest_Grid
      
'Interest START

'CF after interest START
ReDim arrAll_Assets_Net_CF_After_Interest_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
  
                arrAll_Assets_Net_CF_After_Interest_Grid(I, J) = arrUnlevered_Cashflow_Excluding_PP(I, J) + arrAll_Assets_Interest_Grid(I, J)
          Next J
       Next I
      
Range("All_Assets_PasteValues_Net_CF_After_Interest") = arrAll_Assets_Net_CF_After_Interest_Grid
'CF after interest END


'TAX sheet Start
'Building Depreciation start
Dim arrAward_Value_In_Use, arrApplicable_Depreciation_Rate As Variant

arrAward_Value_In_Use = Range("Costs_Award_Value_In_Use")
arrApplicable_Depreciation_Rate = Range("Tax_Applicable_Depreciation_Rate")

ReDim arrTax_Building_Depreciation_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
        For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
  
                arrTax_Building_Depreciation_Grid(I, J) = arrApplicable_Depreciation_Rate(I, 1) / 4 * arrAward_Value_In_Use(I, 1) * (arrQuarters(1, J) > arrN_Expenses_To_Quarter(I, 1)) * (arrQuarters(1, J) <= arrPhysical_Possession_Expenses_To_Quarter(I, 1))
          Next J
       Next I

Range("Tax_PasteValues_Building_Depreciation") = arrTax_Building_Depreciation_Grid
'Building Depreciation END
'Start
Dim arrTotal_Taxes_At_Acq, arrDD_Cost, arrApportioned_Acq_Price_In_Use, Land_BV_For_REO_result As Variant

arrTotal_Taxes_At_Acq = Range("Costs_Total_Taxes_At_Acq")
arrDD_Cost = Range("Debt_DD_Cost")
arrApportioned_Acq_Price_In_Use = Range("Debt_Apportioned_Acq_Price_In_Use")

ReDim arrAllocation_of_Land_Book_Value_For_REOs_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
        For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
  
                If arrAll_Assets_SubPortfolio(I, 1) = "REO" Then
                    Land_BV_For_REO_result = (-arrTotal_Taxes_At_Acq(I, 1) + arrDD_Cost(I, 1) + arrApportioned_Acq_Price_In_Use(I, 1)) * (arrStandardised_Status(I, 1) = 1) * arrCosts__Disposal_Schedule(I, J)
                Else: Land_BV_For_REO_result = 0
                End If
            
             arrAllocation_of_Land_Book_Value_For_REOs_Grid(I, J) = Land_BV_For_REO_result
         
          Next J
       Next I
Range("Tax_PasteValues_Land_BV_for_REOs") = arrAllocation_of_Land_Book_Value_For_REOs_Grid
'end
'TAX sheet END

'DEBT SHEET START
Dim N_CF_ALA, arrApportioned_Debt_In_Use As Variant

N_CF_ALA = Range("N_CF_ALA")
arrApportioned_Debt_In_Use = Range("Debt_Apportioned_Debt_In_Use")


ReDim arrDebt_ALA_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
        For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
  
                arrDebt_ALA_Grid(I, J) = N_CF_ALA * -arrApportioned_Debt_In_Use(I, 1) * arrCosts__Disposal_Schedule(I, J)
          Next J
       Next I

Range("Debt_PasteValues_ALA") = arrDebt_ALA_Grid


'DEBT SHEET END

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[offthelip]

you have got 13 loops where you are looping around exactly the same thing in all the loops:

[COLOR=rgb(184, 49, 47)]For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)[/COLOR]
    For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
        arrAsset_Recovered(I, J) = (arrQuarters(1, J) = arrN_Expenses_To_Quarter(I, 1)) * -1
 
    Next J
[COLOR=rgb(184, 49, 47)]Next I[/COLOR]

I think you might find it is a bit quicker if you did all of these loops with one loop that went round them all . Certainly worth trying

 

[Silverjman]

So even thought they refer to 13 different grids in the model because they have the same parameters I can do them in the same loop?

OK interesting I will look into it.

 

[Silverjman]

Ahh OK, I now better get what you are saying, I just effectively more my range pastes to the end and only have on "Next J Next I" instead of 13. interesting

 

[offthelip]

The only time you can't do this is if you are picking up the data via calculation on the worksheet in between executing any of the intermediate stages.