Help with Formula

[Dunkin]

This is a worksheet where I have a dollar amount that I would enter into a
cell that I want to achieve. (An annual return on my investment.) cell:
(N9)

I have:
1) A cell listing the initial cost of the investment per unit. cell:
(E11)
2) A cell listing the annual return on the investment per unit purchased.
cell: (F11)
3) A cell listing the current cost of the investment per unit. cell: (H11)
--This cell has a formula in it.
=IF(E11>0,((E11)*(1+(G11*0.1))),"N/A")
4) A cell listing the number of units I currently own. cell: (G11)
5) A cell listing the number of units I need to buy to achieve the value in
cell "N9".
--This cell has a formula in it.
=IF(F11>0,ROUNDUP(($N$9/F11),0),"N/A")
6) I would like to enter a formula into cell "M11" that returns the total
cost of my investment to receive the return I entered into cell "N9".

Here is my problem:
1) The cost of the investment increases by 10% per total units purchased
each time a purchase is completed.
2) The maximum number of units that can be purchased in each transaction is
ten.
(All ten units would be purchased at the current unit cost "H11"
then the current cost "H11" would increase.)

EX: Current Cost = $200. I buy 1 unit new Current Cost = $220, I then buy
10 more units at $220 each. This costs me $2,200 and the new Current Cost =
$420.

If I now want to buy 23 more units to achieve the new additional return on
my investment requirement I would have to buy 10 units at $420 (cost $4,200)
and 10 units at $620 (cost $6,200) and then 3 units at $820 (cost $2,460).

Total Cost = $12,860 (The value I want to be returned in cell"M11".)

The new Current Cost would now be $880 for any future purchases.

--Also the number of units being purchased could easily be well over 1,000
units.


Can anyone help me out with this?

Thank You, very much!

 

[Luke M]

The easiest way may be to use an UDF. Open the VBE (Alt+F11), go to Insert -
Module. Paste this is in:

'==========
Function CostGrowth(InitialCost As Double, _
CurrentCost As Double, AmountReq As Integer) As Double

Dim NewPrice As Double, Cost As Double
Dim AmountPur As Integer, CutOff As Integer
AmountPur = 0
NewPrice = CurrentCost
Cost = 0
'Sets what your qty cutoff is
CutOff = 10
'Sets what your rate of growth is
MyRate = 0.1

Do While AmountReq > CutOff
AmountPur = AmountPur + CutOff
Cost = Cost + CutOff * NewPrice
NewPrice = MyRate * CutOff * InitialCost + NewPrice
AmountReq = AmountReq - CutOff
Loop

Cost = Cost + AmountReq * NewPrice
CostGrowth = Cost
End Function
'=============

Then, back in your workbook, you can input your formula using the arguements
of InitialCost, CurrentCost, and AmountReq. In your example, formula would be:
=CostGrowth(200,420,23)
These arguements can be cell references. Note the 2 places in the coding
where you can change the variables controlling rate and cutoff amounts, if
desired.

 

[Dunkin]

Thank You Luke M,

I have not used VBA before. This was a great help. I noticed it's a lot
like Basic I learned back in the days of the Trash80 (TRS80), Commodore & the
Apple IIe.

I did modify what you supplied me with. It worked well but the calculations
were a little off from what I wanted. Without your help I would have ended
up doing the calculations the old fashioned way... paper & pencil.

This is what I ended up with:

'==========
Function CostGrowth(CurrentCost As Double, AmountReq As Integer) As Double

Dim NewPrice As Double, Cost As Double
Dim CutOff As Integer

NewPrice = CurrentCost
Cost = 0
CutOff = 10 'Sets what your qty cutoff is

Do While AmountReq > CutOff
Cost = Cost + CutOff * NewPrice
NewPrice = NewPrice + NewPrice
AmountReq = AmountReq - CutOff
Loop

Cost = Cost + AmountReq * NewPrice
CostGrowth = Cost
End Function
'=============

Thanks again Luke M, I would not have done it without your help.