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).

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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?