An initial investment followed by periodic equal investments are made
in a MF. How to calculate the CAGR at the time of redemption?
There are many points of view on that.
First, you said that the periodic investments are equal; but you did
not say that the periodic frequency is equal -- that is, at regular
intervals, e.g. monthly. If the frequency is not equal, you will need
to resort to using XIRR(). See the Help page. Even if the frequency
is equal, some people prefer to use XIRR() because they believe it is
more "accurate"; for example, "monthly" periods are not an equal
number of days. However, in my experience, most people simply treat
all months as equal.
Assuming equal investments at regular intervals, you might be able to
use the RATE() function. However, sometimes the RATE() function needs
a "guess" to help it compute the interest rate. Moreover, bear in
mind that the RATE() function returns the __periodic__ rate. There
are two points of view on how to annualize the result. They are:
=( 1 + rate(n, -pmt, -pv, fv) ) ^ y - 1
=y * rate(n, -pmt, -pv, fv)
where "n" is the number of investment periods (not including the
initial investment), "pmt" is the periodic investment, "pv" is the
initial investment, "fv" is the current net asset value of the fund,
and "y" is the number of periods per year.
The second formula is often used to compute the IRR (although I
disagree with it). The first formula should be used for the CAGR,
IMHO.
