Help with calculating portfolio returns over rolling 30 yr periods

I believe I understand what you want: a single formula that would do the 30yr calculation for a given starting year 1928, 1929, etc.

I cannot think of a formula construct that allows us to copy it down a column. Perhaps someone more clever than I can offer an Excel solution.

I would write the following VBA function (UDF).

Option Explicit

Function totalReturn(ByVal contrib As Double, yr As Long, rHist As Range, rPctStk As Range) As Double

' contrib is inflation-adjusted amount invested at beginning of each year
' yr is year number of first year (1928, 1929, etc) of 30yr period, or up to end of vHist
' vHist(i,1) is year number (1928, 1929, etc)
' vHist(i,2) is inflation rate (i-th yr over prev yr)
' vHist(i,3) is rate of return for stocks
' vHist(i,4) is rate of return for bonds
' vPctStk(i,1) is percent of stock in portfolio; 1-vPctStk(i,1) is percent of bonds
' portRtn is portfolio rate of return: weighted average of rates of return for stocks
'     and bonds, weighted by percent allocation (vPctStk)
' totalReturn is cumulative year-end balance after portfolio rate of return

Dim vHist As Variant, vPctStk As Variant
Dim yr1 As Long, yrN As Long, i As Long, j As Long
Dim portRtn As Double, wf As Object

Set wf = WorksheetFunction
' copy ranges into arrays for more efficient access
vHist = rHist
vPctStk = rPctStk
' look up yr in 1st column of vHist
' yr1 is index of vHist row corresponding to yr (1928, 1929, etc)
' yrN is index of last vHist row corresponding to 30yr  period
'     (or less, if there are less than 29 yrs after yr1
yr1 = wf.Match(yr, wf.Index(vHist, 0, 1), 0)
yrN = yr1 + 29
If yrN > UBound(vHist, 1) Then yrN = UBound(vHist, 1)
' for 1st yr, total return is:
' initial contrib, ignoring 1st yr inflation rate
' then apply portfolio rate of return (weighted average)
portRtn = vHist(yr1, 3) * vPctStk(1, 1) + vHist(yr1, 4) * (1 - vPctStk(1, 1))
totalReturn = contrib * (1 + portRtn)
j = 1
For i = yr1 + 1 To yrN
    ' for each subsequent yr, accumulate total return by:
    ' add contrib, using prev yr contrib times curr yr inflation rate
    ' then apply curr yr portfolio rate of return (weighted average)
    j = j + 1
    contrib = contrib * (1 + vHist(i, 2))
    portRtn = vHist(i, 3) * vPctStk(j, 1) + vHist(i, 4) * (1 - vPctStk(j, 1))
    totalReturn = (totalReturn + contrib) * (1 + portRtn)
Next
End Function

The attached Excel file demonstrates its use. Enter the following formula into M2, then copy down the column:

=totalreturn(1000, B2, $B$2:$E$93, $F$2:$F$31)

For a proof of concept, columns J, K and L demonstrate how I would calculate the year-by-year cumulative total for each of the first three 30yr periods starting in 1928, 1929 and 1930.

Compare column J with your column I. Column N compares each of the totals returned from the VBA function with the end of columns J, K and L.

-----

Some unrelated obvservations....

1. The total return over 30 yrs that you calculate is based on the time-weighted return (TWR or TWRR), not a money-weighted (sic) return like the IRR. IMHO, both terms are misnomers; and at best, they are reversed. But so be it.

2. You are decreasing the %stock by 2.666667%, not 2.667% (sic). Neither is correct. I would decrease by 40%/15 = 8%/3, rather than an approximation of it. Then the final %stock is indeed 50.0000000000001% instead of 49.999995%. (The infinitesimal 0.0000000000001% is an anomaly of binary floating-point arithmetic.) Arguably, using 8%/3 does not make a significant difference in your example. But it might affect other examples. The point is: avoid approximations when you can calculate an exact amount, unless your process mandates an approximation rounded to a some precision. (But in that case, pick one, not two.)

Originally Posted by lc130

What are the values in M? They don't match J-K. I tried to decipher the VBA but failed

Sorry about that. First, I added some comments in the posting here, but I neglected to put them into the Excel file. Second, I have now embellished the comments in both.

So please download the Excel file again. And I hope the comments help you decipher the VBA code.

Essentially, the VBA function emulates the calculations in columns J, K and L, which are examples. Column J emulates your original calculations in column I.

(Note: With column M, columns H:L can be deleted, as well as columns N:O.)

Column M is intended to be the "rolling 30yr total return" that you wanted, I believe.

For example, M2 is the 30yr total return for 1928-1957 (rows 2:31). In a single formula, it emulates the calculations in I2:I31 (and J2:J31). Thus, M2 should equal I31. And it does, as demonstrated in N2.

Likewise, M3 is the 30yr total return for 1929-1958 (rows 3:32). It emulates the calculations in K3:K32. M3 should and does equal K32, as demonstrated in N3.

And M4 is the 30y total return for 1930-1959 (rows 3:33). It emulates the calculations in L4:L33. M4 should and does equal L33, as demonstrated in N4.

