# Spreading a given cost of X\$'s over Z years Starting at year Y

#### STCrosstown

##### New Member
So I am trying to create a formula to equally spread a given cost X starting at year Y and spread over Z amount of years. Ex

 Cost X Start Year Y Spread over Z years 2019 2020 2021 2022 2023 2024 2025 1000 2020 5 0 200 200 200 200 200 0

<tbody>
</tbody>

Any help is much appreciated

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi,

Change the 0 (zero) to "" if you want Blank rather than 0 to show:

Book1
ABCDEFGHIJ
1Cost XStart Year YSpread over Z years2019202020212022202320242025
210002020502002002002002000
Sheet509
Cell Formulas
RangeFormula
D2=IF(OR(D1<\$B2,D1>=\$B2+\$C2),0,\$A2/\$C2)

Formula copied across.

Thanks,

What if I want the cost to occur once ever 5 years?

Then use D3 formula copied across:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXY
1Cost XStart Year YSpread over Z years2019202020212022202320242025202620272028202920302031203220332034203520362037203820392040
210002020502002002002002000
302000000200000020000002000000200
Sheet509
Cell Formulas
RangeFormula
D2=IF(OR(D1<\$B2,D1>=\$B2+\$C2),0,\$A2/\$C2)
D3=IF(OR(D1<\$B2,D1<>MROUND(D1,\$C2),D1>=\$B2+\$C2^2),0,\$A2/\$C2)

Last edited:
Ignore my D3 formula above, use this one:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXY
1Cost XStart Year YSpread over Z years2019202020212022202320242025202620272028202920302031203220332034203520362037203820392040
210002020502002002002002000
302000000200000020000002000000200
Sheet509
Cell Formulas
RangeFormula
D2=IF(OR(D1<\$B2,D1>=\$B2+\$C2),0,\$A2/\$C2)
D3=IF(OR(D1<\$B2,MOD(D1-\$B2,\$C2)<>0,D1>=\$B2+\$C2^2),0,\$A2/\$C2)

Replies
22
Views
455
Replies
6
Views
414
Replies
6
Views
299
Replies
6
Views
100
Replies
8
Views
437

1,203,240
Messages
6,054,317
Members
444,717
Latest member
melindanegron

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back