Help. I am brand new to Excel but need to quickly come up with a formula to do the following:

[AF]

Help. I am brand new to Excel, but I need to quickly come up with a
formula to do the following. In any programming language it would be
a simple do while loop, or equivalent, but I can not find out how to
do it in Excel:

Input a Daily Amount, let's say $150 * or call it DA

Multiply it times 365 for a yearly amount

Input a Number of Years, let's say 5 * or call it Y

Multiply the Yearly Amount times the number of years to get a Total
Yearly Amount or call it YA

Input an inflation Rate, let's say 5%* or call it IR

Input a number of Years, let's say 20 * or call it NY

Take the Total Yearly Amount and multiply it by 100% plus the
inflation rate to get an inflated amount. or in a formula (YA x (100
+ IR))

repeat this last calculation once for each of the 20 years.r
In a typical progamming language this would look like this

Counter =1
do while Counter <= NY
YA = (YA x (100 + IR))
Counter = Counter + 1
enddo

I can not find out how to loop or repeat in Excel.

Thanks for any help
Best regards,

Al
http://www.affordablefloridainsurance.com
http://www.americanbestmortgages.com

 

[JE McGimpsey]

One way:

=FV(IR, NY, DA*365,0,0)

One generally doesn't loop in XL - either use functions that loop
internally, or use multiple cells, e.g.:

H1: =YA
H2: =H1*(1+IR)+YA

copy H2 down to H20.

 

[AF]

Let me rephrase this since my previous post looks too wordy.

If something costs me $150 per day, let's call that DailyCost, what
will it cost in 20 years, let's call that TimePeriod, at an inflation
rate of 5%, let's call that InflationRate, per year.

Forget the other parts of the formula. Ther will just confuse the
issue.

If I were to calculate this in a programming language, it would look
like this:


DailyCost=150
TimePeriod=20
InflationRate= 1.05
NewDailyCost = DailyCost

Counter=1 for control purposes

do while Counter <=TimePeriod
NewDailyCost=(NewDailyCost*1.05)
Counter=Counter+1
enddo

show NewDailyCosts

I can not figure out how to do this. I know it is simple, but I can
not find it in the help section.

Thanks for any tips.


Best regards,

Al
http://www.affordablefloridainsurance.com
http://www.americanbestmortgages.com

 

[AF]

One way:

=FV(IR, NY, DA*365,0,0)

One generally doesn't loop in XL - either use functions that loop
internally, or use multiple cells, e.g.:

H1: =YA
H2: =H1*(1+IR)+YA

copy H2 down to H20.

Thanks for your post. I can not use your second method since I have
no idea of the number of times we need to loop. That is one of the
input variables. By the way doing it this second way gives me this if
I jsut calculate the inflated value of $150 multiply by a rate of 1.05
and redoing it 20 times:

$150.00
$157.50
$165.38
$173.64
$182.33
$191.44
$201.01
$211.07
$221.62
$232.70
$244.33
$256.55
$269.38
$282.85
$296.99
$311.84
$327.43
$343.80
$360.99
$379.04

So this tells me $150 today at an annual inflation rate of 105% or
1.05 will be $379.04 in 20 years. Which formulas does this? I tried
the FV but I must not be doing something right. I think the FV tries
to tell me the future value of $150 compounded.

But the $150 is really a daily rate. Right now it costs us $150 a day
for something. If the daily rate goes up by 1.05 every year, then the
$150 daily cost will be #379.04 based on the second method of
calculation you gave me. The only problem is that I have
oversimplified this calculation and in some cases the "looping", which
is variable as I mentioned, could be 60 or 70 times. While your
second method would give us results, it is too awkward to put out in
the hands of field reps. Plus we wanted to put it on the Web in a
browser.

So I was hoping to get a simple spread sheet put together in Excel to
publish on the web.

Thanks for your help.

Anyone else have so idea of how to do this?




Best regards,

Al
http://www.affordablefloridainsurance.com
http://www.americanbestmortgages.com

 

[George Nicholson]

Replace enddo with Loop

Do While something or other
yada, yada
Loop

 

[JE McGimpsey]

If you just want to see $150 compounded 20 times at 5%:

=150 * (1+5%)^20 ===> 379.99

if there's no inflation the first year (as per your example):

=150 * (1.5%)^(20-1) ===> 379.04

I'm still not sure how

Multiply the Yearly Amount times the number of years to get a Total
Yearly Amount or call it YA

from your original post fits in...

 

[JE McGimpsey]

Se my reply to your previous thread - in general it's best to stick to
one thread, so that people don't waste time with the same answer(s) that
have already been given.

 

[AF]

If you just want to see $150 compounded 20 times at 5%:

=150 * (1+5%)^20 ===> 379.99

if there's no inflation the first year (as per your example):

=150 * (1.5%)^(20-1) ===> 379.04

I'm still not sure how


from your original post fits in...

This will work. Sometimes the simplest thing is right in front of you.

I think the formula will be, using variable names instead of Cell
number and assuming the Inflation Rate is expressed as an integer:

DailyCost*(1+InflationRate/100)^NoYears times the number of days for
which we are incurring the daily rate.

I won't go into the calculation for the number of days as it is not
important to this example. I should not have given that in my
original post. It just confused things.

Waht is important is the formula you mercifully gave me, as I was
ready to go bonkers over this. (The solution was so simple. I feel
foolish for not seeing it.)

I am surprised Excel does not have a simple loop or do while funstion
of some sort.

Anyway, this at least solves the problem.

Now I just have to get it on the web.

Thanks an awful lot.





Best regards,

Al
http://www.affordablefloridainsurance.com
http://www.americanbestmortgages.com

 

[aaron.kempf]

Excel does have a simple loop-- it's called VBA

but Excel isnt as powerful as Access.

if you do loops like this; you should really take a look at usign
Microsoft Access.

it rocks.

 

[Rick Merrill]

Excel does have a simple loop-- it's called VBA

Is that a function?

 

[JE McGimpsey]

No, it's Visual Basic for Applications, the language that XL macros are
written in.

XL can be made to do loops using Circular References and limiting the
number of iterations, but it's usually more difficult than using a
different technique.

In your case, the math technique, e.g.,

=p * (1 + i/100)^(n - 1)

is by far the most efficient.

You could also do an "open loop" by putting your principal amount in A1,
and multiplying it by the inflation rate in A2:A20, e.g.,:

A1: 150
B1: 5
A2: =A1 * (1 + $B$1/100) ==> 157.5

and drag the fill handle (lower right-hand corner of A2) down to A20 (XL
will adjust the A cell relative references, but not the $B$1 since the
$'s make it an absolute reference):

A20: =A19 * (1 + $B$1/100) ==> 379.04

 

[andywalker]

Se my reply to your previous thread - in general it's best to stick to
one thread, so that people don't waste time with the same answer(s)
that
have already been given.

you could try this

go into tools/macros/visual basic editor

once open, insert a new module throught the insert menu

enter the following code:

Sub inflation()



Dim timeperiod As Variant
Dim inrate As Variant
Dim newdaycost As Variant
Dim daycost As Variant
Dim counter As Variant

daycost = Cells(2, 2).Value
timeperiod = Cells(3, 2).Value
inrate = Cells(4, 2).Value

newdaycost = daycost
counter = 1

Do While counter <= timeperiod
newdaycost = newdaycost * inrate
counter = counter + 1
Loop

Cells(6, 2).Value = newdaycost

End Sub

This allows you to input your 3 variable in cell B2, B3, B4 and gives
an answer at B6. To get it to run i usually put a button in the sheet
with the code:
call inflation

hope this helps.