M5 is the 30y total return for 1931-1960. M6 is the 30yr total return for 1932-1961. Etc.

Note: M65 is the 29yr total return for 1991-2019 (rows 65:93), not a 30yr total return, simply because there only 29 yrs remaining. Likewise, M66 is the 28yr total return for 1992-2019 (rows 66:93). Etc.

If you would prefer to calculate only 30yr total returns, change the formula in M2 to the following and copy down the column:

=IF(COUNT(B2:B31)=30, totalreturn(1000, B2, $B$2:$E$93, $F$2:$F$31), "")

Thus, M65:M93 will appear blank. (Actually, their value is the null string "".)

Is that more clear? Any questions?

Does the totalReturn VBA function do what you want? If not, what did I misunderstand?

Regardless, is a VBA solution acceptable? Again, I cannot imagine an Excel-only solution. But someone else might.

[.... deleted by me; premature ....]

Originally Posted by lc130

if I wanted to see portfolio returns net of inflation (real returns) would I simply add -vHist(i, 2) to the portRtn line?

Inflation gives me a headache. I defer to the explanation at https://www.investopedia.com/terms/i...ted_return.asp .

The changes are highlighted in red below. See the attached Excel file.

Function totalReturn(ByVal contrib As Double, yr As Long, rHist As Range, rPctStk As Range, Optional adjRtn As Boolean = False) As Double

' contrib is inflation-adjusted amount invested at beginning of each year
' yr is year number of first year (1928, 1929, etc) of 30yr period, or up to end of vHist
' vHist(i,1) is year number (1928, 1929, etc)
' vHist(i,2) is inflation rate (costs of i-th yr over prev yr)
' vHist(i,3) is rate of return for stocks
' vHist(i,4) is rate of return for bonds
' vPctStk(i,1) is percent of stock in portfolio; 1-vPctStk(i,1) is percent of bonds
' portRtn is portfolio rate of return: weighted average of rates of return for stocks
'     and bonds, weighted by percent allocation (vPctStk)
' totalReturn is cumulative year-end balance after portfolio rate of return

Dim vHist As Variant, vPctStk As Variant
Dim yr1 As Long, yrN As Long, i As Long, j As Long, nHist As Long
Dim portRtn As Double
Dim wf As Object

Set wf = WorksheetFunction
' copy ranges into arrays for more efficient access
vHist = rHist
vPctStk = rPctStk
nHist = UBound(vHist, 1)
' look up yr in 1st column of vHist
' yr1 is index of vHist row corresponding to yr (1928, 1929, etc)
' yrN is index of last vHist row corresponding to 30yr  period
'     (or less, if there are less than 29 yrs after yr1
yr1 = wf.Match(yr, wf.Index(vHist, 0, 1), 0)
yrN = yr1 + 29
If yrN > nHist Then yrN = UBound(vHist, 1)
' for 1st yr, total return is:
' initial contrib, ignoring 1st yr inflation rate
' then apply portfolio rate of return (weighted average)
portRtn = vHist(yr1, 3) * vPctStk(1, 1) + vHist(yr1, 4) * (1 - vPctStk(1, 1))
If adjRtn Then portRtn = (1 + portRtn) / (1 + vHist(yr1, 2)) - 1
totalReturn = contrib * (1 + portRtn)
j = 1
For i = yr1 + 1 To yrN
    ' for each subsequent yr, accumulate total return by:
    ' add contrib, using prev yr contrib times curr yr inflation rate
    ' then apply curr yr portfolio rate of return (weighted average)
    j = j + 1
    contrib = contrib * (1 + vHist(i, 2))
    portRtn = vHist(i, 3) * vPctStk(j, 1) + vHist(i, 4) * (1 - vPctStk(j, 1))
    If adjRtn Then portRtn = (1 + portRtn) / (1 + vHist(i, 2)) - 1
    totalReturn = (totalReturn + contrib) * (1 + portRtn)
Next
End Function

I assume that you might want to calculate both the actual and the inflation-adjusted total returns. So I added an optional parameter (adjRtn). It defaults to FALSE, the actual total return.

In the attached Excel file, column K is the rolling 30yr inflation-adjusted total return. Column J is the original (actual) rolling 30yr total return. The formulas are:

J2: =totalreturn(1000, $A2, $A$2:$D$93, I$2:I$31)

K2: =totalreturn(1000, $A2, $A$2:$D$93, I$2:I$31, TRUE)

I include a chart that "compares" the two results.

-----

There is an issue that I struggled with. I decided to implement the easy answer.

The question is: which inflation rate applies to the year-end portfolio rate of return (portRtn)?

The contribution at the beginning of each year is the previous-year contribution adjusted by the current-year inflation rate -- except for the first year, which is not adjusted.

Similarly, in my design, the year-end portfolio rate of return is (optionally) adjusted by the current-year inflation rate -- including the first year.

Arguably, the year-end portfolio rate should be (optionally) adjusted by the next-year inflation rate.

But in that case, we do not have sufficient information to adjust the portfolio rate of return when the last year is 2019.

What do you think?