Cumulated expenses with inflation.

[fabrice.gautier]

Hi,

I want to calculate expenses over a period of times.

My inputs are :
- Initial Expense (P[0])
- Number of month (n)

Currently I do not take inflation into acount so my formula is just:

Total Expense : S[n] = P[0]*n

Now, if I want to take inflation into account, my expenses would
increase for each period.
So I have one more input : R is the monthly inflation rate.

So the expense each month is : P=P[i-1]*(1+R)=P[0]*(1+R)^i

An my total expense is S[n] = Sum(P,i=0..n) = P[0]*Sum((1+R)^i,
i=0..n)

How do I translate this in a Excel formula ?

Thank you

 

[Fred Smith]

Yes, it's called FV (for future value). It will calculate exactly what you want.

=fv(rate,n,p[0],p[0])

Remember the period for rate and n must match, so the rate specified must be a
monthly rate.

 

[joeu2004]

I want to calculate expenses over a period of times.
My inputs are :
- Initial Expense (P[0])
- Number of month (n)
[....]
Now, if I want to take inflation into account, my expenses would
increase for each period.
So I have one more input : R is the monthly inflation rate.
[....]
An my total expense is S[n] = Sum(P,i=0..n) = P[0]*Sum((1+R)^i,
i=0..n)

How do I translate this in a Excel formula ?

First, I would not account for inflation on monthly basis. One
approach is to apply the annual inflation rate to the year-over-year
monthly expenses. For example, if the annual inflation rate for 2006
is 4%, your January 2007 expenses might be about 4% higher than your
January 2006 expenses.

Second, there is the issue of which inflation rate to apply to which
expenses. Although you can get away with applying the same "consumer"
inflation rate to most expenses (although you could refine that by
using other price indexes), you should use different inflation rates to
account for education and medical costs, at the very least, if those
are applicable.

Of course, I realize I might be adding too much the problem statement,
since this sounds like a homework assignment.

In any case, the answer to your question is:

=fv(n, r, 0, -P[0])

where "n" is the number of compounding periods (years or months) and
"r" is the inflation rate for each period (annual or monthly
respectively). "P[0]" is your term for the initial expense. Note that
I negate it so that FV() returns a positive number.

 

[joeu2004]

Errata....

=fv(n, r, 0, -P[0])

Oops, got my "n" and "r" mixed up. The correct formula that
corresponds to yours (P[0]*Sum((1+R)^i, i=0..n)) is:

=fv(r, n, 0, -P[0])

 

[fabrice.gautier]

Hum... funny I was looking at FV, but it only seemed to me that this
was used to calculate the final value of an investement with constant
payments...

Now I see that it actually is the same thing... my math is rusty...

Thanks

Yes, it's called FV (for future value). It will calculate exactly what you want.

=fv(rate,n,p[0],p[0])

Remember the period for rate and n must match, so the rate specified must be a
monthly rate.

--
Regards,
Fred

Hi,

I want to calculate expenses over a period of times.

My inputs are :
- Initial Expense (P[0])
- Number of month (n)

Currently I do not take inflation into acount so my formula is just:

Total Expense : S[n] = P[0]*n

Now, if I want to take inflation into account, my expenses would
increase for each period.
So I have one more input : R is the monthly inflation rate.

So the expense each month is : P=P[i-1]*(1+R)=P[0]*(1+R)^i

An my total expense is S[n] = Sum(P,i=0..n) = P[0]*Sum((1+R)^i,
i=0..n)

How do I translate this in a Excel formula ?

Thank you