Attached is an example of what I need to do. Lines 8-13 is how to calculate it out year by year. Line 18 is what I am trying to see if it is possible to do this calculation in a single cell.

Here are the details with cell references:
Initial Investment: $10,000 (D2)
Initial Sales Charge (Year 1 only): 5.75% (D3)
Annual Operating Expenses: 1.44% (D4)
Annual Rate of Return: 5.00% (D5)

How can I use the above to calculate in a single cell the expense projection? I have Year 1, but subsequent years I am having trouble with:

Year one: =((D2-(D2*D3))*D4)+(D2*D3) = $710.72
Year three: I need to be able to compound 3 years on interest and calculate what the expenses are per year and sum that up for a three year total
Year five: same as above for five years
Year ten: same as above for ten years

There has to be a way for excel to do this. I need to do this for 120 portfolio so I am hoping I do not have to do this year by year and sum them up as that would be way too many tabs.

thanks!!

Attached Files

Example.xlsx (11.3 KB, 8 views)