XIRR or CAGR

[dork32]

i dare to put this up.

i have done a mathematical explanation on cagr and xirr. anyone can challenge it if you feel that it is not right.

i also challenge anyone to prove me wrong that the common ratio of a gp function must be written as 2:1 and not 1.1 as i have stated.

before you challenge, please check that you passed you o level. i give you discount. monday then get o level result. if you passed you prelim also can come and challenge me.

 

[dork32]

mummy1234 is a doctor. probably from a top jc. she only got a merit for her maths c s paper. i got a distinction for it. someone that cannot even pass secondary school maths is not entitled to challenge me in this aspect.

 

[dork32]

dear madguy,
mike78 mentioned that cagr and xirr gives that same number.
i did the cagr calculation of a final value of 259 over an initial investment 100 over 10 years gives you a cagr of 10%
Do you know how use the XIRR or rate or any formula to show that the xirr gives 10% as well?
if not, please do not post here. you just do not have enough knowledge to discuss about this matter.

just for your info, mike is correct. they do give the same number.

 

[dork32]

in terms of mathematics,
There is no analytical solution of xirr, only numerical solution. in other words, there are no formula to it. you have to guess the xirr value till the value is correct. something like the guess and check method that is taught in upper primary school.

notice when you use the xirr formula in excel, there is a guess. the reason is to help excel make a good initial guess such that the calculation can converge faster.

 

[Mecisteus]

I have done all these calculations so many times.

XIRR will give the same number as the CAGR simple formula or RATE function.

You can use XIRR or RATE to calculate bonds yield to maturity also.

If the cashflows are irregular, then XIRR has to do some guessing. But if you use Excel, users don't need to care the background or formula.

So in short, just learn how to use the XIRR in Excel. It can calculate all kinds of returns.

 

[dork32]

I have done all these calculations so many times.

XIRR will give the same number as the CAGR simple formula or RATE function.

You can use XIRR or RATE to calculate bonds yield to maturity also.

If the cashflows are irregular, then XIRR has to do some guessing. But if you use Excel, users don't need to care the background or formula.

So in short, just learn how to use the XIRR in Excel. It can calculate all kinds of returns.

i know you can do the calculations and you know i can. we use facts, logic and knowledge to back our calculations.
i cannot beat you. i just added one sentence in your post to make your post more correct.
you also cannot beat me. you just helped me to draw in the brackets.

but our friend here, madguy, tries to beat me using his lack of knowledge and logic.
he can say that he does need this knowledge to earn his wealth. this is correct.
but his lack now knowledge also means he is not fit to post his rubbish here

 

[Mecisteus]

but our friend here, madguy, tries to beat me using his lack of knowledge and logic.
he can say that he does need this knowledge to earn his wealth. this is correct.
but his lack now knowledge also means he is not fit to post his rubbish here

Ok lah give him a chance. At least, he already declared in his post and he is humble.

There are others who are not so smart, not humble at all and don't admit if wrong.

Or maybe I'm stupid and can't understand what ur trying to explain. Sorry for being stupid.

 

[dork32]

Ok lah give him a chance. At least, he already declared in his post and he is humble.

There are others who are not so smart, not humble at all and don't admit if wrong.

i do not hoot people that come in and ask question. i may be good in this area. there are other places that i may not be so strong in. i will still the advice of expert.

i do not sense any humility his post. what i felt was aggression and sarcasm. this guy has been trying to hoot me on the cpf value thread as well. I will not hold back my punches for this sort of people.

and do you see this guy admitting that he is wrong?

 

[dork32]

dear mike,
it is obvious that ctan does not understand all these stuff.

do you see me whacking ctan? i do not expect everyone to know all these unimportant crab.

madguy is here for the sole purpose of hitting at me. i will not be polite on him either.

 

[ctan84]

dear mike,
it is obvious that ctan does not understand all these stuff.

do you see me whacking ctan? i do not expect everyone to know all these unimportant crab.

madguy is here for the sole purpose of hitting at me. i will not be polite on him either.

dear dork, yes you are right that I really dun quite understand all these stuff, & thank you for trying to explain. No no, its certainly not "unimportant crab", the knowledge will certainly help me in the long run.

So can I safely sum up the discussion as:
1) Single 1 time premium - use cagr
2) Recurring premium (both regular / irregular) - use xirr

Is this right?

 

[tangent314]

So can I safely sum up the discussion as:
1) Single 1 time premium - use cagr
2) Recurring premium (both regular / irregular) - use xirr

No. I've explained it a few times in this thread already, but it seems you keep missing it through all the noise.

For both single premium and regular premium you should use a TVM calculator, which is easy to set up. A CAGR calculator is just a special case of TVM where PMT is set to 0 and compounding period is annual.

XIRR can handle pretty much anything you throw at it - both withdrawals and deposits, regular and irregular. It's a lot more time consuming to set up through, because you need one row per transaction with the date and amount in/out.

 

[dork32]

