XIRR or CAGR

[dork32]

CAGR = compounded annual growth rate.

It can be computed using XIRR formulation (for the case of irregular and regular cashflows),
or the typical CAGR formula (only for the case without any intermediate cashflow)
or TVM formulation (for the case with and without periodic regular cashflows (but not for the case with irregular cashflows)).

So, whoever said CAGR can only be computed for the case without any intermediate cashflow just simply don't understand what is CAGR and could possibly have been misled by Investopedia webpage (Yes! some stupid idiot ask you to go read Investopedia webpage containing wrong definition for CAGR because he also don't understand!)

you are right.

but in maths and science, there are many formulae. names have to given to the formulae such that we do not have to read out the entire formula, which can be very confusing.

in financial maths cagr is used to name (final/initial)^(1/years). because the formula is like that, it can only be used to calculate the returns of a single premium investment.

it is like common ratio is used to name the quotient of consecutive terms in a gp series. madguy is not wrong when he mentioned that ratios should be like 1:2. common ratio in english could mean a lot of other things, which is what created the confusion.

 

[dork32]

I seriously don't rmb what did I learnt in sec school.

But on a serious note... now the syllabus has changed so much if I were to do primary school maths, I have to admit I don't even know how to solve the question w/o using algebraic equation! Need to draw boxes I think.

So ya, take it as I will even fail my psle.

this is because you are not in this line.

i teach maths for a living. drawing boxes is called modelling.

i taught my kids for psle. my older kid scored her only a* in maths for psle. my younger kid is in p6. he score overall 96 for maths for the p5 exams.

having said that, i do make careless mistakes every now and then in the postings. the mistakes is much bigger the forgetting to put in brackets. the numbers becomes totally wrong.

 

[chopra]

One final post before i go and sleep. Shall not wait for madguy to put his answers down, may have to wait forever

the following shows how the 4 formulae can be used

Based on by example of ($100 for 10 years at 10%) gives 259

CAGR = (259/100)^(1/10) = 0.1 {must remember to draw bracket; otherwise ocd people will come after me}
RATE =RATE(10,0,100,-259) = 0.1
IRR =IRR({100,0,0,0,0,0,0,0,0,0,-259}) = 0.1
XIRR = XIRR({1 jan 2000, 1 Jan 2010},{100,-259}) = 0.1
actually you cannot key 1 jan 2000 into the formula, XIRR does not recognize dates
1 jan 2000 = 36526
1 jan 2010 = 40179

as what mike mentioned, all gives 10%

wow this is mind boggling. Stupid qn, since cagr n irr n xirr give same answer, then why do we have different formulae?

After many years of using cagr (i dont use excel formula as it is, but i write out the apgp from scratch), i am beginning to see why i should look at other formulae such as irr instead, because

dec 2018 i put $5
Jan 2020 i put another $5, wealth in ibkr becomes $11
Dec 2021 i take out 1 and wealth becomes $15
Mar 2022 i put in $20 and wealth becomes $30 and i don’t know how to count my returns liao

Can spoonfeed me how to enter in the spreadsheet pls? Which number put positive or negative? I can aggarate on the “excel number to enter the dates”

 

[dork32]

wow this is mind boggling. Stupid qn, since cagr n irr n xirr give same answer, then why do we have different formulae?

After many years of using cagr (i dont use excel formula as it is, but i write out the apgp from scratch), i am beginning to see why i should look at other formulae such as irr instead, because

dec 2018 i put $5
Jan 2020 i put another $5, wealth in ibkr becomes $11
Dec 2021 i take out 1 and wealth becomes $15
Mar 2022 i put in $20 and wealth becomes $30 and i don’t know how to count my returns liao

Can spoonfeed me how to enter in the spreadsheet pls? Which number put positive or negative? I can aggarate on the “excel number to enter the dates”

cagr and xirr have the same answer coz xirr is all encompassing and cagr is only for one particular situation. xirr is like grc, eg east coast grc, whether you are at bedok, simei or changi point, you are part of east coast grc. but if you are in simei, you are in east coast grc but you are not in fengshan constituency.

your example given has irregular cash flow but constant interval. hence irr formula is good enuf. but if you die die want to use xirr then it is ok, you just have to key the dates in 1 column, the cash flow in the other, use the xirr formula on the two columns and it will work.

 

[chopra]

gd morning. ok i go try out. will disturb u if i dun get it. haha


Read HWZ Forum Rules!

 

[Mecisteus]

Time Weighted Return vs Dollar Weighted Return (XIRR) both have different purposes.

https://www.fe.training/free-resources/asset-management/time-weighted-and-dollar-weighted-returns/

 

[chopra]

gd morning. ok i go try out. will disturb u if i dun get it. haha


Read HWZ Forum Rules!

Very happy. I managed to punch the values into my excel and get the correct answer, lol

actually i realised i used my own apgp n verified with u b4.
https://forums.hardwarezone.com.sg/threads/qn-calculating-house-investment.6008882/

 

[chopra]

Time Weighted Return vs Dollar Weighted Return (XIRR) both have different purposes.

https://www.fe.training/free-resources/asset-management/time-weighted-and-dollar-weighted-returns/

Ibkr shows both time n money weighted returns. If using my own excel for my ibkr since inception, i get xirr of 10%

i don’t understand ibkr automated calculations tho. It says below for money weighted.
Best is 7.58% while period is 35%?

Return (%)​

Best (2020-11-09)7.58
Worst (2018-12-28)-88.56
Period35.69

 

[chopra]

Qn: given xirr is a function of how much i invest and withdraw at different junctures (various cash flows), how do i know if my xirr is faring better or poorer than say sp500?

 

[Value.Matrix]

Qn: given xirr is a function of how much i invest and withdraw at different junctures (various cash flows), how do i know if my xirr is faring better or poorer than say sp500?

You can compare to the yield of S&P 500 for the same period.

Since The XIRR function is the yield of your investment.

But the actual way to compare is to tabulate the networth you have, then compare to the final npv if you had buy S&P 500. (Which can never go wrong). This is a lot more tedious.

 

[chopra]

You can compare to the yield of S&P 500 for the same period.
Since The XIRR function is the yield of your investment.
But the actual way to compare is to tabulate the networth you have, then compare to the final npv if you had buy S&P 500. (Which can never go wrong). This is a lot more tedious.

yes I figured the latter but i tot i am e only one tht think it’s tedious. thanks dude!!


Read HWZ Forum Rules!

 

[chopra]

tbill yield ranges from 2 to 4%pa n thats irr if my understanding is correct . if my xirr is 5%, can i say that I outperform tbill by 1 to 3%pa?


Read HWZ Forum Rules!