Arrgghh! This is now the 3rd time I've tried to post this without losing it to a session time-out.
Forgive me if I'm a little surly.
Somewhat. It provided more context in which to reread your initial posting. I think I understand. But I'm still not sure because of some terminology. Let me paraphrase my understanding of the problem. If I'm very wrong, at least this presentation might serve as a paradigm for any follow-up you must post to correct my misstatement of the problem. Let's have a go at it (again)....
You have an initial inventory, Inv0, which you deplete each year. The first year's usage is Use1, and you deplete 12% more each year. So Use2 = Use1*1.12; Use3 = Use2*1.12 = Use1*1.12^2; etc. You want to know in how many years
the initial inventory will be depleted completely. Good so far?
Let's create a concrete example and solve it manually. I find that helps me -- and others -- understand the mechanics of the problem and the expected result.
Suppose the initial inventory is 1,000,000, and the first year's usage is 100,000. We might set up the following solution in Excel.
| A
| B
| C
|
1
| initInventory
| 1,000,000
|
|
2
| initUsage
| -100,000
|
|
3
| depleteRate
| 12.00%
|
|
4
|
|
|
|
5
| Year
| Use
| Inventory
|
6
|
|
| 1,000,000.00
|
7
| 1
| -100,000.00
| 900,000.00
|
8
| 2
| -112,000.00
| 788,000.00
|
9
| 3
| -125,440.00
| 662,560.00
|
10
| 4
| -140,492.80
| 522,067.20
|
11
| 5
| -157,351.94
| 364,715.26
|
12
| 6
| -176,234.17
| 188,481.10
|
13
| 7
| -197,382.27
| -8,901.17
|
<TBODY>
</TBODY>
The formulas are:
A7: 1
A8: =A7+1
B7: =B2
B8: =B7*(1+$B$3)
C6: =B1
C7: =C6+B7
C8: =C7+B8
Copy A8:C8 down until column C is negative (A13:C13). We can see the expected solution is between 6 and 7 years.
Apparently, your intuition suggested that this is an "annuity" problem, and we might be able to use NPER to solve the problem without needing a table (or many tables in your case of many inventories).
And you are right! The NPER solution is as follows, which returns about 6.96:
=NPER(B3,B2,0,B1)
You might be surprised that B1 is the 4th parameter (FV) instead of the 3rd parameter (PV). At least, I was!
Well, I cheated! I derived the NPER solution by developing an algebraic solution and determining how the algebraic elements aligned with the presentation of the "annuity" formula in the PV help page.
Some natty details....
Note that in the manual Excel solution above, usage and inventory are not always integers. Of course, they must be integers in real life.
Therefore, we must remember that the NPER solution is an approximation. My guess is: the larger the NPER result (i.e. the more years), the greater the error in the approximation.
A more accurate model might be implemented using VBA, effectively emulating the manual solution above, but taking real-world constraints into account at each iteration (each year).
Also, note that NPER does not return an integer in this example. Indeed, that will be true most of the time.
You need to decide how to handle that: round down, round up, round to nearest, or use the fractional part to determine a month (again, rounding up, down or to nearest). There is no single "right" answer. It depends on your business requirements. Do you need help with that?
Did the above address all of your problem, even if it misses the mark due to my misunderstandings?
I do not understand what VLOOKUP has to do with it. Is that a nuance of the problem that you need help with: perhaps how to nest NPER and VLOOKUP?
And I do not understand "apply that to an inventory balance with no growth formula". Again, did I overlook some nuance of the problem?
Please respond to these questions. With all the time I invested in this response -- frustration with the UI, not your fault at all -- I would appreciate a little the feedback.
