Calculating the average annual cost over 5 years with multiple durations

It would help immensely to see your file so we understand how your data is organized, what is the frequency of your cost data. What does "each year time point" mean? Show us where you would like the results displayed.

Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

I have everything I need but time.

I am reviewing your file and your description above and do not understand what you are trying to accomplish. First, it would help if you gave a little more explanation Second, you have a sheet FULL of formulas. Which formula specifically (in which cell) do you need help with? Or do you need a new formula that does not yet exist?

You have a formula in your file labelled "Average Annual Cost per unit" (G51:G55). This formula calculates

(unit cost x # units for market) / (number of years) / (# units for market)

You are multiplying by number of units then dividing it back out, so this is equivalent to

(unit cost) / (number of years)

I cannot think of any business value for this calculation unless you are trying to allocate expenses over those years for accounting purposes. But if that's what you are doing, you wouldn't do it with unit costs, you would do it with total cost.

As for your question, this formula already uses the number of years, so your goal of "making the calculation flexible to the duration the product lasts, so if it goes from a 3 year duration to a 4 year duration" already seems to be taken into account.

Your formulas that calculate the "Actual Cost" over 5 years (D59:H63) don't make any sense to me either. The second year is 1/2 the first year; the third year is 1/3; the fourth year is 1/2; and the fifth year is 2/5. I can't see the logic to that. What is the meaning of these yearly figures? "I want to be able to see the cost at Year 1, Year 2, Year 3, Year 4 and Year 5." Do you mean that these yearly figures should taking into account the costs calculated above, or that the cost averaging should taking into account these yearly figures?