Total storage cost (similar to financial loan) calculation

Hi Peeps,

Never thought I would need to write on an Excel forum but the day has come.

I have to work out how much storage cost a client will pay depending on the tonnage of their cargo in the warehouse.

The example that I have:

The total tonnage (from here on referred as MT - Metric Tonnes) is 10 000MT.
The storage cost / day is $0.15.
The client removes at a daily rate 400MT and the storage is recalculated according to the tonnage remaining.
The total days the client has cargo in storage is 25. 25 x 400 = 10 000.

I have attached a sheet to make it easier to understand. The total cost over the period of 25 days should be $19,500.00. With FV (future value) calculation I have managed to recreate this cost, but then I have to use a NPER factor of slightly more 23, "=-FV((0.15/365),23.40045,400,10000)"

This could be sheer luck and totally wrong, but in my understanding the NPER should be 25 days?

Anyways, I would appreciate any assistance, my boss is already hot on my heels!!

Kind regards,
J

Nonvolatile formula

Please Login or Register  to view this content.

If the reduction in tonnes are linear, you can solve this algebraically - which results in the following formula

Total cost = Daily rate * (Total tonnage * Days - Uplift rate * (Days -1) * Days / 2)
where Days = Total Tonnage / Uplift Rate

So in your case

Formula:

Please Login or Register  to view this content.