Hello,
I am trying to calculate the inflation-adjusted trailing returns for my financial portfolio, but I don't know how to adjust the formula (specifically the denominator) for inflation when the look-back period bleeds into the previous year(s). For example, suppose the date is February 1, 2023 and I want to determine the return over the last 3 months. This means that the starting value would be from November 1, 2022. In 2022, the monthly inflation differs from that of 2023. Let's say the average monthly inflation was 0.75%. In 2023, let's say it is 0.50%. How can I adjust my return for the inflation that happened in the last 2 months of 2022 and the first month of 2023? The denominator would have to pull from the 'Annual Inflation Rate' 0.75% and 0.50%:
8.56% / (1 + 0.75%)^2 * (1 + 0.50%)
I wrote the formula for a simple example in cell E3 where the assumption is that going back 3 months keeps you in the same fiscal year of 2023. The answer would resolve to:
8.56% / (1+ 0.50%)^3
Obviously, depending on the date and how far you look back (e.g. 5 years back), inflation from previous years become relevant. The second sheet (titled Annual Inflation Rate) contains the average monthly inflation rates.
Thanks for reading this.
Bookmarks