Real RATE of return using =RATE illusive, inflation adjusted inflo

Hi to all, The problem is I cannot get a real rate of return for my problem.
I am presented with an initial value at the beginning of year one, an annual
investment that is stepped 3% each year for inflation and the amount at the
conclusion of the final period.

The canned =RATE formula does not account for anything but a constant inflow
amount. Is there anyone who can provide a solution

Yes we can provide a solution, but we need to know what the problem is.

My guess is you're looking for the XIRR function. It will handle varying cash
flows. Subtract the rate of inflation from XIRR's result to get the real return.

If your problem is to calculate the real rate of return given inflation at 3%,
you could still use Rate with a constant PMT (the first cash flow), and FV as
the final amount discounted by 3% per year.

--
Regards,
Fred


"Pro - Land" <Pro - [email protected]> wrote in message
news:[email protected]...
> Hi to all, The problem is I cannot get a real rate of return for my problem.
> I am presented with an initial value at the beginning of year one, an annual
> investment that is stepped 3% each year for inflation and the amount at the
> conclusion of the final period.
>
> The canned =RATE formula does not account for anything but a constant inflow
> amount. Is there anyone who can provide a solution
>
>

Thank you for your response. I appreciate it. Let me provide an example the
scenario I am looking at.

Assume $50,000 starting balance.
First year contribution $3,325 increasing 3% annually to $5,830 contribution
in year 20.
Amount accumulated at the end of 20 years: $251,000

ROR: ?

This problem seems like a simple proposition. It has, however caused alot of
trial and error around the office and many an attempt at resolution has
caused staff who thought they knew Excel well to be taken aback. Your help
would be greatly appreciated.



"Fred Smith" wrote:

> Yes we can provide a solution, but we need to know what the problem is.
>
> My guess is you're looking for the XIRR function. It will handle varying cash
> flows. Subtract the rate of inflation from XIRR's result to get the real return.
>
> If your problem is to calculate the real rate of return given inflation at 3%,
> you could still use Rate with a constant PMT (the first cash flow), and FV as
> the final amount discounted by 3% per year.
>
> --
> Regards,
> Fred
>
>
> "Pro - Land" <Pro - [email protected]> wrote in message
> news:[email protected]...
> > Hi to all, The problem is I cannot get a real rate of return for my problem.
> > I am presented with an initial value at the beginning of year one, an annual
> > investment that is stepped 3% each year for inflation and the amount at the
> > conclusion of the final period.
> >
> > The canned =RATE formula does not account for anything but a constant inflow
> > amount. Is there anyone who can provide a solution
> >
> >
>
>
>

It seems like what you want is the IRR formula, since the returns are made at
equal times:

Set up a column of numbers:

B2: =50000
B3: =3325
B4: =A3*1.03
copy/drag to B20
B21: =-251000 (note the minus sign)

Formula: =IRR(B2:B23) --> 4.1055% with your data

Note -- as set up the payments are assumed to be made at the end of each
period, and the funds withdrawn one year after the last payment.

Changes in these assumptions will change the results.

====================================

On Mon, 31 Oct 2005 06:42:16 -0800, "Pro - Land"
<[email protected]> wrote:

>Thank you for your response. I appreciate it. Let me provide an example the
>scenario I am looking at.
>
>Assume $50,000 starting balance.
>First year contribution $3,325 increasing 3% annually to $5,830 contribution
>in year 20.
>Amount accumulated at the end of 20 years: $251,000
>
>ROR: ?
>
>This problem seems like a simple proposition. It has, however caused alot of
>trial and error around the office and many an attempt at resolution has
>caused staff who thought they knew Excel well to be taken aback. Your help
>would be greatly appreciated.
>
>
>
>"Fred Smith" wrote:
>
>> Yes we can provide a solution, but we need to know what the problem is.
>>
>> My guess is you're looking for the XIRR function. It will handle varying cash
>> flows. Subtract the rate of inflation from XIRR's result to get the real return.
>>
>> If your problem is to calculate the real rate of return given inflation at 3%,
>> you could still use Rate with a constant PMT (the first cash flow), and FV as
>> the final amount discounted by 3% per year.
>>
>> --
>> Regards,
>> Fred
>>
>>
>> "Pro - Land" <Pro - [email protected]> wrote in message
>> news:[email protected]...
>> > Hi to all, The problem is I cannot get a real rate of return for my problem.
>> > I am presented with an initial value at the beginning of year one, an annual
>> > investment that is stepped 3% each year for inflation and the amount at the
>> > conclusion of the final period.
>> >
>> > The canned =RATE formula does not account for anything but a constant inflow
>> > amount. Is there anyone who can provide a solution
>> >
>> >
>>
>>
>>

--ron

I agree with Ron's calculation of ROR. If you want the real rate of return, it's
the nominal rate minus the inflation rate, eg 4.1055% - 3% = 1.1055%

--
Regards,
Fred


"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> It seems like what you want is the IRR formula, since the returns are made at
> equal times:
>
> Set up a column of numbers:
>
> B2: =50000
> B3: =3325
> B4: =A3*1.03
> copy/drag to B20
> B21: =-251000 (note the minus sign)
>
> Formula: =IRR(B2:B23) --> 4.1055% with your data
>
> Note -- as set up the payments are assumed to be made at the end of each
> period, and the funds withdrawn one year after the last payment.
>
> Changes in these assumptions will change the results.
>
> ====================================
>
> On Mon, 31 Oct 2005 06:42:16 -0800, "Pro - Land"
> <[email protected]> wrote:
>
>>Thank you for your response. I appreciate it. Let me provide an example the
>>scenario I am looking at.
>>
>>Assume $50,000 starting balance.
>>First year contribution $3,325 increasing 3% annually to $5,830 contribution
>>in year 20.
>>Amount accumulated at the end of 20 years: $251,000
>>
>>ROR: ?
>>
>>This problem seems like a simple proposition. It has, however caused alot of
>>trial and error around the office and many an attempt at resolution has
>>caused staff who thought they knew Excel well to be taken aback. Your help
>>would be greatly appreciated.
>>
>>
>>
>>"Fred Smith" wrote:
>>
>>> Yes we can provide a solution, but we need to know what the problem is.
>>>
>>> My guess is you're looking for the XIRR function. It will handle varying
>>> cash
>>> flows. Subtract the rate of inflation from XIRR's result to get the real
>>> return.
>>>
>>> If your problem is to calculate the real rate of return given inflation at
>>> 3%,
>>> you could still use Rate with a constant PMT (the first cash flow), and FV
>>> as
>>> the final amount discounted by 3% per year.
>>>
>>> --
>>> Regards,
>>> Fred
>>>
>>>
>>> "Pro - Land" <Pro - [email protected]> wrote in message
>>> news:[email protected]...
>>> > Hi to all, The problem is I cannot get a real rate of return for my
>>> > problem.
>>> > I am presented with an initial value at the beginning of year one, an
>>> > annual
>>> > investment that is stepped 3% each year for inflation and the amount at
>>> > the
>>> > conclusion of the final period.
>>> >
>>> > The canned =RATE formula does not account for anything but a constant
>>> > inflow
>>> > amount. Is there anyone who can provide a solution
>>> >
>>> >
>>>
>>>
>>>
>
> --ron