Getting til Net Book Value equals 0

[CFuser87]

I'm currently trying to transform the data to calculate depreciation, so it will loop until the Ending Net Book Value is 0 or close to it. I saw people using multi-use formula, but that was with periods already present. Not sure if this would require a macro instead.

Assumptions being made in the dataset 

1. Amortization remains the same throughout the years

2. Ending Net Book Value = Beg Net Book Value - Amortization

Currently, the data is the following

Year	Asset	Beg Net Book Value	Amortization	End Net Book Value
2034	Pencil	5000	500	4500
2034	Eraser	2000	400	1600
2034	Mouse	1000	1000	0

Dataset to be transformed to 

Year  Asset  Beg NBV Amortization End NBV
2034 Pencil 5000 500 4500
2035 Pencil 4500 500 4000
2036 Pencil 4000 500 3500
2037 Pencil 3500 500 3000
2038 Pencil 3000 500 2500
2039 Pencil 2500 500 2000
2040 Pencil 2000 500 1500
2041 Pencil 1500 500 1000
2042 Pencil 1000 500 500
2043 Pencil 500 500 0
2034 Eraser 2000 400 1600
2035 Eraser 1600 500 1100
2036 Eraser 1100 500 600
2037 Eraser 600 500 100
2038 Eraser 100 100 0
2034 Mouse 1000 1000 0

Thanks in advance!

[alexnajm · Accepted solution]

This should work! I am not sure why your Eraser suddenly changes from 400 to 500, so I assumed it should stay as the value specified (400)

[CFuser87]

My bad, I was just trying to create sample data. The solution works thanks!

[alexnajm]

No worries - glad it worked @CFuser87 !

[binuacs]

@CFuser87 using batch macro

[CFuser87]

Thanks for showing it using the batch macro @binuacs!

@alexnajm, I just realized in the solution provided, the year remains constant. Would there be a way to add +1 to the year, for each additional amortization year?

[alexnajm]

Just a Multi Row Formula to add 1 to the year would do the trick @CFuser87 - one sec

[alexnajm]

here you go!

[CFuser87]

Amazing, thank you again! Just learned about the new Tile tool!