Trigger cost occurence according to defined frequency

[Beino]

Hi,

I am doing a school project and this issue has been messing with my brain over the last two days.

I'm trying to calculate when (the exact year) a cost will occur based on a frequency. To be more specific, I want to look at what year tires for a car needs to be replaced when worned out and then calculate the cost. The cost should be an average for a fleet of cars, where each car is driving different mileages per year (assumed take-rates).

Some assumptions:

Tire life expectancy: 40 000 km
Cost for a new set of tires: 800 EUR

Mileage categories and take-rate (% of customers who drives the distance / year):

40 000 km 5%
35 000 km 2%
30 000 km 5%
25 000 km 11%
20 000 km 24%
15 000 km 51%

The key part of the problem is that I want to highlight the costs in a table so that it becomes visualized exactly what year the cost occurs and what the cost is (example below), but I'm not able to come up with a good idea how to build a formula so that get this:

	Year 1	Y2	Y3	Y4	Y5
40000	cost in EUR if cost should occur	etc.
35000
30000
25000
20000
15000

If anyone could help, I would be VERY happy!

Thanks in advance.

 

[Beino]

And just to clarify, the cars that are driving 40000 km per year amounts to 5% of the entire fleet. This cost, as a result of need to change tires, would occur every year. The cost per year for this mileage category would then be 5% * 800 EUR = 40 EUR.