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

Silverjman

Board Regular
Joined
Mar 19, 2014
Messages
110
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
VBA Code:
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.



VBA Code:
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
 
Last edited by a moderator:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
you have got 13 loops where you are looping around exactly the same thing in all the loops:
VBA Code:
[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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top