CAGR formula in excel
Multiple ways we can calculate CAGR in excel. Out of all most popular CAGR formula in Excel
is CAGR = ((End value /Began Value) ^ (1/Number of years)-1).
Now let’s understand in detail.
CAGR is called Compound growth rate of Returns which is used to understand year on year growth rate of returns.
CAGR formula in excel: 1
= ((End value /Began Value) ^ (1/Number of year)-1).
CAGR formula in excel: 2
= Power (End value/Began Value, 1/Number of year) -1
CAGR formula in excel: 3
= Rate (nper, pmt, pv, [fv], [type] , [guess])
= Rate (Number of years, ,-(Starting value),(End value))
For better understanding what is CAGR? and what is CAGR formula in excel? How to calculate simple CAGR in excel for simple investment and mutual fund investments? You can refer the below video.
Below way you can use CAGR formula in Excel for Mutual fund CAGR & XIRR calculation.
Here different date wise each investment you need to treat as a separate investment.
Let’s take an example of monthly SIP. In this case, each month’s SIP earning we need to treat as a separate investment and calculate CAGR accordingly.
CAGR Formula in excel:
Date: Date of investment mentioned over here.
Investment: amounts of investment which is invested in different periods. As it is an investment that is why it is signed as negative.
Unite Bought: How much unites bought by particular investment amount.
Unit Bought= Investment amount/NAV
NAV: Net asset value of the particular mutual fund that points of time.
NAV on Redemption date: what was the NAV when investor planning to redeem particular fund.
In holding time: how much time that particular investment was invested. As in this example, investors withdrew within next 12 months. that is why each period divided by 12 months.
Value at the time of redemption: What was the value of each fund at the time of investment.
Value at the time of redemption= Unit Bought * NAV on Redemption date
CAGR: CAGR of each investment.
CAGR Formula in excel is below
CAGR = ((Value at the time of redemption /Investment) ^ (1/In holding time)-1)
From this table, you can calculate simple XIRR
XIRR= (Investment value,Time)*100
Investment value = if it is an investment than negative sign if it is withdrawn than a positive sign.