Untitled

Function NominalRet(EndYear, Data)
' Projected growth rates of 4 asset classes over a 30-year period, starting 2021, are given.
' User specifies an End Year for portfolio growth analysis.
' Calculates the nominal and real growth of a $1 investment in each asset classes
' Also calculates the corresponding annualised returns of each asset class.
' Investment value shown is as on 1st January of the year

' Set period of investment
Dim StartYr, EndYr As Integer
StartYr = 2021
EndYear = Range("P4").Value '2049 for this assessement

'EndYr = Cells(3, 10).Value ??
EndYr = EndYear

' Set model intput range and starting year  '' can also just refer to it as 'projected' for efficiency
Dim InpRowStart, InpRowEnd, InpStartYr As Integer
InpRowStart = 1
InpRowEnd = 31
InpStartYr = 2021 ' can use same value for Start year since investment start = input start date

' Set model output starting row
Dim OutRowStart() As Double
ReDim OutRowStart(1, 4) ' output is only the green cells so we can set dimensions
OutRowStart = 39 '' quite useless unless refering directly to cell


'' MODEL CALCULATIONS !!!
' Calculate number of years the investment will be for
Dim OutYrs As Integer
OutYrs = EndYr - StartYr  ' dont need + 1

' Calculate years of input data ' some overlap of variables , this one we can just + 1 from above section
Dim InpYrs As Integer
InpYrs = InpRowEnd - InpRowStart + 1

' Read input data to array ' could count number of assets for analysis of more assets
Dim RowNum, ColNum As Integer
Dim InpRates(2020 To 2049, 1 To 5)

For RowNum = 1 To InpYrs Step 2
    For ColNum = 1 To 3
        InpRates(InpStartYr - 1 + RowNum, ColNum) = Data.Cells(RowNum, ColNum).Value
    Next
    For ColNum = 1 To 5
        npRates(InpStartYr - 1 + RowNum, ColNum) = Data.Cells(RowNum, ColNum).Value
    Next
Next

' Set intial investments of $1 in each asset class
Dim OutGrowthNom(2020 To 3010, 1 To 5) 'Nominal investment value
Dim OutGrowthReal(2020 To 3010, 1 To 5) 'Real investment value

For ColNum = 1 To 2
    OutGrowthNom(StartYr, ColNum) = 100
    OutGrowthReal(StartYr, ColNum) = 100
Next

For ColNum = 3 To 5
    OutGrowthNom(StartYr, ColNum) = 100
    OutGrowthReal(StartYr, ColNum) = 100
Next

' Calculate nominal investment growth and cumulative inflation
For RowNum = StartYr + 1 To EndYr + 1
    For ColNum = 1 To 5 Step 2
        OutGrowthNom(RowNum, ColNum) = OutGrowthNom(RowNum - 1, ColNum) * (1 + InpRates(RowNum - 1, ColNum))
    Next
Next

' Calculate real investment growth by adjusting nominal growth
' Use cumulated inflation data for adjustment
For RowNum = StartYr + 1 To EndYr + 1
    For ColNum = 1 To 4
        OutGrowthReal(RowNum, ColNum) = OutGrowthNom(RowNum, ColNum) / OutGrowthNom(RowNum, 5)
    Next
Next

' Calculate annualised returns for each asset class and output to specified cells
Dim AnnRetNom(1 To 4)

For ColNum = 1 To 4
    AnnRetNom(ColNum) = OutGrowthNom(EndYr + 1, ColNum) ^ (1 / OutYrs) - 1
Next

NominalRet = AnnRetNom()

End Function