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

[Guest]

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

 

[Fred Smith]

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.

 

[Guest]

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.

 

[Ron Rosenfeld]

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.

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

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.

--ron

 

[Fred Smith]

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%