Calculating Investment Returns Based On Time and Differing Interest Rates

Hi! This is a similar version to a previous question I had asked, however, this one is more understandable because I did not have a full understanding of what I was trying to do previously. Thus, I hope this does not constitute a double posting since, in my opinion, this is a different question and structure. I have three columns:

Column A: Investment ID
Column B: Year
Column C: Investment Value

My years are from 2018-2023. All of Columns A and B are populated, however, the only populated values under Column C are when the year is 2018 since 2019 - 2023 have not yet completed.

Additionally, I have a lookup table of what we expect the % returns to be each year. This is by year and not by account because we are assuming the same yearly returns for each account. The expected return %s vary by year (otherwise I would not be making this post since that would be much simpler). How would I find the expected investment values over the coming years? I am most likely thinking about this wrong and would love any guidance on this task :D

Update:

I managed to make this work by pivoting the year column, running the index that way, then unpivoting the year column. Still curious if there is another way because the advice on this forum has always helped me to think about Excel in new ways

I'm guessing a bit here as you have not shared expected results.

returns.png

I have populated the green shaded cells C6:c13 with the expected aggregate return given actual 2018 returns and expected returns for 2019 and 2020. The C6 formula copied down is:

Formula:

Please Login or Register  to view this content.

This is an array formula. Commit with CTRL-SHIFT-ENTER.

See the attached workbook.

Let me know if this is what you are expecting. If not please provide the layout you are looking for and a couple of expected results so that I can make sure my math is right.

GeoffW283. Thank you for this. However, how would I solve for this in an unsorted data set? My supervisor wants to be able to see the formulas at all times for quality assurance, however, other analysts will likely sort the data in multiple ways that will invalidate these formulas. Any suggestions?

The formula already accommodates Investment IDs being in any order - see A2 & A3 which I had intentionally re-ordered. In fact as far as I can see the A1:C13 table can be sorted any which way and still yield the same result. What am I missing?

Geoff, if you sort by largest to smallest year, the formulas all become errors is what I'm referring to.

OK, I understand now. Looking at it now . . .

Thank you!

No problem, glad to help. Thanks for the feedback and rep

Geoff, my pleasure! Always got to rep if the answer helps. It would be rude otherwise :p