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

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

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.


In article <[email protected]>,
AF <bscinc3000@Yahoo_NoSpam.com> wrote:

> 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

Replace enddo with Loop

Do While something or other
yada, yada
Loop

--
George Nicholson

Remove 'Junk' from return address.

"AF" <bscinc3000@Yahoo_NoSpam.com> wrote in message
news:[email protected]...
> 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

On Wed, 15 Jun 2005 14:58:09 -0600, JE McGimpsey
<[email protected]> wrote:

>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

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

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...

In article <[email protected]>,
AF <bscinc3000@Yahoo_NoSpam.com> wrote:


> 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.

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.


In article <[email protected]>,
AF <bscinc3000@Yahoo_NoSpam.com> wrote:

> 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

On Wed, 15 Jun 2005 15:28:57 -0600, JE McGimpsey
<[email protected]> wrote:

>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...
>
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

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.

[email protected] wrote:

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

Is that a function?


> 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.
>

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






In article <#[email protected]>,
Rick Merrill <[email protected]> wrote:

> Is that a function?

Originally Posted by 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.


In article <[email protected]>,
AF <bscinc3000@Yahoo_NoSpam.com> wrote:

> 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

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.