dear dork, yes you are right that I really dun quite understand all these stuff, & thank you for trying to explain. No no, its certainly not "unimportant crab", the knowledge will certainly help me in the long run.

So can I safely sum up the discussion as:
1) Single 1 time premium - use cagr
2) Recurring premium (both regular / irregular) - use xirr

Is this right?

1) Single 1 time premium - use cagr, use
xirr also can.

according mike78. you should use xirr under all cases. it works for all cases.
he is correct.

you have to set up your excel spreadsheet to use xirr formula. sometimes this can be quite troublesome.
cagr, irr and rate formula are much simpler forms of the xirr formula.
cagr is used when you are on a single premium
rates are more fixed payment at regular intervals. eg endowment insurance plan.
irr is used when you are payments/ investment are made at regular intervals. the amount differs for different period. eg dividends for the reits do vary over the years, but are made after every 3 months.

to summarize
cagr scope is the smallest.
rates can be use calculate cagr.
irr can be used to calculate rates and cagr
xirr can be used to calculate irr, rates and cagr

 

[Mecisteus]

I guess dork has summarized all the concepts correctly.

I think must translate to their respective mother toungue language if it is still not clear.

 

[dork32]

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%

 

[madtari]

Thanks Bro! Really appreciate it. Don't worry, I'm not affected in anyway by him. I'm just nitpicking on Dork, for the fun of it... It really amuses me how defensive he will get and go all out to defend his statement and proves mine is wrong.
But I really have to admit I know nuts about common ratio... its the first time I've heard about this. And I'm ok with humiliation. If he thinks I can't pass my O'levels so be it... I can't really be bothered.

Ok lah give him a chance. At least, he already declared in his post and he is humble.

There are others who are not so smart, not humble at all and don't admit if wrong.

Bro,

2.59^(1/10)

 

[madtari]

you need to cool down dude... I was just pointing out your mistake in missing out the brackets, and for confusing ppl. You also did not admit your mistake right? You finally added the brackets in your 'summary', and TS did admit that he is confused as well. So am I wrong?

dear mike,
it is obvious that ctan does not understand all these stuff.

do you see me whacking ctan? i do not expect everyone to know all these unimportant crab.

madguy is here for the sole purpose of hitting at me. i will not be polite on him either.

i do not hoot people that come in and ask question. i may be good in this area. there are other places that i may not be so strong in. i will still the advice of expert.

i do not sense any humility his post. what i felt was aggression and sarcasm. this guy has been trying to hoot me on the cpf value thread as well. I will not hold back my punches for this sort of people.

and do you see this guy admitting that he is wrong?

Its ok if you wish to keep on attacking me, but be careful not 一竿子打翻一船人. By saying things such as your post is for ppl with high educational background to understand, you might inadvertently cause ill feelings to those who really don't understand. This forum is accessible by ppl from all walks of life, from different background FYI.

i am sorry. my posting is for people with very high educational background to understand. you need to pass the very tough and challenging GCE O level to be able to understand it.

 

[dork32]

Thanks Bro! Really appreciate it. Don't worry, I'm not affected in anyway by him. I'm just nitpicking on Dork, for the fun of it... It really amuses me how defensive he will get and go all out to defend his statement and proves mine is wrong.
But I really have to admit I know nuts about common ratio... its the first time I've heard about this. And I'm ok with humiliation. If he thinks I can't pass my O'levels so be it... I can't really be bothered.

i dont think i am on defense mode. i am on all out attack.

if you want to nitpick, pick someone that is weak. eg mike78 seems to be quite strong. i do not to hit him because he is able to defend well. also i do not see any gaps to hit him as well.

cannot blame me for saying you did not pass o level. i learn ap, gp common ratio when i was in sec 3.

 

[dork32]

you need to cool down dude... I was just pointing out your mistake in missing out the brackets, and for confusing ppl. You also did not admit your mistake right? You finally added the brackets in your 'summary', and TS did admit that he is confused as well. So am I wrong?

my postings are full of mistakes. i am a very careless person. if you read my posts, you may feel that i failed o level english. i did not deny that that was an error. the error is due to carelessness rather than lack of knowledge. the fact that my numbers are ok means i am able to get these things right.

the other thing is that you do not understand the basics of my postings. mike78 would have detected the error long ago. he did not hoot me because he understands the cagr formula. he saw the numbers are ok, so he did not bother to hoot me.

 

[dork32]

Its ok if you wish to keep on attacking me, but be careful not 一竿子打翻一船人. By saying things such as your post is for ppl with high educational background to understand, you might inadvertently cause ill feelings to those who really don't understand. This forum is accessible by ppl from all walks of life, from different background FYI.

cant your detect the sarcasm?

high educational backgroud = pass o level?

the forum is for people that do not understand things to come and ask questions
the forum is for people that understands to come out and help

the forum is not for people that do not understand things to come and help those that do not understand.

 

[madtari]

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.

cannot blame me for saying you did not pass o level. i learn ap, gp common ratio when i was in sec 3.